blocks|key|1103206|text|一种更好的方法是使用SHOW+KEYS，因为您并不总是可以访问information_schema。以下是工作原理：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1103207|SHOW+KEYS+FROM+table+WHERE+Key_name+=+'PRIMARY'|code-block|syntax|javascript|1103208|Column_name将包含主键的名称。|1103209|entityMap^0|A|9|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

A better way is to use <code>SHOW KEYS</code> since you don't always have access to information_schema. The following works:

<pre><code>SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'
</code></pre>

Column_name will contain the name of the primary key.

blocks|key|1103129|text|以下是主键列的名称|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1103130|SELECT+k.column_name
FROM+information_schema.table_constraints+t
JOIN+information_schema.key_column_usage+k
USING(constraint_name,table_schema,table_name)
WHERE+t.constraint_type='PRIMARY+KEY'
++AND+t.table_schema='YourDatabase'
++AND+t.table_name='YourTable';|code-block|syntax|javascript|1103131|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is the Primary key Column Name

<pre><code>SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
 AND t.table_schema='YourDatabase'
 AND t.table_name='YourTable';
</code></pre>

blocks|key|1318088|text|SELECT+kcu.column_name,+kcu.ordinal_position
FROM+++information_schema.table_constraints+tc
INNER+JOIN+information_schema.key_column_usage+kcu
ON+++++tc.CONSTRAINT_CATALOG+=+kcu.CONSTRAINT_CATALOG
AND++++tc.CONSTRAINT_SCHEMA+=+kcu.CONSTRAINT_SCHEMA
AND++++tc.CONSTRAINT_NAME+=+kcu.CONSTRAINT_NAME
WHERE++tc.table_schema+=+schema()+++++++++++++--+only+look+in+the+current+schema
AND++++tc.constraint_type+=+'PRIMARY+KEY'
AND++++tc.table_name+=+'<your-table-name>'++++--+specify+your+table.
ORDER+BY+kcu.ordinal_position|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1318089|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre class="lang-sql prettyprint-override"><code>SELECT kcu.column_name, kcu.ordinal_position
FROM information_schema.table_constraints tc
INNER JOIN information_schema.key_column_usage kcu
ON tc.CONSTRAINT_CATALOG = kcu.CONSTRAINT_CATALOG
AND tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.table_schema = schema() -- only look in the current schema
AND tc.constraint_type = 'PRIMARY KEY'
AND tc.table_name = '&lt;your-table-name&gt;' -- specify your table.
ORDER BY kcu.ordinal_position
</code></pre>

blocks|key|1318209|text|这样如何：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1318210|SELECT+COLUMN_NAME
FROM+INFORMATION_SCHEMA.COLUMNS
WHERE+TABLE_SCHEMA+=+'Your+Database'
++AND+TABLE_NAME+=+'Your+Table+name'
++AND+COLUMN_KEY+=+'PRI';


SELECT+COLUMN_NAME
FROM+INFORMATION_SCHEMA.COLUMNS
WHERE+TABLE_SCHEMA+=+'Your+Database'
++AND+TABLE_NAME+=+'Your+Table+name'
++AND+COLUMN_KEY+=+'UNI';|code-block|syntax|javascript|1318211|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

How about this:
<pre class="lang-sql prettyprint-override"><code>SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Your Database'
 AND TABLE_NAME = 'Your Table name'
 AND COLUMN_KEY = 'PRI';


SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Your Database'
 AND TABLE_NAME = 'Your Table name'
 AND COLUMN_KEY = 'UNI';
</code></pre>

blocks|key|1103316|text|使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1103317|show+columns+from+tablename+where+`Key`+=+"PRI";|code-block|syntax|javascript|1103318|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

use:

<pre><code>show columns from tablename where `Key` = "PRI";
</code></pre>

blocks|key|1318307|text|如果您希望通过PHP一次性动态生成主键列表，而不必遍历每个表，则可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1318308|SELECT+TABLE_NAME,+COLUMN_NAME
FROM+INFORMATION_SCHEMA.key_column_usage+
WHERE+table_schema+=+'$database_name'+AND+CONSTRAINT_NAME+=+'PRIMARY'+|code-block|syntax|javascript|1318309|尽管您确实需要访问information.schema才能做到这一点。|1318310|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you want to generate the list of primary keys dynamically via PHP in one go without having to run through each table you can use
<pre class="lang-sql prettyprint-override"><code>SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.key_column_usage 
WHERE table_schema = '$database_name' AND CONSTRAINT_NAME = 'PRIMARY' 
</code></pre>
though you do need to have access to the information.schema to do this.

