获取数据库元数据

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

MySQL 提供几种办法以使获取关于数据库和数据库里各种对象(也就是数据库的元数据)的信息,如下:

  1、SHOW语句,如SHOW TABLES等等。

  2、INFORMATION_SCHEMA数据库里的数据表。

  3、命令行程序,如mysqlshow或者是mysqldump。

  一、用SHOW语句获取

  获取服务器所管理的数据库。
SHOW DATABASES;
查看给定数据库的创建语句。
SHOW CREATE DATABASE db_name;

  列出默认数据库里的所有数据表。
SHOW TABLES;

  列出给定数据库里的所有数据表。
SHOW TABLES FROM db_name;

  查看给定数据表的创建语句。
SHOW CREATE TABLE tbl_name;

  查看指定数据表的数据列和索引信息。
SHOW COLUMNS FROM tbl_name;  这个语句和DESC 的输出是一样的。
SHOW INDEX FROM tbl_name;

  有几种SHOW语句还可以带有LIKE 'pattern'子句,用来把SHOW语句的输出限定在给定范围,并且允许使用通配符号。

  二、从INFORMATION_SCHEMA数据库获取元数据

  可以将这个数据库看成一个虚拟的数据库,这个数据库里的数据表是一些由不同的数据库元素数据构成的视图,这个数据库里有以下数据表项:

mysql> show tables from information_schema;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
28 rows in set (0.01 sec)

分别对这些列简单的说明。

  1、SCHEMATA、TABLES、VIEWS、ROUTIMES、TRIGGERS、EVENTS、PARTITIONS、COLUMNS,表示的信息分别是数据库,数据表,视图,存储例程,触发器,数据库里的事件,数据表分区和数据列的信息,以数据表为例,就是TABLES表,有以下列:

mysql> show columns from INFORMATION_SCHEMA.COLUMNS;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | YES  |     | NULL    |       | 
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       | 
| TABLE_NAME               | varchar(64)         | NO   |     |         |       | 
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       | 
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       | 
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       | 
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       | 
| DATA_TYPE                | varchar(64)         | NO   |     |         |       | 
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       | 
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       | 
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       | 
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       | 
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       | 
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       | 
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       | 
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       | 
| EXTRA                    | varchar(27)         | NO   |     |         |       | 
| PRIVILEGES               | varchar(80)         | NO   |     |         |       | 
| COLUMN_COMMENT           | varchar(255)        | NO   |     |         |       | 
+--------------------------+---------------------+------+-----+---------+-------|

  这是我查看一些数据表的记录

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' and TABLE_SCHEMA!='mysql'\G;
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: db_info
     TABLE_NAME: i_node
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 11
 AVG_ROW_LENGTH: 21
    DATA_LENGTH: 240
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 2048
      DATA_FREE: 0
 AUTO_INCREMENT: 16
    CREATE_TIME: 2012-09-07 03:07:37
    UPDATE_TIME: 2012-09-23 07:57:37
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 2. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: db_info
     TABLE_NAME: test
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 21
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2012-09-22 02:25:01
    UPDATE_TIME: 2012-09-22 02:37:18
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 3. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: test
     TABLE_NAME: test
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 20
    DATA_LENGTH: 60
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2012-09-07 20:44:41
    UPDATE_TIME: 2012-09-07 20:44:41
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
3 rows in set (0.00 sec)

 2、FILES。关于NDB硬盘数据文件的信息。

  3、TABLE_CONSTRAINS、KEY_COLUMN_USAGE:关于数据表和数据列上的约束条件的信息,一般唯一化索引和外键都属于这些约束条件。

  4、STATISTICS。关于数据表索引特性的信息。

  5、REFERENTIAL_CONSTRAINS。关于外键的信息。

  6、CHARACTER_SETS、COLLATIONS、COLLATION_CHARACTER_SET_APPLICABILITY。关于所支持的字符集,每种字符集的排序方式、每种排序方式与它的字符集的映射关系信息。

  7、ENGINES、PLUGINS。关于存储引擎和服务器插件的信息。

  8、USER_PRIVILEGES、SCHEMA_PRIVILEGES、TABLE_PRIVILEGES、COLUMN_PRIVILEGES。全局、数据库、数据表和数据列的权限信息。这些信息分别来自mysql数据库里的user,db,tables_priv,column_priv数据表。

  9、PROCESSLIST。在服务器内执行的线程的信息。

  10、GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、SESSION_STATUS。全局和会话级系统变量和状态变量的值。

  三、从命令行获取元数据

  先介绍一个命令的使用:mysqlshow。

