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)

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)

楼主用的什么样的客户端程序？不同的客户端程序查看的方式可能不一样。我用Navicat。选择表后点右键--->选择设计表，在字段后面有个黄色的钥匙，那个字段就是主键。

尽管客户端不同，但查看应该都是在设计表中。

---