blocks|key|1103309|text|最短的代码看起来像这样|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1103310|//+$dblink+contain+database+login+details+
//+$tblName+the+current+table+name+
$r+=+mysqli_fetch_assoc(mysqli_query($dblink,+"SHOW+KEYS+FROM+$tblName+WHERE+Key_name+=+'PRIMARY'"));+
$iColName+=+$r['Column_name'];+|code-block|syntax|javascript|1103311|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Shortest possible code seems to be something like 

<pre><code>// $dblink contain database login details 
// $tblName the current table name 
$r = mysqli_fetch_assoc(mysqli_query($dblink, "SHOW KEYS FROM $tblName WHERE Key_name = 'PRIMARY'")); 
$iColName = $r['Column_name']; 
</code></pre>

blocks|key|1103219|text|对于PHP方法，您可以使用mysql_field_flags|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1103220|$q+=+mysql_query('select+*+from+table+limit+1');

for($i+=+0;+$i+<+mysql_num_fields();+$i%2B%2B)
++++if(strpos(mysql_field_tags($q,+$i),+'primary_key')+!==+false)
++++++++echo+mysql_field_name($q,+$i)."+is+a+primary+key\n";|code-block|syntax|javascript|1103221|entityMap|0|LINK|mutability|MUTABLE|url|http://php.net/manual/en/function.mysql-field-flags.php^0|D|H|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

For a PHP approach, you can use <a href="http://php.net/manual/en/function.mysql-field-flags.php" rel="nofollow noreferrer">mysql_field_flags</a>

<pre><code>$q = mysql_query('select * from table limit 1');

for($i = 0; $i &lt; mysql_num_fields(); $i++)
 if(strpos(mysql_field_tags($q, $i), 'primary_key') !== false)
 echo mysql_field_name($q, $i)." is a primary key\n";
</code></pre>

blocks|key|1103263|text|我终于得到它了！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1103264|<?php

function+mysql_get_prim_key($table){
++++$sql+=+"SHOW+INDEX+FROM+$table+WHERE+Key_name+=+'PRIMARY'";
++++$gp+=+mysql_query($sql);
++++$cgp+=+mysql_num_rows($gp);

++++if+($cgp+>+0)+{
++++++++//+Note+I'm+not+using+a+while+loop+because+I+never+use+more+than+one+prim+key+column
++++++++$agp+=+mysql_fetch_array($gp);
++++++++extract($agp);
++++++++
++++++++return($Column_name);
++++}+else+{
++++++++return(false);
++++}
}

?>|code-block|syntax|javascript|1103265|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I've got it, finally!
<pre class="lang-php prettyprint-override"><code>&lt;?php

function mysql_get_prim_key($table){
 $sql = &quot;SHOW INDEX FROM $table WHERE Key_name = 'PRIMARY'&quot;;
 $gp = mysql_query($sql);
 $cgp = mysql_num_rows($gp);

 if ($cgp &gt; 0) {
 // Note I'm not using a while loop because I never use more than one prim key column
 $agp = mysql_fetch_array($gp);
 extract($agp);
 
 return($Column_name);
 } else {
 return(false);
 }
}

?&gt;
</code></pre>

blocks|key|1318379|text|我使用SHOW+index+FROM+table；它给了我很多信息；如果键是唯一的，它在索引中的顺序，排序规则，子部分，如果是空的，它的类型和注释，如果存在，请看这里的截图|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1318380|📷|atomic|offset|length|1318381|​|1318382|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/3ba805e53a4e24a6fe0f0d3595664ebe.png|imageAlt^0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@$E|T|F|U|1|V]]|A|$]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P|Q|-4]]]]

I use SHOW INDEX FROM table ; it gives me alot of informations ; if the key is unique, its sequenece in the index, the collation, sub part, if null, its type and comment if exists, see screenshot here<img src="https://i.stack.imgur.com/B02GT.png" alt="here">

