MySQL 关于元数据的使用_mysql 元数据访问-CSDN博客

本文链接：https://blog.csdn.net/wb1046329430/article/details/113341445

文章目录

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    |
+--------------+------------+