如何得到表的主键？

有没有办法从 mysql-database 中获得主键字段的名称? 例如:

我有一张这样的桌子:

+----+------+
| id | name |
+----+------+
| 1  | Foo1 |
| 2  | Foo2 |
| 3  | Foo3 |
+----+------+

其中字段 id 是主键(它有自动增量，但我不能使用它)。如何在 php 中检索字段名“ id”？

157206 次浏览
小开

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';
小开 
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
小开
最佳答案

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

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

Column_name will contain the name of the primary key.

小开

For a PHP approach, you can use 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";
小开

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);
}
}


?>
小开

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';
小开

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'];
小开

use:

show columns from tablename where `Key` = "PRI";
小开

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

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.

小开

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 herehere

小开

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:-

SHOW INDEXES FROM products \G

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

*************************** 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:

To just display primary key from the table use :- 

SHOW INDEXES FROM table_name WHERE Key_name = 'PRIMARY'
小开

If you have spatial tables in your database, use:

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY' OR Key_name = 'OGR_FID'
小开

You should use PRIMARY from key_column_usage.constraint_name = "PRIMARY"

sample query,

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";
小开 
SELECT k.column_name
FROM information_schema.key_column_usage k
WHERE k.table_name = 'YOUR TABLE NAME' AND k.constraint_name LIKE 'pk%'

I would recommend you to watch all the fields


提交答案