1. 首页
  2. 系列专栏
  3. 缓存 / 数据库

16、MySQL 中数据类型的默认值

分类：缓存 / 数据库 标签：MySQL 拾遗

MySQL 中，所有的数据类型，都可以显式或隐式的拥有默认值。

我们可以使用 DEFAULT 约束显式的为列指定一个默认值。比如

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

在上面这条语句中，我们为 int 类型的 i 列指定了默认值 -1 ，为 varchar 类型的 c 列指定了默认值 '' ，为 double 类型的 price 列指定了默认值 0.00。也就是说，当我们插入数据的时候，并不需要完整的为每一列指定值。如果没有指定值，那么 MySQL 就会使用默认值填充。

但是，DEFAULT 约束有有一个特例，就是 SERIAL DEFAULT VALUE。等于类型为整形的列，它的作用相当于 NOT NULL AUTO_INCREMENT UNIQUE。

其实这不怪 DEFAULT ，是 SERIAL 的锅。

但是，default 并不是没有 bug，显式 DEFAULT 约束处理的某些方面依赖于版本。

MySQL 8.0.13 中处理显式默认值

DEFAULT 约束中指定的默认值可以是文字常量或表达式。

如果使用表达式作为默认值，则需要表达式默认值括在括号内 () ，以将它们与文字常量默认值区分开来。

例如

CREATE TABLE t1 (
  -- 常量默认值
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  -- 表达式默认值
  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 列。

对于TIMESTAMP 和 DATETIME 列，我们可以将 CURRENT_TIMESTAMP 函数指定为默认值，而需要添加括号。

CREATE TABLE t1 (
  -- 常量默认值
  c TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
  u DATETIME          DEFAULT CURRENT_TIMESTAMP,
);

有关TIMESTAMP 和 DATETIME 列的详细内容，可以访问我们的 傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 上 )

而对于BLOB，TEXT，GEOMETRY和 JSON 数据类型，只有在将值写为表达式时，才能分配默认值，即使表达式值是文字也是如此。

例如，下面这种写法是允许的，也就是使用文字字面量表达式

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

但下面这种写法则是不允许的，因为它是一个字面量而不是一个表达式

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

表达式默认值必须遵守以下规则。如果表达式包含不允许的构造，则会发生错误

  • 允许使用文字，内置函数（确定性和非确定性）和运算符
  • 不允许使用子查询，参数，变量，存储函数和用户定义的函数
  • 表达式默认值不能依赖于具有 AUTO_INCREMENT 属性的列。
  • 某一列的表达式默认值可以引用另外一张表中的列，但是对生成的列或具有表达式默认值的列的引用必须是对于在表定义中较早出现的列。也就是说，表达式默认值不能包含对生成的列或具有表达式默认值的列的前向引用。翻译成白话文就是，引用的列必须已经存在。
  • 排序 ( ordering ) 约束也适用于使用 ALTER TABLE 重新排序表列。如果结果表的表达式默认值包含对具有表达式默认值的生成列或列的前向引用，则该语句将失败

注意： 如果表达式默认值的任何组件取决于 SQL 模式，则表的不同用法可能会出现不同的结果，除非在所有使用过程中 SQL 模式都相同

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

如果表达式默认值引用非确定性函数，则导致表达式计算的任何语句对于基于语句的复制都是不安全的。包括 INSERT，UPDATE 和 ALTER TABLE 等语句

插入新行时，可以通过省略列名或将列指定为 DEFAULT 来插入具有表达式 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 类型转换规则对声明的类型进行隐式强制。

MySQL 8.0.13 之前的版本中处理显式默认值

MySQL 8.0.13 之前的版本，DEFAULT 约束中指定的默认值必须是文字常量，而不能是一个函数或表达式。

但有一个例外，这个例外就是： TIMESTAMP 和 DATETIME 列。

这意味着，我们不能将 date 列的默认值设置为函数的值，例如 NOW() 或 CURRENT_DATE 。

而对于BLOB ， TEXT ，GEOMETRY 和 JSON 数据类型，根本就不允许分配默认值。

同样的，如果默认值的计算结果与声明的列类型不同，则根据通用的 MySQL 类型转换规则对声明的类型进行隐式强制。

处理隐式默认值

如果在定义表结构时不使用 DEFAULT 约束为列显式定义默认值，MySQL 将自己决定如何设置默认值。

  • 如果列可以将 NULL 作为值，则会使用显式的 DEFAULT NULL 子句定义该列。其实不用显式，因为它就是默认的定义。
  • 如果列不能将 NULL 作为值，则 MySQL 会定义没有显式 DEFAULT 子句的列。也就是并不会添加 DEFAULT 约束。

但这两条规则有一个例外，如果列被定义为 PRIMARY KEY 的一部分但未显式设置为 NOT NULL，则 MySQL 将其创建为 NOT NULL 列（ 因为PRIMARY KEY 列必须为 NOT NULL ）。

对于没有显式 DEFAULT 约束的 NOT NULL 列的数据输入，如果 INSERT 或 REPLACE 语句不包含该列的值，或者 UPDATE 语句将列设置为 NULL ，MySQL 会根据当时生效的 SQL 模式处理列：

  • 如果启用了严格的 SQL 模式，则事务表会发生错误，并且会回滚 SQL 语句。对于非事务性表，会发生错误，但如果多行语句的第二行或后续行发生这种情况，则前面的行会正常插入。
  • 如果未启用严格模式，MySQL 会将列设置为列数据类型的隐式默认值

对于这段叙述，总觉得很拗口，好不，我们举个例子来说明下，假设表 t 定义如下

CREATE TABLE t (i INT NOT NULL);

上面这个表定义语句中，我们并没有为 i 字段显式的定义默认值，因此在严格模式下，以下每个语句都会产生错误，并且不会插入任何行。

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

不使用严格模式时，只有第三个语句产生错误，因为前两个语句插入了隐式默认值，但第三个语句失败，因为 DEFAULT(i) 无法生成值。

对于给定的表，我们可以使用 SHOW CREATE TABLE 语句显示哪些列具有显式 DEFAULT 约束。

而对于隐式默认值，则定义如下

  • 对于数字类型，默认值为 0，但对于使用 AUTO_INCREMENT 属性声明的整数或浮点类型，默认值是序列中的下一个值。
  • 对于 TIMESTAMP 以外的日期和时间类型，默认值为该类型的相应 「 零 」 值。如果启用了 explicit_defaults_for_timestamp 系统变量，那么 TIMESTAMP 类型的列的默认值也是 「 零 」 值。否则，对于表中的第一个 TIMESTAMP 列，默认值为当前日期和时间。
  • 对于 ENUM 以外的字符串类型，默认值为空字符串 （ ""）。对于 ENUM 类型，默认值是第一个枚举值