DEFAULT 子句中指定的默认值可以是字面常量或表达式。除了一个例外，应将表达式默认值用括号括起来，以将其与字面常量默认值区分开来。示例：The default value specified in a DEFAULT clause can be a literal constant or an expression. With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values. Examples:

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 函数作为默认值，而无需使用括号。参见 第 13.2.5 节，“TIMESTAMP 和 DATETIME 的自动初始化和更新”。The exception is that, for TIMESTAMP and DATETIME columns, you can specify the CURRENT_TIMESTAMP function as the default, without enclosing parentheses. See Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

BLOB、TEXT、GEOMETRY 和 JSON 数据类型只有在将值写为表达式时才能分配默认值，即使该表达式值是一个字面值：The BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal:

表达式默认值必须遵守以下规则。如果表达式包含不允许的结构，将发生错误。Expression default values must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

对于 CREATE TABLE ... LIKE 和 CREATE TABLE ... SELECT，目标表会保留原始表的表达式默认值。For CREATE TABLE ... LIKE and CREATE TABLE ... SELECT, the destination table preserves expression default values from the original table.

如果表达式的默认值引用了非确定性函数，则任何导致该表达式被求值的语句对于基于语句的复制都是不安全的。这包括诸如 INSERT 和 UPDATE 之类的语句。在这种情况下，如果禁用二进制日志，语句将正常执行。如果启用二进制日志并且 binlog_format 设置为 STATEMENT，该语句会被记录并执行，但会向错误日志写入警告信息，因为副本可能会出现分歧。当 binlog_format 设置为 MIXED 或 ROW 时，语句将正常执行。If an expression default value refers to a nondeterministic function, any statement that causes the expression to be evaluated is unsafe for statement-based replication. This includes statements such as INSERT and UPDATE. In this situation, if binary logging is disabled, the statement is executed as normal. If binary logging is enabled and binlog_format is set to STATEMENT, the statement is logged and executed but a warning message is written to the error log, because replicas might diverge. When binlog_format is set to MIXED or ROW, the statement is executed as normal.

在插入新行时，对于具有表达式默认值的列，可以通过省略列名或将列指定为 DEFAULT（就像具有字面默认值的列一样）来插入默认值：When inserting a new row, the default value for a column with an expression default can be inserted either by omitting the column name or by specifying the column as DEFAULT (just as for columns with literal defaults):

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) 来指定命名列的默认值仅允许用于具有字面默认值的列，而不适用于具有表达式默认值的列。However, the use of DEFAULT(col_name) to specify the default value for a named column is permitted only for columns that have a literal default value, not for columns that have an expression default value.

并非所有存储引擎都允许表达式默认值。对于那些不允许的引擎，会发生 ER_UNSUPPORTED_ACTION_ON_DEFAULT_VAL_GENERATED 错误。Not all storage engines permit expression default values. For those that do not, an ER_UNSUPPORTED_ACTION_ON_DEFAULT_VAL_GENERATED error occurs.

如果默认值计算出的数据类型与声明的列类型不同，将根据通常的 MySQL 类型转换规则进行隐式强制转换。参见 第 14.3 节，“表达式求值中的类型转换”。If a default value evaluates to a data type that differs from the declared column type, implicit coercion to the declared type occurs according to the usual MySQL type-conversion rules. See Section 14.3, “Type Conversion in Expression Evaluation”.

如果数据类型规范未包含明确的 DEFAULT 值，MySQL 会按如下方式确定默认值：If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows:

如果列可以取 NULL 值，则该列定义为显式 DEFAULT NULL。If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

如果列不能取 NULL 值，MySQL 会将该列定义为没有显式 DEFAULT 子句。If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.

对于没有显式 DEFAULT 子句的 NOT NULL 列的数据输入，如果 INSERT 或 REPLACE 语句未为该列提供值，或者 UPDATE 语句将该列设置为 NULL，MySQL 会根据当时生效的 SQL 模式处理该列：For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

假设有一个表 t 定义如下：Suppose that a table t is defined as follows:

CREATE TABLE t (i INT NOT NULL);

在这种情况下，i 没有显式的默认值，因此在严格模式下，以下每条语句都会产生错误，并且不会插入任何行。在不使用严格模式时，只有第三条语句会产生错误；前两条语句会插入隐式默认值，但第三条失败，因为 DEFAULT(i) 无法生成值：In this case, i has no explicit default, so in strict mode each of the following statements produce an error and no row is inserted. When not using strict mode, only the third statement produces an error; the implicit default is inserted for the first two statements, but the third fails because DEFAULT(i) cannot produce a value:

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

参见 第 7.1.11 节，“服务器 SQL 模式”。See Section 7.1.11, “Server SQL Modes”.

对于给定的表，SHOW CREATE TABLE 语句会显示哪些列具有显式的 DEFAULT 子句。For a given table, the SHOW CREATE TABLE statement displays which columns have an explicit DEFAULT clause.

隐式默认值定义如下：Implicit defaults are defined as follows: