mysql alter table add column before,MySQL ALTER TABLE添加列：另一列错误

ok, i have people table and try run sql-script with the next command:

ALTER TABLE `people`

ADD COLUMN `name_spelling`

VARCHAR(255) NULL DEFAULT NULL

AFTER `last_name`;

Then I'll get a error:

[22001][1292] Data truncation: Incorrect date value:

'0000-00-00' for column 'birth_date' at row 35

At this row birth_date is NULL, but if I try set some date like 2016-05-05 at this column and row and than set NULL back -- all works is correct at this row, but get error on some next null date.

In addition, not all null date values return error.

解决方案

Maybe your MySQL has some option such as the NO_ZERO_DATE SQL mode (I think it's not the only mysql config that prevents 0000-00-00 dates). This would prevent you from using 0000-00-00 as a value. It happened to me before that I had a table already created violating that condition. It will not be editable until you change the value or disable what's preventing you to set 0 dates.