MYSQL查询表的主键

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

测试执行结果如下:

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)2011-09-27

select*from表名
然后自己看哪一个是主键2011-09-27