blocks|key|1103501|text|MySQL有一个SQL查询"SHOW+INDEX+FROM“，它返回表中的索引。例如。-以下查询将显示products表的所有索引：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1103502|SHOW+INDEXES+FROM+products+\G|code-block|syntax|javascript|1103503|它返回一个包含type、column_name、Key_name等的表，并将所有索引和主键的输出显示为-|1103504|***************************+1.+row+***************************
++++++++Table:+products
+++Non_unique:+0
+++++Key_name:+PRIMARY
+Seq_in_index:+1
++Column_name:+product_id
++++Collation:+A
++Cardinality:+0
+++++Sub_part:+NULL
+++++++Packed:+NULL
+++++++++Null:+
+++Index_type:+BTREE
++++++Comment:+
Index_comment:+|1103505|要只显示表中的主键，请使用:-|1103506|SHOW+INDEXES+FROM+table_name+WHERE+Key_name+=+'PRIMARY'|1103507|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

MySQL has a SQL query "SHOW INDEX FROM" which returns the indexes from a table.
For eg. - the following query will show all the indexes for the products table:-

<pre><code>SHOW INDEXES FROM products \G
</code></pre>

It returns a table with type, column_name, Key_name, etc. and displays output with all indexes and primary keys as - 

<pre><code>*************************** 1. row ***************************
 Table: products
 Non_unique: 0
 Key_name: PRIMARY
 Seq_in_index: 1
 Column_name: product_id
 Collation: A
 Cardinality: 0
 Sub_part: NULL
 Packed: NULL
 Null: 
 Index_type: BTREE
 Comment: 
Index_comment: 
</code></pre>

To just display primary key from the table use :- 

<pre><code>SHOW INDEXES FROM table_name WHERE Key_name = 'PRIMARY'
</code></pre>

blocks|key|1103566|text|如果您的数据库中有空间表，请使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1103567|SHOW+KEYS+FROM+table+WHERE+Key_name+=+'PRIMARY'+OR+Key_name+=+'OGR_FID'|code-block|syntax|javascript|1103568|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you have spatial tables in your database, use:

<pre><code>SHOW KEYS FROM table WHERE Key_name = 'PRIMARY' OR Key_name = 'OGR_FID'
</code></pre>

blocks|key|1103629|text|您应该使用key_column_usage.constraint_name+=+"PRIMARY"中的主服务器|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1103630|示例查询，|1103631|SELECT+k.column_name+as+PK,+concat(tbl.TABLE_SCHEMA,+'.`',+tbl.TABLE_NAME,+'`')+as+TABLE_NAME
FROM+information_schema.TABLES+tbl
JOIN+information_schema.key_column_usage+k+on+k.table_name+=+tbl.table_name
WHERE+k.constraint_name='PRIMARY'
++AND+tbl.table_schema='MYDB'
++AND+tbl.table_type="BASE+TABLE";|code-block|syntax|javascript|1103632|entityMap^0|5|18|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

You should use PRIMARY from <code>key_column_usage.constraint_name = "PRIMARY"</code>

sample query,

<pre><code>SELECT k.column_name as PK, concat(tbl.TABLE_SCHEMA, '.`', tbl.TABLE_NAME, '`') as TABLE_NAME
FROM information_schema.TABLES tbl
JOIN information_schema.key_column_usage k on k.table_name = tbl.table_name
WHERE k.constraint_name='PRIMARY'
 AND tbl.table_schema='MYDB'
 AND tbl.table_type="BASE TABLE";
</code></pre>

blocks|key|1318631|text|SELECT+k.column_name
FROM+information_schema.key_column_usage+k+++
WHERE+k.table_name+=+'YOUR+TABLE+NAME'+AND+k.constraint_name+LIKE+'pk%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1318632|我建议你看着所有的田野|unstyled|1318633|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT k.column_name
FROM information_schema.key_column_usage k 
WHERE k.table_name = 'YOUR TABLE NAME' AND k.constraint_name LIKE 'pk%'
</code></pre>

I would recommend you to watch all the fields

Is there a way to get the name of primary key field from mysql-database? For example:

I have a table like this:

<pre><code>+----+------+
| id | name |
+----+------+
| 1 | Foo1 |
| 2 | Foo2 |
| 3 | Foo3 |
+----+------+
</code></pre>

Where the field id is primary key (it has auto increment but I can't use that). How can I retrieve fields name "id" in php?

How to get primary key of table?

数据库

服务器

MySQL

有没有办法从mysql-database中获取主键字段的名称？例如：我有一张这样的桌子：+----+------+| id | name |+----+------+| 1  | Foo1 || 2  | Foo2 || 3  | Foo3 |+----+------+其中字段id是主键(它有自动递增功能，但我不能使用它)。如何在php中检索名为"id“的字段？

问如何获取表的主键？
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取表的主键？EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取表的主键？
EN