Subscribed unsubscribe Subscribe Subscribe

oinume journal

Scratchpad of what I learned

Get auto increment values from MySQL information schema

MySQL in English

Information schema is metadata about MySQL. We can obtain useful information from it. For example, I get current auto increment values by following SQL.

SELECT
  t.table_name, t.auto_increment
FROM
  information_schema.tables AS t
WHERE
  t.table_schema = 'your_db'
ORDER BY t.table_name;
+-------------------------------------+----------------+
| table_name                          | auto_increment |
+-------------------------------------+----------------+
| blog_categories                     |              4 |
| blog_posts                          |              4 |
| blog_post_categories                |              4 |
| blog_post_comments                  |              1 |
| images                              |              7 |
+-------------------------------------+----------------+

INFORMATION_SCHEMA database has much more useful tables like COLUMNS, GLOBAL_VARIABLES and so on. It's good to use it when you want to know much about MySQL.