数据类型的默认值

概述

数据类型的默认值可以是显式或隐式的。

可通过 DEFAULT value 子句显式指定列的默认值。例如：

CREATE TABLE t1 (
  i     INT DEFAULT -1,
  c     VARCHAR(10) DEFAULT '',
  price DOUBLE(16,2) DEFAULT 0.00
);

SERIAL DEFAULT VALUE 是一种特殊情况。在整型列的定义中，它是 NOT NULL AUTO_INCREMENT UNIQUE 的别名。

显式 DEFAULT 子句处理的某些方面受版本影响，如下所述。

• MySQL 8.0.13 起的显式默认值的情况

• MySQL 8.0.13 前的显式默认值的情况

• 隐式默认值的情况

MySQL 8.0.13 起的显式默认值的情况

DEFAULT 子句指定的默认值可以是字面量常量或表达式。后者需要放在括号内，以将其与字面量常量默认值进行区分。例如：

CREATE TABLE t1 (
  -- literal defaults
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  -- expression defaults
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())),
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
  p POINT       DEFAULT (Point(0,0)),
  j JSON        DEFAULT (JSON_ARRAY())
);

特例情况是：对于 TIMESTAMP 和 DATETIME 列，你可以将无括号包围的 CURRENT_TIMESTAMP 函数作为默认值。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

仅当值作为表达式编写时，才可为 BLOB、TEXT、GEOMETRY 和 JSON 数据类型指定默认值，即使表达式值是字面量：

• 这是允许的（字面量默认值指定为表达式）：

   CREATE TABLE t2 (b BLOB DEFAULT ('abc'));

• 这会报错（字面量默认值未指定为表达式）：

   CREATE TABLE t2 (b BLOB DEFAULT 'abc');

表达式默认值必须遵守以下规则。如果表达式包含不允许的部分，则会报错。

• 允许使用字面量、内置函数（确定性和非确定性均可）和操作符。

• 不允许使用子查询、参数、存储函数和用户定义的函数。

• 表达式默认值不能依赖于具有 AUTO_INCREMENT 属性的列。

• 列的表达式默认值可以引用其他表的列，但对于引用生成列（generated column）或拥有表达式默认值的列，必须出现在表定义前。也就是说，表达式默认值不能包含生成列或拥有表达式默认值的列的前向引用（forward reference）。

  forward refercene：Forward reference vs. forward declaration（译者注）

  这种顺序约束也适用于使用 ALTER TABLE 重新排序表列的情况。如果结果表拥有的表达式默认值包含对生成列或具有表达式默认值的列的前向引用，则该语句将失败。

  注意：如果表达式默认值的任意组成部分依赖 SQL 模式，则表的不同用法可能会产生不同的结果，除非在所有用法中 SQL 模式保持一致。

对于 CREATE TABLE ... LIKE 和 CREATE TABLE ... SELECT，目标表会保留原始表的表达式默认值。

如果表达式默认值引用一个非确定性函数，那么任何能导致表达式求值的语句对于基于语句复制来说都是不安全的。这包括 INSERT、UPDATE 和 ALTER TABLE 等语句。在这种情况下，如果禁用了二进制日志记录，则语句将正常执行。如果启用了二进制日志记录，且将 binlog_format 设置为 STATEMENT，则会记录并执行该语句，但会向错误日志写入一条警告消息，因为复制从属节点可能会出现分歧。当 binlog_format 设置为 MIXED 或 ROW 时，将不执行该语句，并向错误日志写入一条错误消息。

插入新行时，可通过省略列名或将列指定为 DEFAULT 来插入具有表达式默认值的列的默认值：

mysql> CREATE TABLE t4 (uid BINARY(16) DEFAULT (UUID_TO_BIN(UUID())));
mysql> INSERT INTO t4 () VALUES();
mysql> INSERT INTO t4 () VALUES(DEFAULT);
mysql> SELECT BIN_TO_UUID(uid) AS uid FROM t4;
+--------------------------------------+
| uid                                  |
+--------------------------------------+
| f1109174-94c9-11e8-971d-3bf1095aa633 |
| f110cf9a-94c9-11e8-971d-3bf1095aa633 |
+--------------------------------------+

