创建新列时获取默认值错误

Question

如果我注释掉第二个alter table，它可以工作，因为数据是插入的。

CREATE TABLE users (
  id int unsigned NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL
);



ALTER TABLE users ADD start datetime DEFAULT (DATE_SUB(now(), INTERVAL -1 DAY)) NOT NULL;

insert into users (id, name) values (1, 'usera'), (2, 'usera');

ALTER TABLE users ADD anotherDate datetime DEFAULT (now()) NOT NULL; -- This errors

select * from users;

完整错误为:错误1067 42000“start”Fiddle显示错误的默认值无效:https://dbfiddle.uk/？rdbms=mysql_8.0&Fiddle=2df3b421793a7470a321247abec85b47

Answer 1

我从sql_mode='strict_trans_tables,no_engine_substitution':

mysql> ALTER TABLE users ADD start datetime DEFAULT (DATE_SUB(now(), INTERVAL -1 DAY)) NOT NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> insert into users (id, name) values (1, 'usera'), (2, 'usera');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER TABLE users ADD anotherDate datetime DEFAULT (now()) NOT NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER TABLE users ADD anotherDate2 datetime DEFAULT (now()) NOT NULL;
ERROR 1067 (42000): Invalid default value for 'start'
mysql>

根据https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_in_date,和no_zero_in_date都不推荐使用

这是在MySQL 8.0.22上

mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message
                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)