php 如何获取表的主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2341278/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get primary key of table?
提问by Martti Laine
Is there a way to get the name of primary key field from mysql-database? For example:
有没有办法从mysql数据库中获取主键字段的名称？例如：
I have a table like this:
我有一张这样的表：
+----+------+
| id | name |
+----+------+
| 1 | Foo1 |
| 2 | Foo2 |
| 3 | Foo3 |
+----+------+
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?
字段 id 是主键的地方（它有自动增量，但我不能使用它）。如何在php中检索字段名称“id”？
回答by alexn
A better way is to use SHOW KEYSsince you don't always have access to information_schema. The following works:
更好的方法是使用，SHOW KEYS因为您并不总是可以访问 information_schema。以下工作：
SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'
Column_name will contain the name of the primary key.
Column_name 将包含主键的名称。
回答by Svetlozar Angelov
Here is the Primary key Column Name
这是主键列名
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';
回答by Roland Bouman
SELECT key_column_usage.column_name
FROM information_schema.key_column_usage
WHERE table_schema = schema() -- only look in the current db
AND constraint_name = 'PRIMARY' -- always 'PRIMARY' for PRIMARY KEY constraints
AND table_name = '<your-table-name>' -- specify your table.
回答by Afroz
How about this.
这个怎么样。
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';
回答by v1rus32
use:
用：
show columns from tablename where `Key` = "PRI";
回答by Donal
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
如果您想一次性通过 php 动态生成主键列表，而不必遍历您可以使用的每个表
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.key_column_usage WHERE table_schema = '$database_name' AND CONSTRAINT_NAME = 'PRIMARY'
though you do need to have access to the information.schema to do this.
尽管您确实需要访问 information.schema 才能执行此操作。
回答by Benoit Vidis
For a PHP approach, you can use mysql_field_flags
对于 PHP 方法，您可以使用mysql_field_flags
$q = mysql_query('select * from table limit 1');
for($i = 0; $i < 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";
回答by K. Kilian Lindberg
Shortest possible code seems to be something like
最短的代码似乎是这样的
// $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'];
回答by John
I've got it, finally!
我得到了，终于！
<?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);
}
}
?>
回答by John
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
我使用 SHOW INDEX FROM table ; 它给了我很多信息；如果键是唯一的，它在索引中的序列，排序规则，子部分，如果为空，它的类型和注释是否存在，请看这里的截图
