mysql 设置not null,MySQL添加一个NOT NULL列

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.

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

解决方案

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

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

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 data1. Similar rules apply when the DEFAULT value is specified.

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

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

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 ..

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

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