MySQL:插入错误 "Cannot be NULL"-- DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

Question

其实类似的问题有很多，我都解决不了这个问题。我正在使用 codeigniter 框架。当我通过传递一个 php 对象调用 ActiveRecord 的 insert 方法时，它会通过其值或 null 发送所有属性。它导致 cannot be null. 错误。

表结构:

CREATE TABLE `scheduledTasks` (
  `id` int(11) NOT NULL,
  `type` varchar(255) COLLATE utf8_bin NOT NULL,
  `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `executionTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ownerUserId` int(11) DEFAULT NULL,
  `subjectUserId` text COLLATE utf8_bin,
  `detail` text COLLATE utf8_bin,
  `isExecuted` int(1) DEFAULT '0'
);
ALTER TABLE `scheduledTasks`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `scheduledTasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

我试过的查询:

INSERT INTO `scheduledTasks` (`id`, `type`, `createTime`, `executionTime`, `ownerUserId`, `detail`, `isExecuted`)
VALUES (NULL, 'QuoteRequest', NULL, NULL, '926', NULL, NULL)

Mysql版本:

+-------------------------+
| VERSION()               |
+-------------------------+
| 5.7.17-0ubuntu0.16.04.1 |
+-------------------------+

我有

explicit_defaults_for_timestamp | OFF

而sql模式是

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

Answer 1

讨论

MySQL 5.7 manual指出...

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

手册并未说明您可以对 DATETIME 字段执行此操作。最好的做法是在 INSERT 查询中为 createTime 和 executionTime 字段提供任何值。那应该给你 DEFAULT_TIMESTAMP。如果失败了，好吧，我试过了。

INSERT INTO `scheduledTasks` (`id`, `type`, `createTime`, `executionTime`, `ownerUserId`, `detail`, `isExecuted`)
VALUES (NULL, 'QuoteRequest', , , '926', NULL, NULL)

此外，请记住 MySQL default values ，即使您的 DATETIME 列确实有一个显式的 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:

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 will have been inserted.

If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

最后，关于strict mode在 MySQL 手册中:

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 12.7, “Data Type Default Values”.

结论

总之，如果您处于严格模式(您是STRICT_TRANS_TABLES)并且DATETIME 列设置为NOT NULL DEFAULT CURRENT_TIMESTAMP，然后您在 INSERT 期间提供 NULL 值，随后您会得到“cannot be NULL”错误，...下次不要向 DATETIME 字段提供值。

如果您的框架无法设置为在 INSERT 期间省略值，并且更改 SQL 模式不起作用，则更改表以使用 (gasp) TIMESTAMP 可能是在严格模式下使用 NULL 并显示 DEFAULT TIMESTAMP 的唯一选择。