php MySQL：动态确定表的主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/893874/
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
MySQL: Determine Table's Primary Key Dynamically
提问by macinjosh
I'm, generating a SQL query like this in PHP:
我在 PHP 中生成这样的 SQL 查询：
$sql = sprintf("UPDATE %s SET %s = %s WHERE %s = %s", ...);
Since almost every part of this query is dynamic I need a way to determine the table's primary key dynamically, so that I'd have a query like this:
由于这个查询的几乎每个部分都是动态的，我需要一种方法来动态确定表的主键，以便我有一个这样的查询：
$sql = sprintf("UPDATE %s SET %s=%s WHERE PRIMARY_KEY = %s", ...);
Is there a MySQL keyword for a table's primary key, or a way to get it?
表的主键是否有 MySQL 关键字，或获取它的方法？
I've used the information_schema DB before to find information like this, but it'd be nice if I didn't have to resort to that.
我以前使用过 information_schema DB 来查找这样的信息，但如果我不必求助于它就好了。
回答by Frank Farmer
SHOW INDEX FROM <tablename>
You want the row where Key_name = PRIMARY
您想要 Key_name = PRIMARY 所在的行
http://dev.mysql.com/doc/refman/5.0/en/show-index.html
http://dev.mysql.com/doc/refman/5.0/en/show-index.html
You'll probably want to cache the results -- it takes a while to run SHOW statements on all the tables you might need to work with.
您可能希望缓存结果——在您可能需要使用的所有表上运行 SHOW 语句需要一段时间。
回答by lukmdo
It might be not advised but works just fine:
可能不建议这样做，但效果很好：
SHOW INDEX FROM <table_name> WHERE Key_name = 'PRIMARY';
The solid way is to use information_schema:
可靠的方法是使用information_schema：
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='owalog';
As presented on the mysql-list. However its a few times slower from the first solution.
如mysql-list 所示。然而，它比第一个解决方案慢了几倍。
回答by Jake Sully
A better way to get Primary Key columns:
获取主键列的更好方法：
SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'dbName')
AND (`TABLE_NAME` = 'tableName')
AND (`COLUMN_KEY` = 'PRI');
From http://mysql-0v34c10ck.blogspot.com/2011/05/better-way-to-get-primary-key-columns.html
来自http://mysql-0v34c10ck.blogspot.com/2011/05/better-way-to-get-primary-key-columns.html
回答by Hikmat Sijapati
Also
还
SHOW INDEX FROM <table_name> WHERE Key_name = 'PRIMARY';
Is equivalent to
相当于
SHOW KEYS FROM <table_name> WHERE Key_name = 'PRIMARY';
回答by Ivano
Based on @jake-sully and @lukmdo answers, making a merge of their code, I finished with the following snippet:
根据@jake-sully 和@lukmdo 的回答，合并了他们的代码，我完成了以下代码段：
SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = DATABASE())
AND (`TABLE_NAME` = '<tablename>')
AND (`COLUMN_KEY` = 'PRI');
Hope it could help someone
希望它可以帮助某人