但是，仅允许对具有字面量默认值的列使用 DEFAULT(col_name) 指定指名列的默认值，而不允许对具有表达式默认值的列使用。

并非所有存储引擎都支持表达式默认值，对于那些不支持的，会出现 ER_UNSUPPORTED_ACTION_ON_DEFAULT_VAL_GENERATED 错误。

如果默认值的数据类型与列声明的类型不同，则会根据 MySQL 常规的类型转换规则将其隐式转换为声明类型。详情请看 Section 12.2, “Type Conversion in Expression Evaluation”。

MySQL 8.0.13 前的显式默认值的情况

DEFAULT 子句定义的默认值必须是字面量常量，即不能是函数或表达式。例如，不能为日期列的默认值设置为函数，如 NOW() 或 CURRENT_DATE。唯一例外是：对于 TIMESTAMP 和 DATETIME 列，可以指定 CURRENT_TIMESTAMP 作为默认值。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

无法为 BLOB、TEXT GEOMETRY 和 JSON 数据类型指定默认值。

如果默认值的数据类型与列声明的类型不同，则会根据 MySQL 常规的类型转换规则将其隐式转换为声明类型。详情请看 Section 12.2, “Type Conversion in Expression Evaluation”。

隐式默认值的情况

如果无显式的 DEFAULT 值，则 MySQL 按照以下方式确定默认值：

如果列可接受 NULL 值，则使用显式 DEFAULT NULL 子句定义该列。

如果列不接受 NULL 值，则 MySQL 定义该列时不使用显式 DEFAULT 子句（译者注：不理解这句话）。例外：若列定义为 PRIMARY KEY 但无显式声明 NOT NULL，则 MySQL 将其创建为 NOT NULL 列（因为 PRIMARY KEY 必须为 NOT NULL）。

向没有显式 DEFAULT 子句的 NOT NULL 列输入数据时，如果 INSERT 或 REPLACES 语句不包含该列的值，或 UPDATE 语句设置该列为 NULL，则 MySQL 会根据当时的 SQL 模式处理该列：

• 若开启 SQL 严格模式，则事务表会报错，并且回滚该语句。对于非事务表，也会报错，但如果此报错发生在多行语句（multiple-row statement，译者注：即一条语句影响多行数据）的第二行或更后的行中，则依然会插入前面的行。

• 若未开启 SQL 严格模式，则 MySQL 将列设置为该列数据类型的隐式默认值。

假设表 t 定义如下：

CREATE TABLE t (i INT NOT NULL);

在这种情况下，i 没有明确的默认值，因此在严格模式下，下面的每条语句均会报错，并且不会插入行。当不开启严格模式，前两句会插入隐式默认值，第三句会报错，因为 DEFAULT(i) 无法产生值：

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

详情请看 Section 5.1.11, “Server SQL Modes”。

SHOW CREATE TABLE 语句能显示指定表的哪些列具有显式的 DEFAULT 子句。

隐式默认值的定义如下：

• 对于数值类型，默认值是 0。但对于具有 AUTO_INCREMENT 属性的整型或浮点型列，默认值是序列的下一个值。

• 对于 TIMESTAMP 以外的“日期和时间”类型，默认值是对应类型的“零”值。如果启用 explicit_defaults_for_timestamp 系统变量（详情请看 Section 5.1.8, “Server System Variables”)，则 TIMESTAMP 亦是如此。否则，对于表中的第一个 TIMESTAMP 列，默认值为当前日期和时间。详情请看 Section 11.2, “Date and Time Data Types”。
• 对于 ENUM 以外的字符串类型，默认值是空字符串。对于 ENUM 类型，默认值是第一个枚举值。