十一、MySQL 系统表

---

一、INFORMATION_SCHEMA 系统表

1. 数据库

-- 当前库
SELECT DATABASE();

-- 使用 test 库
USE test;

---

-- 数据库
SELECT CATALOG_NAME               AS "目录名称"
     , SCHEMA_NAME                AS "数据库名"
     , DEFAULT_CHARACTER_SET_NAME AS "默认字符集名"
     , DEFAULT_COLLATION_NAME     AS "默认排序规则名"
     , SQL_PATH                   AS "SQL路径"
     , t.*
FROM INFORMATION_SCHEMA.SCHEMATA t
WHERE SCHEMA_NAME = 'test';

---

2. 数据表

-- 查询所有表名
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_SCHEMA = DATABASE();

-- 当前用户,所有表（`Table_in_CHNMEDICINE`）
SHOW TABLES;
SHOW CREATE TABLE `表名`

---

-- 数据表
SELECT TABLE_CATALOG   AS "表目录"
	 , TABLE_SCHEMA    AS "库名"
     , TABLE_NAME      AS "表名"
     , TABLE_TYPE      AS "表类型"
     , ENGINE          AS "引擎"
     , VERSION         AS "版本"
     , ROW_FORMAT      AS "行格式"
     , TABLE_ROWS      AS "行数"
     , AUTO_INCREMENT  AS "自增"
     , CREATE_TIME     AS "创建时间"
     , UPDATE_TIME     AS "修改时间"
     , TABLE_COLLATION AS "表排序"
     , TABLE_COMMENT   AS "表注释"
     , t.*
FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_SCHEMA = 'test';

---

3. 数据列

-- 根据表名,查询列名和类型
SELECT t.COLUMN_NAME, t.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS t
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = #{tableName}

SHOW FULL COLUMNS FROM `tableName`

---

-- 数据列
SELECT TABLE_CATALOG            AS "表目录"
     , TABLE_SCHEMA             AS "库名"
     , TABLE_NAME               AS "表名"
     , COLUMN_NAME              AS "列名"
     , ORDINAL_POSITION         AS "序号位置"
     , COLUMN_DEFAULT           AS "默认值"
     , IS_NULLABLE              AS "可以为空[NO|YES]"
     , DATA_TYPE                AS "数据类型"
     , CHARACTER_MAXIMUM_LENGTH AS "中文的最大长度"
     , CHARACTER_OCTET_LENGTH   AS "字符的最大长度[中文的最大长度*3]"
     , NUMERIC_PRECISION        AS "整数精度"
     , NUMERIC_SCALE            AS "小数精度"
     , DATETIME_PRECISION       AS "日期精度"
     , CHARACTER_SET_NAME       AS "字符集名称[utf8]"
     , COLLATION_NAME           AS "排序规则名[utf8_general_ci]"
     , COLUMN_TYPE              AS "列类型"
     , COLUMN_KEY               AS "列键[PRI主键]"
     , EXTRA                    AS "[auto_increment自增]"
     , PRIVILEGES               AS "权限"
     , COLUMN_COMMENT           AS "注释"
     , t.*
FROM INFORMATION_SCHEMA.COLUMNS t
WHERE TABLE_SCHEMA = 'test';

---

4. 约束主键

-- 根据表名,查询主键
SELECT t2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
    USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME)
WHERE t.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_NAME = #{tableName};

---

4.1 约束

-- 表的约束
SELECT CONSTRAINT_CATALOG AS "约束目录"
     , CONSTRAINT_SCHEMA  AS "约束数据库"
     , CONSTRAINT_NAME    AS "约束名"
     , TABLE_SCHEMA       AS "库名"
     , TABLE_NAME         AS "表名"
     , CONSTRAINT_TYPE    AS "约束类型[PRIMARY KEY主键|UNIQUE唯一]"
     , t.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t;

---

4.2 键列

