mysql查询数据库中各表的主键、自增标识

最新推荐文章于 2024-01-30 14:34:22 发布

哥是东施

最新推荐文章于 2024-01-30 14:34:22 发布

阅读量1.7w

点赞数 5

分类专栏：数据库文章标签：自增主键 information_schema

本文链接：https://blog.csdn.net/cai843383655/article/details/53906257

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

查询数据库中所有表的自增主键：

SELECT
  t.TABLE_NAME,
  c.COLUMN_NAME,
  ts.AUTO_INCREMENT
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
  information_schema.TABLES AS ts,
  information_schema.KEY_COLUMN_USAGE AS c
WHERE
  t.TABLE_NAME = ts.TABLE_NAME
  AND ts.TABLE_NAME  = c.TABLE_NAME
  -- AND t.TABLE_SCHEMA = 数据库名称
  AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'
  ORDER BY ts.`AUTO_INCREMENT` DESC;

查询数据库中所有表的主键及数量：

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,
  information_schema.TABLES AS ts
WHERE
  t.TABLE_NAME = c.TABLE_NAME
  -- AND t.TABLE_SCHEMA = 数据库名称
  AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';

参考：http://www.mysqlpub.com/onlinemanual/doc/refman/5.1/zh/information-schema.html