Anonymous' Blog

这是一个很常见的 SQL 建表语句：

CREATE TABLE `people` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SQL 建表语句结构如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]

我们对照结构可以看到我们的建表语句最后一部分是表选项，而在这里我们指定了两个选项：引擎和字符集，那么总共都有哪些选项可以设置呢？参照MySQL官方文档，我们一起来看看。

AUTO_INCREMENT

MySQL 一张表中可以指定一个字段为 auto increment，这样在当这个字段没有指定值时就在当前表最大值的基础上自增，在插入了自增的一列后，可以通过：

select LAST_INSERT_ID();

来获取 MySQL 自动生成的 ID。

在建表的时候，可以通过 AUTO_INCREMENT 指定自增开始的值：

create table if not exists peoples (
    id int primary key auto_increment,
    name varchar(40) null
) AUTO_INCREMENT = 13;

AVG_ROW_LENGTH

CHARACTER SET

可以指定这个表使用的默认字符集（因为也可以针对列单独设置字符集），如果表不指定或者设置成 default 的话则会使用库的默认字符集，如果数据库的也没有设置的，MySQL Server 的默认字符集是 utf8mb4。

MySQL 目前支持的字符集，可以通过：SHOW CHARACTER SET 查看，我们可能用到的：ascii，gb2312，gbk，big5，utf16，utf8，utf8mb4 等字符编码集都是支持的。

指定 people 表的字符集为 utf8mb4

create table if not exists people (
    id int primary key auto_increment,
    name varchar(40) null
) character set = 'utf8mb4';

一个表的默认字符集指定之后也是可以修改的：

ALTER TABLE people CONVERT TO CHARACTER SET 'utf8'; # 不需要emoji，从 utf8mb4 修改为 utf8

可以通过查看建表语句来查看一个表的字符集：

SHOW CREATE TABLE people;

输出：
CREATE TABLE `people` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

可以看到字符集已经修改成功了。

CHECKSUM

1 表示需要 MySQL 为表维持一个校验和，以便于快速的发现数据受损，不过一定程度上会影响更新性能。

COLLATE

指定使用哪个字符排序规则，这个是由于不同的字符在不同的文化语义中可以会有不同的顺序，同时也交给用户更多的选择，比如要不要区分大小写。

每个字符集都会有一个默认的排序规则，以 utfmb4 为例，通过 show collation where Chareset='utf8mb4' 可以看到 utf8mb4 有七十多个字符排序规则，而我们可以看到它默认的字符序是utf8mb4_0900_ai_ci：

SHOW CHARACTER SET where Charset= 'utf8mb4';

输出：

Charset	Description	Default collation	Maxlen
utf8mb4	UTF-8 Unicode	utf8mb4_0900_ai_ci	4

ai 表示 accent insensitive，口音无关，比如：e, è, é, ê 会被视为一样的；

ci 则代表 case insensitive，大小写无关，A 和 a 会被视为同样的字符，所以我们会看到非常神奇的一幕：

执行：select 'A' = 'a','A'= 'A','A' = 'B';

输出：

'A' = 'a'	'A'= 'A'	'A' = 'B'
1	1	0

所以 select * from table where string="xxx" 的时候就要注意了，默认是不区分大小写的，可以临时指定使用其他的字符序，或者根据业务场景判断是否需要在建表的时候就把表的字符序就设置为大小写敏感的字符序，比如：utf8mb4_0900_as_cs 等包含 _cs 的字符序：

create table if not exists people (
    id int primary key auto_increment,
    name varchar(40) null
) character set = 'utf8mb4' collate = 'utf8mb4_0900_as_cs';

COMMENT

加个注释，方便日后维护，写注释是个好习惯，有的公司的 SQL 工单平台甚至会强制必须有注释。

create table if not exists people (
    id int primary key auto_increment,
    name varchar(40) null
) comment '存储人物信息';

COMPRESSION

开启表压缩选项，只对 InnoDB 引擎有效，支持：Zlib 和 LZ4两种压缩算法，可以置成 None 关闭。

CONNECTION

FEDERATED 引擎可以远程连接到其他数据库映射到本地，可以通过这个字段来指定对应远程数据库的连接字符串。

(DATA | INDEX) DIRECTORY

InnoDB 引擎在建表的时候可以通过 DATA DIRECTORY 指定 .idb 文件的位置，这个特性在当前分区磁盘空间不足需要使用其他分区时会很有用。

而 MyIsam 引擎不是聚簇的，它的数据和索引是分开的，所以不仅可以通过 DATA DIRECORY 指定生成的 .MYD 数据文件位置，还可以通过 INDEX DIRECOTRY 来指定生成的 .MYI索引文件。

不过官网上特意申明了一个 bug，自己指定的文件目录不能包含 MySQL 文件目录，不然会报错：Incorrect arguments to DATA DIRECTORY。

DELAY_KEY_WRITE

这个选项只对 MyISAM 生效，当 DELAY_KEY_WRITE置为1时，对MyISAM执行更新操作时，只更新磁盘中的数据，但是索引只更新内存，提升更新速度。如果 crash 之后也可以根据数据来修复索引。

ENCRYPTION

ENCRYPTION='Y'代表开启表空间加密，’N’代表不开启，不过需要加载加密插件才可以。

ENGINE

这应该是被使用最多的选项了，为要创建的这个表单独指定存储引擎。支持的引擎如下：

