Server层统计信息字典表 | 全方位认识 information_schema

在上一篇《初相识|全方位认识information_schema》中，我们针对 information_schema 系统库做了一个简单的认识，本期我们将为大家带来系列第二篇《Server层统计信息字典表 | 全方位认识 information_schema》，下面请跟随我们一起开始 information_schema系统库的学习之旅吧~

1. COLUMNS

该表提供查询表对象中的列(字段)信息

• 该表为InnoDB引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 05:32:07> select * from COLUMNS where TABLE_NAME='sbtest1' limit 1\G;
*************************** 1. row ***************************
      TABLE_CATALOG: def
        TABLE_SCHEMA: sbtest
          TABLE_NAME: sbtest1
        COLUMN_NAME: id
    ORDINAL_POSITION: 1
      COLUMN_DEFAULT: NULL
        IS_NULLABLE: NO
          DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
  NUMERIC_PRECISION: 10
      NUMERIC_SCALE: 0
  DATETIME_PRECISION: NULL
  CHARACTER_SET_NAME: NULL
      COLLATION_NAME: NULL
        COLUMN_TYPE: int(10) unsigned
          COLUMN_KEY: PRI
              EXTRA: auto_increment
          PRIVILEGES: select,insert,update,references
      COLUMN_COMMENT: 
GENERATION_EXPRESSION: 
1 row in set (0.00 sec)

字段含义如下：

• TABLE_SCHEMA：显示列信息对应表所在的库名
• TABLE_NAME：显示列信息所在的表名
• COLUMN_NAME：显示列名称
• ORDINAL_POSITION：显示列在表中的创建顺序
• COLUMN_DEFAULT：显示列默认值
• IS_NULLABLE：显示列是否带有NULL属性
• DATA_TYPE：显示列的数据类型，不包含列的数据类型定义的额外属性
• CHARACTER_MAXIMUM_LENGTH：显示字符类型字段的定义长度
• CHARACTER_OCTET_LENGTH：显示字符类型字段的定义长度对应的字节数，如果是单字节字符集，则该字段值
• CHARACTER_MAXIMUM_LENGTH字段值相同(多字节字符集除外)
• NUMERIC_PRECISION：显示数字类型字段的精度（定义长度），如果不是数字类型则该类为NULL
• NUMERIC_SCALE：显示数字类型字段的标度（小数位数），如果不是数字类型则该类为NULL
• DATETIME_PRECISION：显示时间类型字段的精度（5.6版本之后，datetime时间类型字段在存储引擎层存储时都当作int类型处理，但存储时会比timestamp多一个字节），如果不是时间类型则该类为NULL
• CHARACTER_SET_NAME：显示表列的字符集，如果使用SHOW FULL COLUMNS语句查看，那么可以从结果集的Collation列中看到字符集类型，例如：Collation值为latin1_swedish_ci，则该字符集就是latin1
• COLLATION_NAME：显示列的校对规则
• COLUMN_TYPE：显示列的定义类型，包含列数据类型定义的额外属性（在show columns语句的结果集中该字段信息显示在Type列），例如：varchar(32)，该字段为 "MySQL extension" 列
• COLUMN_KEY：如果字段是索引列，则这里会显示出索引的类型
• EXTRA：显示生成列的类型，有效值为：VIRTUAL GENERATED或VIRTUAL STORED，该字段为 "MySQL extension" 列
• PRIVILEGES：显示列的可授予权限列表（这里指的是可对单个列授予的列级别权限），未列出的权限无法使用grant语句授予
• COLUMN_COMMENT：显示列的注释信息
• GENERATION_EXPRESSION：显示生成列的计算表达式，该字段为 "MySQL extension" 列

COLUMNS表中记录的信息实际上还可以使用show columns语句进行查询，下面给出两者的一个对比查询结果供大家鉴赏。

# 使用show columns语句来查询表对象的列信息
## 语法如下
SHOW [full] COLUMNS
FROM tbl_name
[FROM db_name]
[LIKE 'wild']

## 查询结果如下，当然，从这里我们也可以看到，使用show columns语句查询的列信息明显比直接查询columns表中的信息要少得多
root@localhost : information_schema 12:42:34> show columns from sbtest.sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL    | auto_increment |
| k    | int(10) unsigned | NO  | MUL | 0      |                |
| c    | char(120)        | NO  | MUL |        |                |
| pad  | char(60)        | NO  |    |        |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

## 还可以结合where语句来进行过滤
root@localhost : information_schema 12:44:46> show columns from sbtest.sbtest1 where Field like 'id%';
+-------+------------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

# 使用select语句来查询，这里我们就直接指定与show columns语句输出信息相对应的列来做一个对比
## 语法
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
[AND table_schema = 'db_name']
[AND column_name LIKE 'wild']

## 查询结果如下
root@localhost : information_schema 12:42:20> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT  FROM INFORMATION_SCHEMA.COLUMNS where table_schema='sbtest' and table_name='sbtest1';
+-------------+-----------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+-------------+-----------+-------------+----------------+
| id          | int      | NO          | NULL          |
| k          | int      | NO          | 0              |
| c          | char      | NO          |                |
| pad        | char      | NO          |                |
+-------------+-----------+-------------+----------------+
4 rows in set (0.00 sec)

# 另外，还可以使用desc语句和show create table tb_name;语句来查询列信息，这里不再赘述，请自行尝试
desc db_name.tb_name column_name;
desc db_name.tb_name;
show create table tb_name;

PS：查询某个表所有的字段的可授予权限，除了直接查询columns表之外，还可以使用show full columns语句，如下&#