# 字段

创建数据表时需要定义它包含哪些列（column），即每行数据由哪些字段（field）组成。
- 假设一个数据表定义了 a、b、c 三个列，则表中的每行数据都需要有这些字段，并按列对齐。
- 创建数据表时，至少要定义一个字段。
- 创建数据表之后，可以增加字段。也可以修改已有的字段的配置，不过这会影响已有的所有数据行的该字段的值，可能不兼容。

# 定义字段

定义一个字段时，可添加多种配置，如下：

id int                -- 指定字段名、数据类型
      DEFAULT 1       -- 设置该字段的默认值为 1
      NOT NULL        -- 不允许取值为 NULL ，否则会报错
      AUTO_INCREMENT  -- 让写入的值自动递增
      COMMENT '编号'  -- 添加注释

MySQL 中的 NULL 是一个特殊的值，比空字符更占存储空间，不能使用通用的查询语句，还不利于索引优化。
- 判断一个字段的值是否为 NULL ，只能用 IS NULL 或 IS NOT NULL ，返回的结果为 1 或 0 。不能用 = NULL 或 != NULL ，否则返回的结果总是为 NULL 。
- 因此，建议将每个字段都声明为 NOT NULL 。

# 默认值

每个字段默认声明了 DEFAULT NULL ，表示采用当前数据类型对应的默认值。
- 比如整型、浮点型字段的默认值为 0 ，字符串型字段的默认值为 "" ，datetime 类型字段的默认值为 "0000-00-00 00:00:00" 。
- 用户可在定义字段时，修改其 DEFAULT 默认值。
默认值生效的几种情况：
- 如果 INSERT 一行数据时，某个字段的插入值为 NULL ，则考虑：
  - 如果该字段声明了 NOT NULL ，则报错：Column cannot be null
  - 如果该字段没声明 NOT NULL ，则赋值为 NULL 。
- 如果 INSERT 一行数据时，某个字段没有插入值，则考虑：
  - 如果该字段声明了 DEFAULT NULL ，则赋值为 NULL 。如果同时声明了 NOT NULL ，则赋值为当前数据类型的默认值。
  - 如果该字段没声明 DEFAULT NULL ，即用户设置了其它默认值，比如 DEFAULT 1 ，则赋值为该默认值。
- 如果一个字段原本允许取值为 NULL ，现在改为 NOT NULL 。则原来该字段取值为 NULL 的数据行，取值会自动改为 0 或 "" 等默认值。
  - 这里的默认值是指当前数据类型的默认值，不是用户定义的默认值。
- 如果用 UPDATE tb1 SET name=NULL; 的方式将一个字段的值改为 NULL ，则会实际赋值为当前数据类型的默认值。
  - 即使该字段声明了 NOT NULL ，也可以执行该 UPDATE 语句。
- 如果字段声明了 AUTO_INCREMENT ，则 INSERT 插入值为 NULL 时，或没有插入值时，实际赋值都采用递增值，等于前一个主键的取值加一。

# 主键

一个数据表中最多声明一个主键，也可以不声明主键。
- 用 PRIMARY KEY (id) 的格式即可将一个字段声明为主键。
- 用 PRIMARY KEY (id, name) 的格式可以将多个字段声明为主键，称为复合主键。
MySQL 规定了主键必须声明为 NOT NULL ，并且只有主键能定义成 AUTO_INCREMENT 。

# 数据类型

在数据表中定义一个字段时，需要声明其数据类型。

# 整型

tinyint       # 占 1 字节的存储空间
smallint      # 2 字节
mediumint     # 3 字节
int           # 4 字节
bigint        # 8 字节

上述数据类型默认为带符号的（signed），也可以声明为无符号的（unsigned）。
- 比如 tinyint 的取值范围为 -128 ~ 127 ，而 tinyint unsigned 的取值范围为 0 ~ 255 。

例：

CREATE TABLE tb1(
  id    int,          -- 定义一个字段，名为 id ，数据类型为 int
  id2   int unsigned, -- 设置为无符号的
  id3   int(5),       -- 设置总位数为 5 ，如果不足，则自动在左侧添加 0 补齐。比如写入 18 时，存储为 00018
};

# 浮点型

float         # 4 字节
double        # 8 字节
decimal(m,d)  # 小数。m 是总位数，d 是小数位数

# 字符串型

char(n)
varchar(n)

