How can I get auto-increment field name or primary key field name from mysql table?

Question

In PHP, how do I get the name of a field field that was set as an automatic increment when a new recursion is added to it?

In most cases, this is the same as the PRIMARY_KEY table, but not necessarily always.

So, this question consists of two parts, and the second - in the 3rd part.

1- How to get the name of an auto-increment field name ...

2- How to get the name of the primary_key field name ...

2.1 How to get primary_key (s) information when a table uses more than one field as the primary key ...

Answer 1

You can get table information using the SHOW COLUMNS FROM table . Something like that:

 $res = $mysqli->query('SHOW COLUMNS FROM tablename'); while($row = $res->fetch_assoc()) { if ($row['Extra'] == 'auto_increment') echo 'Field with auto_increment = '.$row['Field']; if ($row['Key'] == 'PRI') echo 'Field with primary key = '.$row['Field']; } 

Answer 2

if you want to get the primary key column in the table, you can use this code:

 SELECT k.COLUMN_NAME FROM information_schema.table_constraints t LEFT JOIN information_schema.key_column_usage k USING(constraint_name,table_schema,table_name) WHERE t.constraint_type='PRIMARY KEY' AND t.table_schema=DATABASE() AND t.table_name='tbName'; -- the name of your table 

To get an auto-increment field, try the following:

 SELECT Auto_increment FROM information_schema.tables WHERE table_name = 'tbName' AND table_schema = DATABASE(); 

Answer 3

You can get this information with the SHOW COLUMNS command. Additional Information

Example. Suppose you have a table named City. Request to view table attributes:

 mysql> SHOW COLUMNS FROM City; ...And the result: +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | Country | char(3) | NO | UNI | | | | District | char(20) | YES | MUL | | | | Population | int(11) | NO | | 0 | | +------------+----------+------+-----+---------+----------------+ 

This is from http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

Answer 4

You can query the information_schema database:

 SELECT column_name, column_key, extra FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='tablename'; 

• column_key will consist of a key type, i.e. PRI , MUL , etc.
• The extra column will contain auto_increment for the auto-increment column.

Note that the information_schema database is global, so you should always pass the appropriate database (either specifically or through DATABASE() for the current database) or to a table, otherwise you will get a BIG result set.