本篇内容主要讲解“MySQL元数据有哪些”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“MySQL元数据有哪些”吧!
成都创新互联长期为超过千家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为任县企业提供专业的成都网站设计、做网站,任县网站改版等技术服务。拥有十多年丰富建站经验和众多成功案例,为您定制开发。
1. MySQL元数据
1.1. 元数据访问方法
数据库是数据的结构化集合。元数据是“有关数据的数据”。MySQL 通过以下方法提供对元数据的访问:
INFORMATION_SCHEMA:MySQL 服务器包含一个名为INFORMATION_SCHEMA 的数据库(模式)的数据字典,其中包含许多显示为表的对象。
SHOW 语句:用于获取服务器统计信息、模式和模式对象的相关数据的专用语法。 SHOW DATABASES 和SHOW TABLES返回包含数据库和表名的列表;SHOW COLUMNS生成表中列的定义;使用SHOW 语句需要有SELECT权限。
DESCRIBE:可用于查询表结构和列属性的SQL 语句快捷方式
mysqlshow:用作指向一些SHOW 语句的命令行命令。您设置的参数将决定要显示的信息,然后程序会发出相应的SHOW 语句并显示语句的结果。
下面将详细介绍这4中访问元数据的方法。
1.2. INFORMATION_SCHEMA 数据库
INFORMATION_SCHEMA 数据库充当数据库元数据的中央系统信息库,包含模式和模式对象、服务器统计信息(状态变量、设置、连接)。它未存储在硬盘上,从这个方面来看,它是“虚拟数据库”;但是,它与其他任何数据库一样包含表,与其他任何表一样可以使用SELECT 来访问其中表的内容
1) 查询INFORMATION_SCHEMA 中的表
mysql> SELECT TABLE_NAME
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'information_schema'
-> ORDER BY TABLE_NAME;
INFORMATION_SCHEMA 表包含以下类型的信息:
Ø 表信息
COLUMNS:表和视图中的列
ENGINES:存储引擎
SCHEMATA:数据库
TABLES:数据库中的表
VIEWS:数据库中的视图
Ø 分区
PARTITIONS:表分区
FILES:存储MySQL NDB 磁盘数据表的文件
Ø 权限
COLUMN_PRIVILEGES:MySQL 用户帐户所拥有的列权限
SCHEMA_PRIVILEGES:MySQL 用户帐户所拥有的数据库权限
TABLE_PRIVILEGES:MySQL 用户帐户所拥有的表权限
USER_PRIVILEGES:MySQL 用户帐户所拥有的全局权限
Ø 字符集支持
CHARACTER_SETS:可用的字符集
COLLATIONS:每个字符集的整理
COLLATION_CHARACTER_SET_APPLICABILITY:适用于特定字符集的整理
Ø 约束和索引
KEY_COLUMN_USAGE:关键列的约束
REFERENTIAL_CONSTRAINTS:外键
STATISTICS:表索引
TABLE_CONSTRAINTS:表的约束
Ø 服务器设置和状态
KEY_COLUMN_USAGE:约束
GLOBAL_STATUS:所有MySQL 连接的状态值
GLOBAL_VARIABLES:用于新的MySQL 连接的值
PLUGINS:服务器插件
PROCESSLIST:指示哪些线程正在运行
SESSION_STATUS:当前MySQL 连接的状态值
SESSION_VARIABLES:当前MySQL 连接的生效值
Ø 例程及相关信息
EVENTS:预定事件
ROUTINES:存储过程和功能
TRIGGERS:数据库中的触发器
PARAMETERS:存储过程和功能参数以及存储函数
Ø InnoDB
INNODB_CMP 和INNODB_CMP_RESET:对压缩的InnoDB 表的相关操作的状态
INNODB_CMPMEM 和INNODB_CMPMEM_RESET:InnoDB 缓冲池中压缩页面的状态
INNODB_LOCKS:InnoDB 事务所请求和持有的每个锁
INNODB_LOCK_WAITS:每个阻塞的InnoDB 事务的一个或多个行锁
INNODB_TRX:当前正在InnoDB 内部执行的所有事务
TABLESPACES:活动的表空间
有关INFORMATION_SCHEMA 表的更多信息,请参阅《MySQL 参考手册》:
http://dev.mysql.com/doc/refman/5.6/en/information-schema.html。
例如:查询INFORMATION_SCHEMA 数据库表列:
mysql> SELECT COLUMN_NAME
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
-> AND TABLE_NAME = 'VIEWS';
当使用SELECT 语句在INFORMATION_SCHEMA 表中检索元数据时,您可以使用任何常见的SELECT 功能。通过使用CREATE TABLE...SELECT 语句或INSERT...SELECT 语句,您可以将INFORMATION_SCHEMA 查询的结果检索到其他表中。您可以保存结果,以便稍后在其他语句中使用它们。
A. 显示用于给定数据库中表的存储引擎
mysql> SELECT TABLE_NAME, ENGINE
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'world_innodb';
B. 查找所有包含SET 列的表
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE DATA_TYPE = 'set';
C. 显示每个字符集的默认整理
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME
-> FROM INFORMATION_SCHEMA.COLLATIONS
-> WHERE IS_DEFAULT = 'Yes';
D. 显示每个数据库中表的编号
mysql> SELECT TABLE_SCHEMA, COUNT(*)
-> FROM INFORMATION_SCHEMA.TABLES
-> GROUP BY TABLE_SCHEMA;
E. INFORMATION_SCHEMA 表是只读的,无法用INSERT、DELETE 或UPDATE 之类的语句进行修改。如果执行这些类型的语句以尝试更改INFORMATION_SCHEMA 表中的数据,服务器将生成错误。
mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS;
ERROR 1044 (42000): Access denied for user
'root'@'localhost' to database 'information_schema'
2) 使用INFORMATION_SCHEMA 表创建Shell 命令
使用CONCAT 功能可以将字符串内容结合起来创建可在命令行中执行的shell 脚本。如示例所示,SQL 语句将生成一条输出,仅转储world_innodb 数据库中那些以单词“Country”开始的的表。输出将生成可以在shell 命令行上正确执行的shell 脚本。下一步是将此输出存储在一个可在shell 命令行中执行的批处理文件中。这通过添加子句INTO OUTFILE 来完成:
mysql> SELECT CONCAT("mysqldump -uroot -p ",
-> TABLE_SCHEMA, " ",TABLE_NAME, " >> ",TABLE_SCHEMA,".sql")
-> FROM TABLES WHERE TABLE_NAME LIKE 'Country%'
-> INTO OUTFILE '/Country_Dump.sh';
然后可以在命令行中执行此文件,命令行将运行示例的两个mysqldump 命令:
shell> \tmp\Country_Dump.sh
shell> \tmp\mysqldump -uroot -poracle world_innodb Country >>world_innodb.sql
shell> \tmp\mysqldump -uroot -poracle world_innodb Country_Language >>world_innodb.sql
3) 使用INFORMATION_SCHEMA 表创建SQL 语句
使用mysql 命令创建SQL 语句,使用-e 选项输入SELECT/CONCAT 语句:
shell> mysql -uroot -p --silent --skip-column-names -e
"SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'world_innodb';"
将导致以下语句发送到标准输出:
CREATE TABLE world_innodb.City_backup LIKE world_innodb.City;
CREATE TABLE world_innodb.Country_backup LIKE world_innodb.Country_backup;
CREATE TABLE world_innodb.CountryLanguage_backup LIKE world_innodb.CountryLanguage_backup;
INFORMATION_SCHEMA 表可创建可在命令行中执行的SQL 语句。本示例使用mysql 命令执行了一个语句,以制作world_innodb 数据库中所有表的精确副本。此命令将创建SQL 输出,如果执行该输出,将基于world_innodb 数据库中的表创建三个备份表。
注:--silent 命令在输出中删除列标题,--skip-column-names 命令删除输出中的格式(使输出类似于表的格式)。这两个命令用来确保对命令自身的解释是正确的,没有任何干扰执行的外部格式或标题行问题。
添加管道符号(|) 并随之执行mysql 命令会将这些SQL 语句发送到MySQL 服务器以便执行:
shell> mysql -uroot -p --silent --skip-column-names -e "SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'world_innodb';" | mysql -uroot -poracle
1.3. SHOW 语句
除了INFORMATION_SCHEMA 表之外,MySQL 还支持SHOW 和DESCRIBE 语句,作为访问元数据的备选方式。SHOW 和DESCRIBE 语法不如使用INFORMATION_SCHEMA 查询灵活,但是对于大多数用途,SHOW 和DESCRIBE 语法就足够了。在这些情况下,使用MySQL 特定语法通常会更快速、简单。MySQL支持的SHOW语句有:
SHOW DATABASES
SHOW TABLES
SHOW TABLE STATUS
SHOW CREATE TABLE
SHOW OPEN TABLES
SHOW INDEX
SHOW COLUMNS
SHOW PROCESSLIST
SHOW COLLATION
SHOW CHARACTER SET
SHOW 语句示例:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| world_innodb |
+--------------------+
mysql> SHOW TABLES;
mysql> SHOW TABLES FROM mysql;
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
mysql> SHOW COLUMNS FROM CountryLanguage;
mysql> SHOW FULL COLUMNS FROM CountryLanguage\G
mysql> SHOW DATABASES LIKE 'm%';
mysql> SHOW COLUMNS FROM Country WHERE `Default` IS NULL;
mysql> SHOW INDEX FROM City\G
mysql> SHOW CHARACTER SET;
mysql> SHOW COLLATION;
1.4. DESCRIBE 语句
DESCRIBE等效于SHOW COLUMNS,可以缩写为DESC
mysql> DESCRIBE
显示INFORMATION_SCHEMA 表信息
mysql> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(64) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
例如:
mysql> DESCRIBE table_name;
mysql> DESC table_name;
以下语句等效于上述DESCRIBE/DESC 示例:
mysql> SHOW COLUMNS FROM table_name;
但是,SHOW COLUMNS 支持可选的LIKE 和WHERE 子句,而DESCRIBE 不支持。
EXPLAIN :当指定表名称作为参数时,EXPLAIN 等效于DESCRIBE:
mysql> EXPLAIN table_name;
1.5. mysqlshow命令
mysqlshow 为各种格式的SHOW 语句提供了一个命令行界面,这些语句用于列出数据库的名称、数据库中的表或有关表列或索引的信息。
mysqlshow 的选项部分可包含任一标准连接参数选项,例如--host 或--user。如果默认连接参数不适合,则必须提供选项。mysqlshow 也接受特定于其自身运行的选项。使用--help 选项调用mysqlshow 可查看其选项的完整列表。mysqlshow 所执行的操作取决于已提供的非选项参数的数量。
mysqlshow 示例
显示所有数据库或特定数据库、表和/或列的相关信息:
A. 在没有参数的情况下,mysqlshow 将显示类似于SHOW DATABASES 的结果
shell> mysqlshow -u
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| world_innodb |
+--------------------+
B. 在使用单个参数的情况下,mysqlshow 将该参数解释为数据库名称,并针对该数据库显示类似于SHOW TABLES 的结果。
shell> mysqlshow world_innodb
C. 在有两个参数的情况下,mysqlshow 将参数解释为数据库和表名称,并针对该表显示类似于SHOW FULL COLUMNS 的结果。
shell> mysqlshow world_innodb City
D. 在有三个参数的情况下,其输出与两个参数的情况相同,不同之处在于:mysqlshow将第三个参数当做列名称,且仅针对该列显示SHOW FULL COLUMNS 输出。
shell> mysqlshow world_innodb City CountryCode
E. 如果命令行中最后的参数包含特殊字符,mysqlshow 会将该参数解释为模式,且仅显示与该模式匹配的名称。特殊字符包括:% 或 *(匹配任一字符序列),以及 _ 或 ?(匹配任一单个字符)。本示例中的命令仅显示那些名称始于w 的数据库。
shell> mysqlshow "w%"
注:这些示例要求在执行命令时使用用户和口令作为参数。
到此,相信大家对“MySQL元数据有哪些”有了更深的了解,不妨来实际操作一番吧!这里是创新互联网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
网页名称:MySQL元数据有哪些
分享URL:http://cqwzjz.cn/article/ishcjg.html