tinytext      # 最大容量为 2^8 -1 = 255   字符，使用开头的 1 字节存储值的长度
text          # 最大容量为 2^16-1 = 65535 字符
mediumtext    # 最大容量为 2^24-1 ≈ 16 MB 字符
longtext      # 最大容量为 2^32-1 ≈ 4 GB  字符

char(n)
- n 的取值范围为 0~255 ，默认为 1 。
  - 如果写入的字符数不足 n ，则自动在末尾加上空格填充，因此固定占用 n 字符的存储空间。
- 适合存储固定长度的字符串，但可能浪费存储空间。索引速度快。
- 检索 char 类型的值时，会自动删掉末尾的空格。检索 varchar、text 等类型的值时不会删掉。
- char(0) 字段只占用 1 bit 的存储空间，只能写入一个 Null 或空字符串作为值。
varchar(n)
- n 的取值范围为 0~65535 。
  - 当 n<256 时，使用开头的 1 字节存储值的长度，因此最多允许写入 n-1 个字符。
  - 当 n>=256 时，使用开头的 2 字节存储值的长度，因此最多允许写入 n-2 个字符。
- 适合存储长度可变的字符串，不会浪费存储空间。索引速度慢。
- 如果给 char、varchar 字段写入的值超过最大长度，则会被自动截断。
  - 但如果启用了 STRICT sql_mode ，则不会截断，而是拒绝写入并报错。此时应该先检查写入的值是否过长。
- 如果创建 varchar、varbinary 字段时，长度超过 65535 ，则会分别自动转换成 text、blob 类型。
  - 但如果启用了 STRICT sql_mode ，则会报错。
text
- 创建 char、varchar 字段时需要声明其长度，而创建 text 字段时不需要。
- 适合存储长度可变且很长的字符串。
MySQL 表中，每行的所有字段累计最多占用 65535 字节，否则不能创建表。
- 这主要限制了 char、varchar 字段的数量和大小，因为 text、blob 字段是分开存储在其它位置，在行内只占用 9~12 字节的存储空间。

# 二进制型

binary(n)
varbinary(n)

tinyblob
blob          # 最大容量为 2^16-1 = 65535 字节
mediumblob
longblob

binary、varbinary、blob 的用法、容量与 char、varchar、text 类似。
- 以字符串格式存储的字段，是以字符为单位计算长度。以二进制格式存储的字段，是以字节为单位计算长度。
- 例如，在 char(3) 字段中写入 3 个 utf8 字符，则最多占用 3*3 字节的存储空间，
如果创建 char、varchar、text 字段时声明了 CHARACTER SET binary ，则会分别自动转换成 binary、varbinary、blob 类型。

# 时间类型

date          # 3 字节，格式为 `YYYY-MM-DD`
time          # 3 字节，格式为 `HH:MM:SS`
datetime      # 8 字节，由 date 和 time 组合而成，格式为 `YYYY-MM-DD HH:MM:SS`
timestamp     # 4 字节，自动存储修改的时间戳

# 其它类型

json          # MySQL 5.7 增加的数据类型

# 外键

可以让表 A 中的一个字段引用表 B 中的一个字段，从而让两张表的每行数据建立映射关系。该字段称为外键（foreign key）。
建立外键的条件：
- 表 A 的外键字段的值在表 B 中都存在。
- 表 B 中被引用的字段的值是 unique 的。
外键的几种映射关系：
- 一对一：表 A 中的数据与表 B 中的数据一一对应。
- 多对一：表 A 中的多条数据对应到表 B 中的一条数据。（此时，要在多的那方数据表中定义外键）
- 多对多：表 A 中的多条数据对应到表 B 中的多条数据。（此时，可以在任意一方数据表中定义外键）

定义外键的例子：

CREATE TABLE tb1(
    id int primary key AUTO_INCREMENT,     -- 定义 id 字段，并声明为主键
    num int NOT NULL,                      -- 定义字段 num
    foreign key(num) references tb2(id)    -- 将字段 num 声明为外键，引用 tb2 表的 id 字段
);

ALTER TABLE tb1 ADD constraint num foreign key(num) references tb2(id);  -- 将字段 num 声明为外键

ALTER TABLE tb1 DROP foreign key num;      -- 取消外键

在 tb1 中插入一行包含外键的数据时，该外键值必须在 tb2 中存在，否则会报错。
删除 tb2 之前，必须先删除 tb1 中关联到它的所有行数据。