MySQL 8.0-11.6 Data Type Default Values

KING BOMB

于 2021-11-03 10:50:38 发布

阅读量305

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 database

原文链接：https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

版权

Mysql 专栏收录该内容

142 篇文章 2 订阅

订阅专栏

Data type specifications can have explicit or implicit default values.

数据类型规范可以具有显式或隐式默认值。

A DEFAULT value clause in a data type specification explicitly indicates a default value for a column. Examples:

数据类型规范中的DEFAULT值子句显式地指示列的默认值。例子:

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

SERIAL DEFAULT VALUE is a special case. In the definition of an integer column, it is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

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

Some aspects of explicit DEFAULT clause handling are version dependent, as described following.

显式DEFAULT子句处理的某些方面依赖于版本，如下所述。

Explicit Default Handling as of MySQL 8.0.13

显式默认处理MySQL 8.0.13

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:

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())
);

The exception is that, for TIMESTAMP and DATETIME columns, you can specify the CURRENT_TIMESTAMP function as the default, without enclosing parentheses. See Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

例外情况是，对于TIMESTAMP和DATETIME列，可以将CURRENT_TIMESTAMP函数指定为默认值，而不需要使用圆括号。参见11.2.5节，“TIMESTAMP和DATETIME的自动初始化和更新”。

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:

只有当值被写成表达式时，BLOB、TEXT、GEOMETRY和JSON数据类型才能被分配默认值，即使表达式值是文字值:

This is permitted (literal default specified as expression):这是允许的(文字默认指定为表达式):
```
CREATE TABLE t2 (b BLOB DEFAULT ('abc'));
```
This produces an error (literal default not specified as expression):这将产生一个错误(没有指定为表达式的文字默认值):
```
CREATE TABLE t2 (b BLOB DEFAULT 'abc');
```

Expression default values must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

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

Literals, built-in functions (both deterministic and nondeterministic), and operators are permitted.
允许文字、内置函数(确定性和非确定性)和操作符。
Subqueries, parameters, variables, stored functions, and loadable functions are not permitted.
不允许使用子查询、参数、变量、存储函数和可加载函数。
An expression default value cannot depend on a column that has the AUTO_INCREMENT attribute.
表达式默认值不能依赖于具有AUTO_INCREMENT属性的列。
An expression default value for one column can refer to other table columns, with the exception that references to generated columns or columns with expression default values must be to columns that occur earlier in the table definition. That is, expression default values cannot contain forward references to generated columns or columns with expression default values.
一个列的表达式默认值可以引用表中的其他列，但对生成的列或具有表达式默认值的列的引用必须是表定义中较早出现的列。也就是说，表达式默认值不能包含对生成的列或具有表达式默认值的列的正向引用。
The ordering constraint also applies to the use of ALTER TABLE to reorder table columns. If the resulting table would have an expression default value that contains a forward reference to a generated column or column with an expression default value, the statement fails.
排序约束也适用于使用ALTER TABLE对表列重新排序。如果生成的表具有一个表达式默认值，该值包含对生成的列或具有表达式默认值的列的正向引用，则该语句将失败。

Note

If any component of an expression default value depends on the SQL mode, different results may occur for different uses of the table unless the SQL mode is the same during all uses.

如果表达式默认值的任何组件依赖于SQL模式，则表的不同使用可能会出现不同的结果，除非所有使用过程中的SQL模式都是相同的。

For CREATE TABLE ... LIKE and CREATE TABLE ... SELECT, the destination table preserves expression default values from the original table.

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

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 replication slaves might diverge. When binlog_format is set to MIXED or ROW, the statement is executed as normal.

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

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):

当插入一个新行时，带有表达式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 |
+--------------------------------------+

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.

但是，只允许使用DEFAULT(col_name)来指定指定列的默认值，而不允许使用表达式默认值的列。

Not all storage engines permit expression default values. For those that do not, an ER_UNSUPPORTED_ACTION_ON_DEFAULT_VAL_GENERATED error occurs.

不是所有的存储引擎都允许表达式的默认值。对于那些没有，一个ER_UNSUPPORTED_ACTION_ON_DEFAULT_VAL_GENERATED错误发生。

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 12.3, “Type Conversion in Expression Evaluation”.

如果默认值计算出的数据类型与声明的列类型不同，则根据通常的MySQL类型转换规则隐式强制转换声明的类型。参见第12.3节“表达式求值中的类型转换”。

Explicit Default Handling Prior to MySQL 8.0.13

With one exception, the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that, for TIMESTAMP and DATETIME columns, you can specify CURRENT_TIMESTAMP as the default. See Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

除了一个例外，default子句中指定的默认值必须是文字常量;它不能是函数或表达式。这意味着，例如，您不能将日期列的默认值设置为NOW()或CURRENT_DATE等函数的值。例外情况是，对于TIMESTAMP和DATETIME列，可以指定CURRENT_TIMESTAMP作为默认值。

The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.

如果默认值计算出的数据类型与声明的列类型不同，则根据通常的MySQL类型转换规则隐式强制转换声明的类型。

Implicit Default Handling

If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows:

如果一个数据类型规范不包含显式的DEFAULT值，MySQL将确定默认值如下:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

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

If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.

如果该列不能接受NULL作为值，MySQL将定义没有显式DEFAULT子句的列。

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:

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

If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows are inserted.
如果启用了严格SQL模式，则事务表将发生错误，语句将回滚。对于非事务性表，会发生错误，但如果多行语句的第二行或后续行发生错误，则会插入前面的行。
If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
如果没有启用strict模式，MySQL将列设置为列数据类型的隐式默认值。

Suppose that a table t is defined as follows:

假设表t定义如下:

CREATE TABLE t (i INT NOT NULL);

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:

在这种情况下，i没有显式的默认值，因此在严格模式下，下面的每个语句都会产生一个错误，并且没有插入行。当不使用严格模式时，只有第三条语句产生错误;前两条语句插入了隐式的default，但第三条语句失败，因为default (i)不能产生值:

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

See Section 5.1.11, “Server SQL Modes”.

For a given table, the SHOW CREATE TABLE statement displays which columns have an explicit DEFAULT clause.

对于给定的表，SHOW CREATE table语句显示哪些列具有显式的DEFAULT子句。

Implicit defaults are defined as follows:

隐式默认值定义如下:

For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.
对于数字类型，默认值是0，但对于用AUTO_INCREMENT属性声明的整数或浮点类型，默认值是序列中的下一个值。
For date and time types other than TIMESTAMP, the default is the appropriate “zero” value for the type. This is also true for TIMESTAMP if the explicit_defaults_for_timestamp system variable is enabled (see Section 5.1.8, “Server System Variables”). Otherwise, for the first TIMESTAMP column in a table, the default value is the current date and time. See Section 11.2, “Date and Time Data Types”.
对于TIMESTAMP以外的日期和时间类型，默认值是该类型的适当“零”值。如果启用了explicit_defaults_for_timestamp系统变量(参见5.1.8节“服务器系统变量”)，则TIMESTAMP也是如此。否则，对于表中的第一个TIMESTAMP列，默认值是当前日期和时间。
For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.
对于非ENUM的字符串类型，默认值是空字符串。对于ENUM，默认值是第一个枚举值。

KING BOMB

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 8.0-11.6 Data Type Default Values

Data type specifications can have explicit or implicit default values.数据类型规范可以具有显式或隐式默认值。ADEFAULTvalueclause in a data type specification explicitly indicates a default value for a column. Examples:数据类型规范中的DEFAULT值子句显式地指示列的默认值。例子:CREATE TABLE t...
复制链接

扫一扫