引擎	描述
InnoDB	支持事务、行锁、外键等高级特性，主键聚簇。具有良好的性能和可靠性。MySQL默认的存储引擎。
MyISAM	由ISAM（Indexed Sequential Access Metod）引擎改良，可移植性好，占用空间小，但是表级别的锁使得并发读写性能较差，适用于只有查询或者读多写少的场景，如：服务配置存储等。
Memory	又被叫做：Heap，数据存储在内存中，可靠性较低，一般用于临时表场景使用。另外 InnoDB 也支持内存缓存可以大量甚至全部数据载入内存，所以这个引擎使用场景越来越少了。
CSV	数据通过 CSV(Comma-Separated Values)的格式存储，可读性和可操作性非常好，但是由于数据未经过索引，性能很差，所以一般用于将其他表中的数据导过来用于与其他应用交互。
Archive	支持压缩，无索引，适用于存储大量冷门归档数据。
Blackhole	黑洞，不存储数据，查询也都会返回空。但这也并不代表这个引擎没有实际价值，它虽然不存储数据但是 binlog 还是会有，所以经常用于做为伪从库减轻主库压力或者记录binlog用。
NDB	全称Network DataBase，是一个分布式存储引擎。
Merge	可以用于将一系列 MyISM 抽象为一张表。
Federated	可以将其他远程数据的表映射到本地。
Example	主要是一个 demo，供开发人员学习如何开发一个存储引擎。

各引擎支持的功能特性对比：

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B树索引	Yes	Yes	Yes	No	No
备份/恢复	Yes	Yes	Yes	Yes	Yes
分布式集群支持	No	No	No	No	Yes
聚簇索引	No	No	Yes	No	No
数据压缩	Yes	No	Yes	Yes	No
数据缓存	No	N/A	Yes	No	Yes
数据加密	Yes	Yes	Yes	Yes	Yes
外键	No	No	Yes	No	Yes
全文搜索（倒排）	Yes	No	Yes	No	No
地理空间数据	Yes	No	Yes	Yes	Yes
地理空间索引	Yes	No	Yes	No	No
散列索引	No	Yes	No	No	Yes
索引缓存	Yes	N/A	Yes	No	Yes
锁粒度	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
冗余存储 (note 1)	Yes	Limited	Yes	Yes	Yes
存储限制	256TB	RAM	64TB	None	384EB
T树索引	No	No	No	No	Yes
事务	No	No	Yes	No	Yes
更新数据字典的统计信息	Yes	Yes	Yes	Yes	Yes

注：关于存储限制虽然上限都很高，但是将数据都存储在一个文件的引擎会受操作系统的文件大小限制（TB级别）。

Example，创建一个 MyISAM 的表：

create table if not exists people (
    id int primary key auto_increment,
    name varchar(40) null,
) comment '存储人物信息' engine='myisam;

ENGINE_ATTRIBUTE

保留字段，以后用于透传信息到引擎层。

INSERT_METHOD

MERGE 存储引擎使用。

KEY_BLOCK_SIZE

指定压缩后的页大小，单位为 KB，不过MySQ不一定会按照设定的数字来运行。

InnoDB的默认页大小为16KB，可以查看 innodb_page_size 变量：show variables like 'innodb_page_size';

所以压缩后的大小有：1KB、2KB、4KB、8KB这几个选项。

MAX_ROWS

最大的行数，这个是给MySQL的优化建议，告诉它至少要能存这么多数据。

MIN_ROWS

同最大行数，也是一个建议，告诉MySQL我至少要存这么多行数据。

PACK_KEYS

MyISAM专用参数，压缩索引。

PASSWORD

废弃了

ROW_FORMAT

行格式决定了数据在文件表空间中某一页的具体格式。

InnoDB 有两种文件格式，早期为：Antelope，对应有两种行格式可选：Compact 和 Redundant，后面又新增了Barracuda格式，新增了 Compressed 和 Dynamic 两种格式。

5.6及之前的版本默认的行格式都是 Compact，而 5.6 之后的 5.7 和 8.0 则设为了 Dynamic。

Redundant 是早期的文件格式和早期MySQL版本兼容，在列的值小于 768 字节时会将数据直接存储在当前页中，超过才会存储至溢出页，这虽然能减少小字段的 IO，但是可能会导致 B+树节点太满，一个页中存不了多少行。

Compact 相较于 Redundant 能减少 20% 的存储空间，对于溢出列的处理和 Redundant 一致。

Dynamic 对于可变长列的存储更加智能，会综合考虑整行的大小来决定要不要把列存储在溢出页。

Compressed 会对数据使用 zlib 压缩，能进一步减少空间占用。

各种格式的特性对比：

行格式	紧凑存储	变长列完全溢出页存储	大索引支持	压缩	支持的表空间类型
REDUNDANT	No	No	No	No	system, file-per-table, general
COMPACT	Yes	No	No	No	system, file-per-table, general
DYNAMIC	Yes	Yes	Yes	No	system, file-per-table, general
COMPRESSED	Yes	Yes	Yes	Yes	file-per-table, general

SECONDARY_ENGINE_ATTRIBUTE

同 ENGINE_ATTRIBUTE。

STATS_AUTO_RECALC

是否自动重新计算 InnoDB 的表统计信息，设置为 1 会在表中数据变动超过 10% 触发，也可以关闭自动触发手动通过 ananyze table 触发。

更准确的统计信息可以让执行计划优化的更好。

STATS_PERSISTENT

是否开启统计信息，默认开启。

STATS_SAMPLE_PAGES

统计信息的页面采样数量，更大准确，采样时间越久，默认 20 页。

TABLESPACE

用于单独为这个表覆盖 INNODB_PER_FILE_TABLE ，可以让这个表单开一个表空间，或者所有表共用一个表空间。

UNION

Merge 存储引擎使用，用于将一系列 MyISAM 表联合成一个。