文章目录
- 1. 元数据
- 2. 使用示例
- 2.1. 信息查询
- 2.2. 生成脚本
- 2.3. 部署验证
- 2.4. 动态 SQL
- 2.5. 空间统计(重要)
1. 元数据
INFORMATION_SCHEMA
是 MySQL 实例中的一个数据库,它存储有关 MySQL 服务器维护的所有其他数据库的信息。提供对数据库元数据的访问,比如数据库或表的名称、列的数据类型或访问权限等关于 MySQL 服务器的信息。
可参考我对官方文档的翻译:第26章 INFORMATION_SCHEMA Tables
元数据本质上是关于数据的数据,这个数据统称为数据字典或者系统目录,数据库服务器需要不断的保存这个数据,同时,为了验证和执行 SQL 语句它需要能够快速的检索数据。
INFORMATION_SCHEMA
数据库有以下视图:
视图名称 | 提供的相关信息 |
SCHEMATA | 数据库 |
TABLES | 表和视图 |
COLUMNS | 表和视图的列 |
STATISTICS | 索引 |
USER_PRIVILEGES | 模式权限分配 |
SCHEMA_PRIVILEGES | 数据权限分配 |
TABLE_PRIVILEGES | 表权限分配 |
COLUMN_PRIVILEGES | 列权限分配 |
CHARACTER_SETS | 可用字符集 |
COLLATIONS | 各字符集对照信息 |
COLLATION_CHARACTER_SET_APPLICABILITY | 用于校对的字符集 |
TABLE_CONSTRAINTS | 唯一、外键和主键约束 |
KEY_COLUMN_USAGE | 与每个键列相关的约束 |
ROUTINES | 存储例程(过程和函数) |
VIEWS | 视图 |
TRIGGERS | 触发器 |
PLUGINS | 服务器插件程序 |
ENGINES | 可用的存储引擎 |
PARTITIONS | 表分区 |
EVENTS | 预定时间 |
PROCESSLIST | 正在运行的进程 |
REFERENTIAL_CONSTRAINTS | 外键 |
PARAMETERS | 存储过程和函数参数 |
PROFILING | 用户配置信息 |
2. 使用示例
2.1. 信息查询
(1)查询指定数据库bank
中所有表的名字:
SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'bank' AND TABLE_TYPE = 'BASE TABLE' ORDER BY 1;
(2)查询指定数据库bank
的所有视图的名字:
SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.`VIEWS` WHERE TABLE_SCHEMA = 'bank' ORDER BY 1;
(3)查询指定数据库bank
中的表account
的字段信息,按照添加的顺序查询:
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, ORDINAL_POSITION FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'bank' AND TABLE_NAME = 'account' ORDER BY ORDINAL_POSITION;
(4)查询指定数据库bank
中的表account
的索引信息:
SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'bank' AND TABLE_NAME = 'account' ORDER BY 1,3;
(5)查询指定数据库bank
中的所有约束信息
SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'bank' ORDER BY 3,1;
2.2. 生成脚本
可以通过查询information_schema
库中的相关视图,生成建表语句:
SELECT 'CREATE TABLE customer(' create_table_statement
UNION ALL
SELECT cols.txt FROM(
SELECT CONCAT(' ',COLUMN_NAME,' ',COLUMN_TYPE,
CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END,
CASE WHEN EXTRA IS NOT NULL THEN CONCAT(' ',EXTRA) ELSE '' END,
','
) txt FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = 'bank' AND TABLE_NAME = 'customer' ORDER BY ORDINAL_POSITION
) cols
UNION ALL
SELECT ' CONSTRAINT PRIMARY KEY ('
UNION ALL
SELECT cols.txt FROM (
SELECT CONCAT(
CASE WHEN ORDINAL_POSITION>1 THEN ' ,' ELSE ' ' END,
COLUMN_NAME
) txt
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'bank' AND TABLE_NAME = 'customer' AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY ORDINAL_POSITION
) cols
UNION ALL
SELECT ' )'
UNION ALL
SELECT ');';
生成的建表语句:
+----------------------------------------------------+
| create_table_statement |
+----------------------------------------------------+
| CREATE TABLE customer( |
| address varchar(30) , |
| city varchar(20) , |
| cust_id int(10) unsigned NOT NULL auto_increment, |
| cust_type_cd enum('I','B') NOT NULL , |
| fed_id varchar(12) NOT NULL , |
| postal_code varchar(10) , |
| state varchar(20) , |
| CONSTRAINT PRIMARY KEY ( |
| cust_id |
| ) |
| ); |
+----------------------------------------------------+
2.3. 部署验证
查询返回bank
库中每个表的列数,索引数,以及主键约束数:
SELECT tb1.TABLE_NAME,
(SELECT COUNT(*) FROM information_schema.`COLUMNS` clm WHERE clm.TABLE_SCHEMA = tb1.TABLE_SCHEMA AND clm.TABLE_NAME = tb1.TABLE_NAME) num_columns,
(SELECT COUNT(*) FROM information_schema.STATISTICS sta WHERE sta.TABLE_SCHEMA = tb1.TABLE_SCHEMA AND sta.TABLE_NAME = tb1.TABLE_NAME) num_indexes,
(SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS tc WHERE tc.TABLE_SCHEMA = tb1.TABLE_SCHEMA AND tc.TABLE_NAME = tb1.TABLE_NAME AND tc.CONSTRAINT_TYPE='PRIMARY KEY') num_primary_keys
FROM information_schema.`TABLES` tb1
WHERE tb1.TABLE_SCHEMA = 'bank' AND tb1.TABLE_TYPE = 'BASE TABLE' ORDER BY 1;
可以在部署前后两次执行该语句,对比两个结果集之间的区别。
2.4. 动态 SQL
设置变量以及使用变量:
mysql> SET @aa = 'sss';
Query OK, 0 rows affected
mysql> SELECT @aa;
+-----+
| @aa |
+-----+
| sss |
+-----+
1 row in set
mysql>
MySQL 执行动态 SQL:
-
SET
语句用来设置变量。 -
PREPARE
语句用来将字符串SQL
提交到数据库引擎,为了解析、安全检查和优化。 -
EXECUTE
语句用来执行SQL
,可以在这里使用变量。 -
DEALLOCATE PREPARE
语句用来释放执行中使用的所有数据库资源(如游标)。
mysql> SET @qry = 'SELECT product_cd, product_type_cd, date_offered, date_retired FROM product WHERE product_cd = ?';
Query OK, 0 rows affected
mysql> PREPARE dynsql FROM @qry;
Query OK, 0 rows affected
Statement prepared
mysql> SET @prodcd = 'SAV';
Query OK, 0 rows affected
mysql> EXECUTE dynsql USING @prodcd;
+------------+-----------------+--------------+--------------+
| product_cd | product_type_cd | date_offered | date_retired |
+------------+-----------------+--------------+--------------+
| SAV | ACCOUNT | 2000-01-01 | NULL |
+------------+-----------------+--------------+--------------+
1 row in set
mysql> DEALLOCATE PREPARE dynsql;
Query OK, 0 rows affected
mysql>
下面这个和上面效果一样,只是将SQL
语句由硬编码手写修改为通过information_schema
查询拼接出来的:
mysql> SELECT CONCAT('SELECT ',
CONCAT_WS(', ',
cols.col1,cols.col2,cols.col3,cols.col4,cols.col5,cols.col6,cols.col7,cols.col8,cols.col9
), ' FROM product WHERE product_cd = ?'
) INTO @qry FROM(
SELECT
MAX(CASE WHEN ORDINAL_POSITION = 1 THEN COLUMN_NAME ELSE NULL END) col1,
MAX(CASE WHEN ORDINAL_POSITION = 2 THEN COLUMN_NAME ELSE NULL END) col2,
MAX(CASE WHEN ORDINAL_POSITION = 3 THEN COLUMN_NAME ELSE NULL END) col3,
MAX(CASE WHEN ORDINAL_POSITION = 4 THEN COLUMN_NAME ELSE NULL END) col4,
MAX(CASE WHEN ORDINAL_POSITION = 5 THEN COLUMN_NAME ELSE NULL END) col5,
MAX(CASE WHEN ORDINAL_POSITION = 6 THEN COLUMN_NAME ELSE NULL END) col6,
MAX(CASE WHEN ORDINAL_POSITION = 7 THEN COLUMN_NAME ELSE NULL END) col7,
MAX(CASE WHEN ORDINAL_POSITION = 8 THEN COLUMN_NAME ELSE NULL END) col8,
MAX(CASE WHEN ORDINAL_POSITION = 9 THEN COLUMN_NAME ELSE NULL END) col9
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = 'bank' AND TABLE_NAME = 'product' GROUP BY TABLE_NAME
) cols;
Query OK, 1 row affected
mysql> SELECT @qry;
+--------------------------------------------------------------------------------------------------------+
| @qry |
+--------------------------------------------------------------------------------------------------------+
| SELECT product_cd, NAME, product_type_cd, date_offered, date_retired FROM product WHERE product_cd = ? |
+--------------------------------------------------------------------------------------------------------+
1 row in set
mysql> PREPARE dynsql FROM @qry;
Query OK, 0 rows affected
Statement prepared
mysql> SET @prodcd = 'SAV';
Query OK, 0 rows affected
mysql> EXECUTE dynsql USING @prodcd;
+------------+-----------------+-----------------+--------------+--------------+
| product_cd | NAME | product_type_cd | date_offered | date_retired |
+------------+-----------------+-----------------+--------------+--------------+
| SAV | savings account | ACCOUNT | 2000-01-01 | NULL |
+------------+-----------------+-----------------+--------------+--------------+
1 row in set
mysql> DEALLOCATE PREPARE dynsql;
Query OK, 0 rows affected
mysql>
2.5. 空间统计(重要)
统计bank
库中每个表所占空间的大小:
SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_LENGTH+INDEX_LENGTH AS TOTAL_LENGTH,
CONCAT(ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 3),'MB') AS TOTAL_SIZE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='bank' AND TABLE_TYPE = 'BASE TABLE';
+--------------+-------------+--------------+--------------+------------+
| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | TOTAL_LENGTH | TOTAL_SIZE |
+--------------+-------------+--------------+--------------+------------+
| account | 16384 | 65536 | 81920 | 0.078MB |
| branch | 16384 | 0 | 16384 | 0.016MB |
| business | 16384 | 0 | 16384 | 0.016MB |
| customer | 16384 | 0 | 16384 | 0.016MB |
| department | 16384 | 0 | 16384 | 0.016MB |
| employee | 16384 | 49152 | 65536 | 0.063MB |
| individual | 16384 | 0 | 16384 | 0.016MB |
| number_tb | 16384 | 0 | 16384 | 0.016MB |
| officer | 16384 | 16384 | 32768 | 0.031MB |
| product | 16384 | 16384 | 32768 | 0.031MB |
| product_type | 16384 | 0 | 16384 | 0.016MB |
| string_tb1 | 16384 | 0 | 16384 | 0.016MB |
| test | 16384 | 0 | 16384 | 0.016MB |
| transaction | 16384 | 49152 | 65536 | 0.063MB |
+--------------+-------------+--------------+--------------+------------+
统计bank
库中所有表所占空间的大小:
SELECT TABLE_SCHEMA, CONCAT(ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024, 3),'MB') AS TOTAL_SIZE
FROM information_schema.TABLES WHERE TABLE_SCHEMA='bank' AND TABLE_TYPE = 'BASE TABLE';
+--------------+------------+
| TABLE_SCHEMA | TOTAL_SIZE |
+--------------+------------+
| bank | 0.406MB |
+--------------+------------+