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: 记录数据库表字段信息
- SCHEMA: 记录数据库信息
- STATISTICS: 记录表索引信息
- TABLES: 记录表信息
接下来，我们进入正题。
内功心法篇
第一层·上篇·初识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，这大大方便了在多环境开发情况下，处理不同环境数据库表结构同步的问题。
网友评论