-- 使用的键
SELECT CONSTRAINT_CATALOG            AS "约束目录"
     , CONSTRAINT_SCHEMA             AS "约束数据库"
     , CONSTRAINT_NAME               AS "约束名"
     , TABLE_CATALOG                 AS "表目录"
     , TABLE_SCHEMA                  AS "库名"
     , TABLE_NAME                    AS "表名"
     , COLUMN_NAME                   AS "列名"
     , ORDINAL_POSITION              AS "序号位置"
     , POSITION_IN_UNIQUE_CONSTRAINT AS "唯一约束中的位置"
     , REFERENCED_TABLE_SCHEMA       AS "外键库"
     , REFERENCED_TABLE_NAME         AS "外键表"
     , REFERENCED_COLUMN_NAME        AS "外键列"
     , t.*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t;

---

5. 引擎

-- 引擎
SELECT ENGINE       AS "引擎"
     , SUPPORT      AS "支持"
     , COMMENT      AS "注释"
     , TRANSACTIONS AS "事务[YES|NO]"
     , t.*
FROM INFORMATION_SCHEMA.ENGINES t;

---

6. 存储过程、存储函数

SELECT SPECIFIC_NAME   AS "具体名"
     , ROUTINE_CATALOG AS "目录"
     , ROUTINE_SCHEMA  AS "库名"
     , ROUTINE_NAME    AS "名称"
     , ROUTINE_TYPE    AS "类型[PROCEDURE存储过程|FUNCTION存储方法]"
     , DATA_TYPE       AS "数据类型"
     , t.*
FROM INFORMATION_SCHEMA.ROUTINES t
WHERE ROUTINE_SCHEMA = 'test';

---

7. 触发器

SELECT ACTION_ORDER,
       EVENT_OBJECT_TABLE,
       TRIGGER_NAME,
       EVENT_MANIPULATION,
       EVENT_OBJECT_TABLE,
       DEFINER,
       ACTION_STATEMENT,
       ACTION_TIMING
FROM information_schema.triggers
WHERE BINARY event_object_schema = 'test-db'
  AND BINARY event_object_table = 'sys_users'
ORDER BY event_object_table;

---

二、变量

-- 小写文件系统、小写字母表名称
SHOW VARIABLES LIKE 'lower_case_%';

-- `SQL`模式
SHOW VARIABLES LIKE 'sql_mode';

---

三、MySQL 查询表结构

-- `MySQL`查询表结构
SELECT IF(t2.ORDINAL_POSITION = 1, t.TABLE_NAME, '')    AS "表名"
     , IF(t2.ORDINAL_POSITION = 1, t.TABLE_COMMENT, '') AS "表注释"
     , t2.COLUMN_NAME                                   AS "列名"
     , t2.COLUMN_TYPE                                   AS "类型"
     , IF(t2.IS_NULLABLE = 'NO', t2.IS_NULLABLE, '')    AS "NULL"
     , t2.COLUMN_DEFAULT                                AS "默认值"
     , t2.COLUMN_KEY                                    AS "键"
     , t2.EXTRA                                         AS "自增"
     , t2.COLUMN_COMMENT                                AS "列注释"
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS t2 ON t2.TABLE_SCHEMA = t.TABLE_SCHEMA AND t2.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
ORDER BY t.TABLE_NAME, t2.ORDINAL_POSITION;

---

四、字段转小写

SELECT concat('ALTER TABLE '
           , '表名'
           , ' CHANGE COLUMN '
           , COLUMN_NAME, ' '
           , LOWER(COLUMN_NAME)
           , ' '
           , COLUMN_TYPE
           , ' COMMENT '''
           , COLUMN_COMMENT
           , ''';') AS '修改脚本'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名'
  AND TABLE_NAME = '表名';

---

五、慢 SQL

-- 慢 SQL
SELECT *
FROM information_schema.processlist
WHERE user = 'root'
  AND command <> 'sleep'
ORDER BY time DESC
LIMIT 10;

---