MySql 不允许在 5.7 版的日期时间字段中插入完整日期。*

Question

只想通过配置更改它。不改变我的查询

当前 SQL_MODE 是

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

我有一张表dummy_test，有一个字段名created_at

创建时间 => 日期时间(NULL)

当我执行以下查询时

insert into dummy_test values('2018-08-14 05:38:11 pm')

IT 给我一个错误

查询错误 (1292):日期时间值不正确:第 1 行“created_at”列的“2018-08-14 05:38:11 pm”

它工作正常

insert into dummy_test values('2018-08-14 05:38:11')

Answer 1

根据 MySQL documentation :

MySQL recognizes TIME values in these formats:

• As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 34.

• As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as '10:11:12', but '109712' is illegal (it has a nonsensical minute part) and becomes '00:00:00'.

• As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following alternative formats are also understood: SS, MMSS, or HHMMSS.

所以在发送到 MySQL 时你必须使用 24 小时制。如果您通过 PHP 传递日期/时间，this question可能会有帮助。