Get the auto increment-primary key column programatically

Question

witch is the auto increment field in a mysql table?

I have a table structure, for example: table name is my_table and the fields are my_id, my_name, my_blah.. The one of fields is an auto incremented primary key. Witch is it?

How can I get the name of auto increment field on this table with a php code and/or a mysql query?

Refer this http://stackoverflow.com/questions/11093650/how-can-i-get-the-auto-incrementing-field-name-or-the-primary-key-fieldname-from — Saravana Kumar, Feb 19 '15 at 13:16

score 1 · Accepted Answer · answered Feb 19 '15 at 13:16

You can use the MySQL SHOW COLUMNS query to retrieve information about the columns in a table:

mysql> SHOW COLUMNS FROM `test`;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| col1     | varchar(100) | YES  |     | NULL    |                |
| col2     | int(11)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

The extra column will contain auto_increment for the primary key field.

score 0 · Answer 2 · answered Feb 19 '15 at 13:16

0

You could try with a

SHOW COLUMNS FROM TableName

See here: http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

answered Feb 19 '15 at 13:16

erg

1,632
1
11
23

score 0 · Answer 3 · answered Feb 19 '15 at 13:18

Use a MySQL client and issue the query SHOW CREATE TABLE my_table.

It shows you the code one needs to run to create that table. You can see the column names, types (and lengths), other attributes each column may have (they depend on the type). AUTO_INCREMENT is such an attribute.

It also shows you the PK and the indexes of the table.

Get the auto increment-primary key column programatically

3 Answers3