mysql如何获取主键的字段名

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='db'

AND t.table_name=tbl'

SELECT

t.TABLE_NAME,

t.CONSTRAINT_TYPE,

c.COLUMN_NAME,

c.ORDINAL_POSITION

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c

WHERE

t.TABLE_NAME = c.TABLE_NAME

AND t.TABLE_SCHEMA = 'test'

AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

测试执行结果如下:

mysql>SELECT

-> t.TABLE_NAME,

-> t.CONSTRAINT_TYPE,

-> c.COLUMN_NAME,

-> c.ORDINAL_POSITION

->FROM

-> INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,

-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c

->WHERE

-> t.TABLE_NAME = c.TABLE_NAME

-> AND t.TABLE_SCHEMA = 'test'

-> AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

->LIMIT 3

+------------+-----------------+-------------+------------------+

| TABLE_NAME | CONSTRAINT_TYPE | COLUMN_NAME | ORDINAL_POSITION |

+------------+-----------------+-------------+------------------+

| mr_dept| PRIMARY KEY | dept_id |1 |

| order | PRIMARY KEY | id |1 |

| tab| PRIMARY KEY | id |1 |

+------------+-----------------+-------------+------------------+

3 rows in set (0.06 sec)

哈哈，楼主遇到我算是你有福气了，我专门为你编写了一个函数，来获取表的主键名()（可能还有更简单的方法，但我不知道）。

请看如下代码：

<?php

function get_primary($host,$user,$password,$tb)//定义函数，参数分别为主机名、用户名、密码、表名（还可以加入库名，我忘了）

{

mysql_connect($host,$user,$password) //连接主机

mysql_select_db("test") //选择库

$result=mysql_query("DESCRIBE ".$tb) //分析表

while($rows=mysql_fetch_array($result)) //遍历结果集（即所有字段情况）

{

if($rows["Key"]=="PRI")//判断相应字段主键情况，如果是主键则跳出

break

}

return $rows["Field"] //返回所在字段名称

}

echo get_primary("localhost","root","","te") //调用函数

?>

---