MySQL添加一个NOT NULL列 [英] MySQL add a NOT NULL column

问题描述

我正在向表中添加一列:

I am adding a column to a table:

ALTER TABLE t ADD c varchar(10) NOT NULL;

该列被添加，并且每个记录都有一个空字符串.

The column gets added, and each record has the empty string.

在MySQL 5.5+的所有条件下(严格模式等)，这是否有望以这种方式工作?

Is this expected to work this way under all conditions (strict mode, etc.) in MySQL 5.5+?

推荐答案

在MySQL中，每个列类型都有一个隐式默认值.

In MySQL, each column type has an "implicit default" value.

对于字符串类型，[隐式]默认值为空字符串.

For string types [the implicit] default value is the empty string.

如果将NOT NOT NULL列添加到表中，并且未指定任何显式的DEFAULT，则隐式默认值用于填充新的列数据 ¹ .指定DEFAULT值时，将应用类似的规则.

If a NOT NULL column is added to a table, and no explicit DEFAULT is specified, the implicit default value is used to populate the new column data¹. Similar rules apply when the DEFAULT value is specified.

因此，原始DDL产生的结果与以下内容相同:

As such, the original DDL produces the same results as:

-- After this, data will be the same, but schema has an EXPLICIT DEFAULT
ALTER TABLE t ADD c varchar(10) NOT NULL DEFAULT ''
-- Now we're back to the IMPLICIT DEFAULT (MySQL stores NULL internally)
ALTER TABLE t ALTER c DROP DEFAULT

严格"模式设置会影响依赖于默认值的DML语句，但不会影响添加列时的隐式默认用法.

The "strict" mode settings affects DML statements relying on default values, but do not affect the implicit default usage when the column is added.

对于将数据输入到没有显式DEFAULT子句的NOT NULL列中，如果 INSERT或REPLACE语句不包含该列的值[并且]如果启用了严格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 [and if] strict SQL mode is enabled, an error occurs ..

这是一个sqlfiddle证明" ，其中严格模式不适用于ALTER TABLE .. ADD语句.

Here is an sqlfiddle "proof" that strict mode does not apply to the ALTER TABLE .. ADD statement.

¹ 这是一个MySQL功能.其他引擎(例如SQL Server)需要此类架构更改的显式DEFAULT(或NULL列)约束.

¹ This is a MySQL feature. Other engines, like SQL Server, require an explicit DEFAULT (or NULL column) constraint for such schema changes.

这篇关于MySQL添加一个NOT NULL列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！