MySQL的元数据
元数据访问方式介绍
查询 INFORMATION_SCHEMA 数据库表。
– 其中包含 MySQL 数据库服务器所管理的所有对象的相关数据
• 使用 SHOW 语句。
– 用于获取数据库和表信息的 MySQL 专用语句
• 使用 DESCRIBE(或 DESC)语句。
– 用于检查表结构和列属性的快捷方式
• 使用 mysqlshow 客户端程序。
– SHOW 语法的命令行程序
INFORMATION_SCHEMA 数据库介绍
• 充当数据库元数据的中央系统信息库
– 模式和模式对象
– 服务器统计信息(状态变量、设置、连接)
• 采用表格式以实现灵活访问
– 使用任意 SELECT 语句
• 是“虚拟数据库”
– 表并非“真实”表(基表),而是“系统视图”
– 根据当前用户的特权动态填充表
注意:为什么说是虚拟数据库。因为在数据库的data目录下并不存在这个数据库information_schema,而是在数据库启动之后生成"系统视图"。也是为了保护数据库的安全。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | mysql> use information_schema; Database changed mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLESPACES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | | INNODB_LOCKS | | INNODB_TRX | | INNODB_SYS_DATAFILES | | INNODB_LOCK_WAITS | | INNODB_SYS_TABLESTATS | | INNODB_CMP | | INNODB_METRICS | | INNODB_CMP_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMPMEM_RESET | | INNODB_FT_DELETED | | INNODB_BUFFER_PAGE_LRU | | INNODB_SYS_FOREIGN | | INNODB_SYS_COLUMNS | | INNODB_SYS_INDEXES | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_SYS_FIELDS | | INNODB_CMP_PER_INDEX_RESET | | INNODB_BUFFER_PAGE | | INNODB_CMPMEM | | INNODB_FT_INDEX_TABLE | | INNODB_FT_BEING_DELETED | | INNODB_SYS_TABLESPACES | | INNODB_FT_INDEX_CACHE | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_TABLES | | INNODB_BUFFER_POOL_STATS | | INNODB_FT_CONFIG | +---------------------------------------+ 59 rows in set (0.00 sec) |
我们将来需要关注的是跟INNODB相关的,比如跟事务、锁、数据库对象相关的表
使用命令查询元数据
# 在tables查找数据名为world的表名、引擎。
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world';
# 在clolums中查找数据类型为set的数据库名、表名、列名
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'set';
#查看每个字符集默认的校验规则
SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE IS_DEFAULT = 'Yes';
# 统计下所有库下的所有表的个数
SELECT TABLE_SCHEMA, COUNT(*) FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;
# 由于是虚拟的表只能查询,不能修改和删除。所以删除会报错!
DELETE FROM INFORMATION_SCHEMA.VIEWS;
使用 INFORMATION_SCHEMA 表获取有关创建 shell 命令的信息
将 SELECT 和 CONCAT 一起使用以创建 mysqldump 脚本
1 2 3 4 5 | select concat( "mysqldump -uroot -poldboy123 " , table_schema, " " ,table_name, ">>" , "/backup/" , table_schema, "_" ,table_name, ".bak.sql" ) from information_schema.tables where table_schema= 'world' ; |
直接生成了备份world库所有表的mysqldump的语句,执行结果如下:
使用 mysql 命令生成创建 SQL 语句
1 2 3 4 | SELECT CONCAT( 'CREATE TABLE ' , TABLE_SCHEMA, '.' , TABLE_NAME, '_backup LIKE ' , TABLE_SCHEMA, '.' , TABLE_NAME, ';' ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world' ; |
直接生成创建world库中所有表的备份表的表结构的语句,执行结果如下:

SHOW 语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | show databases show create database oldboy show tables show create table t1 SHOW databases:列出所有数据库 SHOW TABLES:列出默认数据库中的表 SHOW TABLES FROM <database_name>:列出指定数据库中的所有表 SHOW COLUMNS FROM <table_name>:显示表的列结构 SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息 SHOW CHARACTER SET:显示可用的字符集及其默认整理 SHOW COLLATION:显示每个字符集的整理 SHOW STATUS:列出当前数据库状态 SHOW VARIABLES:列出数据库中的参数定义值 |
mysqlshow用命令行查询数据库信息
1 2 3 4 5 6 7 8 9 10 11 12 13 | [root@minion_1 ~]# mysqlshow -p123456 Warning: Using a password on the command line interface can be insecure. +--------------------+ | Databases | +--------------------+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | test | | world | +--------------------+ |
拥抱不确定,未来可期!喜欢该文章请不吝点赞推荐,如有疑问欢迎留言,我们一起探讨。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步