探寻ALTER语句与information_schema不得不说

2020-09-18  本文已影响0人  王小奕

MySQL5.7 ALTER information_schema

参考

MySQL官网ALTER语句语法介绍
MySQL官网CREATE TABLE语句语法介绍
MySQL官网information_schema库介绍
MySQL官网CREATE INDEX语句语法介绍

前言

information_schema作为MySQL四大"原始法身"之一，自鸿蒙之初便存在，记录了数据库的各种元信息：

执行下面两条命令

use information_schema;
show tables;

可以看到information_schema大大小小有64张表，记录了各种各样的元数据，这里我们只关注其中4张表：

接下来，我们进入正题。

内功心法篇

第一层·上篇·初识COLUMNS表

列名 备注
TABLE_CATALOG column所在table的类型，总为def
TABLE_SCHEMA column所在table所属的database名
TABLE_NAME column所在table名
COLUMN_NAME column名
ORDINAL_POSITION column在table中的位置，方便SELECT from COLUMNS时排序
COLUMN_DEFAULT column的默认值，当字段明确声明默认值为NULL或者字段定义不包含DEFAULT语句时，值为NULL
IS_NULLABLE column是否可为空，如是，值为YES，否则为NO
DATA_TYPE column数据类型，只声明类型，不声明长度，类似的有一个COLUMN_TYPE
CHARACTER_MAXIMUM_LENGTH 对于string型的column，记录其最大字符长度
CHARACTER_OCTET_LENGTH 对于string型column，记录其最大bytes长度
NUMERIC_PRECISION 对于数值型column，记录其精度
NUMERIC_SCALE 对于数值型column，记录其数值范围
DATETIME_PRECISION 对于时间型column，记录其分秒精度
CHARACTER_SET_NAME 对于character string型column，记录其字符集名称
COLLATION_NAME 对于character string型column，记录其字符序名称
COLUMN_TYPE column数据类型，含长度声明
COLUMN_KEY column是否被索引
EXTRA 关于column的额外信息
PRIVILEGES 当前用户对该column拥有哪些权限
COLUMN_COMMENT column定义中的comment内容
GENERATION_EXPRESSION 对于生成型column，记录其生成的表达式，其他column类型此值为空

第一层·下篇·再探ALTER语句

MySQL官方语法如下：

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

其中alter_option是指操作类型，看语法可知，一个ALTER语句中可以执行多个字段变更操作。常用的有以下几种：

增加字段

ADD [COLUMN] col_name column_definition
    [FIRST | AFTER col_name]

这里的column_definition我们可以参考CREATE TABLE语句的定义：

data_type [NOT NULL | NULL] [DEFAULT default_value]
  [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']
  [COLLATE collation_name]
  [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}] /*没用过，不讨论*/
  [STORAGE {DISK | MEMORY}] /*没用过，不讨论*/
  [reference_definition]  /*没用过，不讨论*/
/*或者*/
data_type
  [COLLATE collation_name]
  [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']
  [reference_definition]

第二种生成型字段，楼主平日没接触过，没啥发言权，这里暂不做展开，以下内容重点讨论非生成型字段。举个例子：
存在一张表ductor.employee，表结构如下：

CREATE TABLE `employee` (
  `employee_id` bigint(20) NOT NULL COMMENT '员工编码',
  `name` varchar(100) NOT NULL COMMENT '员工姓名',
  `mobile` varchar(100) DEFAULT NULL COMMENT '手机号码',
  `position` varchar(100) DEFAULT NULL COMMENT '员工职位',
  `location` varchar(100) DEFAULT NULL COMMENT '员工入职地点',
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表'

执行下面这条语句：

ALTER TABLE employee
  ADD testcolumn INT (10) NOT NULL DEFAULT 10 COMMENT 'some description';

会在表employee中新增一个字段testcolumn(默认位置为6，即最后一位)，对应的，我们可以在information_schema库的COLUMNS表中查到该column对应的记录：

SELECT
  *
FROM
  information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = 'ductor'
  AND TABLE_NAME = 'employee'
  AND COLUMN_NAME = 'testcolumn';

得到结果：

字段名 字段值
TABLE_CATALOG def
TABLE_SCHEMA ductor
TABLE_NAME employee
COLUMN_NAME testcolumn
ORDINAL_POSITION 6
COLUMN_DEFAULT 10
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)
COLUMN_KEY
EXTRA
PRIVILEGES select,insert,update,references
COLUMN_COMMENT some description
GENERATION_EXPRESSION

观察不难得到ALTER语句与information_schema.COLUMNS表中各字段的对应关系。
(TO BE CONTINUED……)

第二层·上篇·初识STATISTICS表

列名 备注
NON_UNIQUE 如果index可以重复，值为1，否则为0
INDEX_NAME index名，如果是主键，值为 PRIMARY
SEQ_IN_INDEX 1个索引可能含多个字段，每个字段在STATISTICS中都是一条记录，SEQ_IN_INDEX记录各字段的列序号
COLUMN_NAME 列名
COLLATION 记录索引中该字段的排序方式，值为：A(升序)、D(降序)、NULL(不排序)
CARDINALITY 索引中唯一数值个数的一个预估值
SUB_PART 索引前缀。即，当string型字段被部分索引的时候，记录被索引字符个数，如果整个被索引，此值为NULL
PACKED 记录key被打包的方式，如果没有，值为NULL
NULLABLE 如果该column可以为NULL，值为YES，否则为''
INDEX_TYPE 索引方法，可能为：BTREE, FULLTEXT, HASH, RTREE
COMMENT index对应column未描述的信息，例：当index被关闭的时候，值为disabled
INDEX_COMMENT 索引声明备注

第二层·下篇·再探CREATE INDEX语句

语法

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

第三层·组合拳

结合前两节的分析，我们通过分析information_schema库的两张表：COLUMNS、STATISTICS可以获取某张table的基本信息，通过这些基本信息我们可以构造出对应的建表语句，同时，通过对比同一个table不同version的信息，我们可以增量地生成变更sql，这大大方便了在多环境开发情况下，处理不同环境数据库表结构同步的问题。

上一篇下一篇

猜你喜欢

热点阅读