mysqlshow[选项] [db_name [tbl_name [col_name]]]

  1、如果没有给出数据库,显示所有匹配的数据库。

  2、如果没有给出表,显示数据库中所有匹配的表。

  3、如果没有给出列,显示表中所有匹配的列和列类型。

  说明几个常用的选项信息:

  1、--keys。查看某给定数据表里的索引信息。

  2、--status。查看某给定数据库里的数据表的描述性信息。如:

[root@localhost ~]# mysqlshow --status db_info
Database: db_info
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| i_node | MyISAM | 10      | Dynamic    | 11   | 21             | 240         | 281474976710655  | 2048         | 0         | 16             | 2012-09-07 03:07:37 | 2012-09-23 07:57:37 |            | latin1_swedish_ci |          |                |         |
| test   | MyISAM | 10      | Fixed      | 3    | 7              | 21          | 1970324836974591 | 1024         | 0         |                | 2012-09-22 02:25:01 | 2012-09-22 02:37:18 |            | latin1_swedish_ci |          |                |         |
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------

  在使用该工具的时候,如果没有默认的数据库服务例程,不要忘了加上--host --user --password甚至是 --socket等信息。









====================================分割线================================



最新内容请见作者的GitHub页:http://qaseven.github.io/

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
|
弹性计算 安全 数据库
云上攻防-云服务篇&弹性计算&云数据库&实例元数据&控制角色&AK控制台接管
云上攻防-云服务篇&弹性计算&云数据库&实例元数据&控制角色&AK控制台接管
110 7
|
6月前
|
SQL 存储 关系型数据库
MySQL数据库——锁-表级锁(表锁、元数据锁、意向锁)
MySQL数据库——锁-表级锁(表锁、元数据锁、意向锁)
270 0
|
Oracle 关系型数据库 MySQL
数据库之-元数据 DatabaseMetaData 初学(二)
数据库之-元数据 DatabaseMetaData 初学(二)
167 0
|
Oracle 关系型数据库 MySQL
数据库之-元数据 DatabaseMetaData 初学(一)
数据库之-元数据 DatabaseMetaData 初学(一)
192 0
|
Windows
ArcGIS:如何连接文件夹、修改元数据样式、建立个人地理数据库、复制移动文件?
ArcGIS:如何连接文件夹、修改元数据样式、建立个人地理数据库、复制移动文件?
378 0
|
存储 SQL 数据采集
SaaS厂商数据库设计(3)-租户元数据管理&数据管理
SaaS厂商设计中元数据管理以及逻辑视图方式下数据管理
685 2
SaaS厂商数据库设计(3)-租户元数据管理&数据管理
|
存储 监控 数据库
【DBMS 数据库管理系统】数据仓库 数据组织 ( 数据组织级别 | 元数据 | 粒度 | 分割 | 数据组织形式 )(二)
【DBMS 数据库管理系统】数据仓库 数据组织 ( 数据组织级别 | 元数据 | 粒度 | 分割 | 数据组织形式 )(二)
199 0
|
前端开发 数据库 数据库管理
【DBMS 数据库管理系统】数据仓库 数据组织 ( 数据组织级别 | 元数据 | 粒度 | 分割 | 数据组织形式 )(一)
【DBMS 数据库管理系统】数据仓库 数据组织 ( 数据组织级别 | 元数据 | 粒度 | 分割 | 数据组织形式 )(一)
362 0
|
存储 数据库
【视频】配置信息管理 的 使用方法(三):查看和修改元数据、查看数据库的表视图存储过程等信息
  这个是查看和修改我们在上一个视频里添加的元数据的方法。   感谢 svnhost.cn 提供空间。  
875 0
|
算法 数据库
【自然框架】元数据的数据库结构的详细说明和示例(一):项目描述部分
自然框架在线演示:  http://pthuanyu.com/   【自然框架】PowerDesigner 格式的元数据的表结构   自然框架的源码、Demo、数据库、说明文档的下载,还是老地方:自然框架的源代码、Demo、数据库、配置信息管理程序下载(2010.
963 0