MySQL 插入到 DATETIME：使用 ISO::8601 格式是否安全？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11053941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL insert to DATETIME: is it safe to use ISO::8601 format?
提问by raina77ow
In our project we use Zend Framework Model generator, which produces something like this to set the properties that are stored in DB (MySQL) as DATETIME fields:
在我们的项目中，我们使用 Zend 框架模型生成器，它生成类似这样的东西来将存储在 DB (MySQL) 中的属性设置为 DATETIME 字段：
public function setObjectDatetime($data) {
if (! $data instanceof Zend_Date) { ... some conversion code ... }
$this->objectDatetime = $data->toString(Zend_Date::ISO_8601);
}
So the ISO::8601 formatted string ('2012-06-15T18:33:00+03:00' for example) is what actually is stored as a property.
所以 ISO::8601 格式的字符串（例如'2012-06-15T18:33:00+03:00'）实际上是作为属性存储的。
The problem arises when we try to savethis model, and pass this string to MySQL (version 5.5.16): it raise the warning, but still inserts/updates the corresponding row with a correct result. It's easy to check that the issue is caused by MySQL, and not some drivers' behaviour: just issue such query as...
当我们尝试使用save此模型并将此字符串传递给 MySQL（版本 5.5.16）时，问题就出现了：它引发了警告，但仍然使用正确的结果插入/更新相应的行。很容易检查问题是否是由 MySQL 引起的，而不是某些驱动程序的行为：只需发出这样的查询...
UPDATE table_name SET datetime_field = '2012-06-15T18:33:00+03:00' WHERE id = 1;
... and the result will be 1 row affected, 1 warning, with
......其结果将是1 row affected, 1 warning，与
1264 | Out of range value for column 'dt' at row 1
1264 | Out of range value for column 'dt' at row 1
warning (shown by SHOW WARNINGS).
警告（由 显示SHOW WARNINGS）。
To my amuzement, phpMyAdmin doesn't show any warnings at all; and all the server-side code processed this query as a solid one. )
令我惊讶的是，phpMyAdmin 根本没有显示任何警告；并且所有服务器端代码都将此查询作为一个可靠的查询进行处理。)
So the question is: should we really reformat what we store in our Model into another string format ('YY-MM-dd HH:mm:ss', for example?) Or is it just some weird behavior of MySQL that will be fixed sooner or later?
所以问题是：我们真的应该将我们存储在模型中的内容重新格式化为另一种字符串格式（例如，'YY-MM-dd HH:mm:ss'？）还是只是 MySQL 的一些奇怪行为将被修复迟早？
采纳答案by raina77ow
It looks like the short answer to this question is "No, it's not safe" - this conclusion follows a series of experiments with MySQL shell. Still would appreciate a more "theoretical" answer, though...
看起来这个问题的简短回答是“不，它不安全”——这个结论是在一系列 MySQL shell 实验之后得出的。不过，仍然会欣赏更“理论”的答案......
Apparently MySQL engine is (by default) pretty liberal in what it accepts as a Datetime literal even with sql_modeset to STRICT_ALL_TABLES : not only various separators are accepted, they may differ as well:
显然，即使sql_mode设置为 STRICT_ALL_TABLES ，MySQL 引擎（默认情况下）在接受日期时间文字方面也非常自由：不仅接受各种分隔符，它们也可能不同：
INSERT INTO t(dt) VALUES('2012-01,03.04:05@06'); -- Query OK, 1 row affected
INSERT INTO t(dt) VALUES('2012-01,03.04:05@06'); -- Query OK, 1 row affected
Besides, if the string is too short, it will be padded with zeroes... but there might be surprises:
此外，如果字符串太短，它将用零填充......但可能会有惊喜：
INSERT INTO t(dt) VALUES('2012011'); -- 2020-12-01 01:00:00 is what's inserted
INSERT INTO t(dt) VALUES('2012011'); -- 2020-12-01 01:00:00 is what's inserted
The sad thing is that the string too long (when the last parsable digit is followed by something other than whitespace) will be considered an invalid value in strict mode:
可悲的是，字符串太长（当最后一个可解析数字后跟空格以外的内容时）在严格模式下将被视为无效值：
mysql> INSERT INTO t(dt) VALUES('2012-06-27T05:25Z');
ERROR 1292 (22007): Incorrect datetime value: '2012-06-27T05:25Z' for column 'dt' at row 1
mysql> INSERT INTO t(dt) VALUES('2012-06-27T05:25');
Query OK, 1 row affected (0.10 sec)
In the traditional mode parsing is even more relaxed - but not more precise; besides, the strings that are considered incorrect in the strict mode will give sort of 'silent warnings', though operations will succeed:
在传统模式下，解析更加轻松——但不是更精确；此外，在严格模式下被认为不正确的字符串会发出某种“无声警告”，尽管操作会成功：
mysql> INSERT INTO t(dt) VALUES('2012-06-27T05:25Z');
Query OK, 1 row affected, 1 warning (0.10 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'dt' at row 1 |
+---------+------+---------------------------------------------+
mysql> SELECT dt FROM t;
+---------------------+
| dt |
+---------------------+
| 2012-06-27 05:25:00 |
+---------------------+
The bottom line is that we had to rewrite some DAL-related code so that dates (and datetimes) are always sent to the DB in "normalized" form. I wonder why it's we who have to do it, and not Zend_Db developers. But that's another story, I suppose. )
最重要的是，我们必须重写一些与 DAL 相关的代码，以便日期（和日期时间）始终以“规范化”形式发送到数据库。我想知道为什么是我们必须这样做，而不是 Zend_Db 开发人员。但我想那是另一个故事了。)
回答by Brian Driscoll
As far as I know there is no way to store time offset information (the +03:00 at the end of your ISO 8601 string) in MySQL Date or Time types, so you're sort of on your own to find a solution.
据我所知，无法在 MySQL 日期或时间类型中存储时间偏移信息（ISO 8601 字符串末尾的 +03:00），因此您需要自己找到解决方案。
One possible approach is to split the ISO 8601 string and store the offset in a char(5) column, though admittedly it would make it sort of difficult to work with. I suppose you could store the offset in a Time column, which might make date/time manipulations a little bit easier.
一种可能的方法是拆分 ISO 8601 字符串并将偏移量存储在 char(5) 列中，尽管无可否认，这会使其难以使用。我想您可以将偏移量存储在时间列中，这可能会使日期/时间操作更容易一些。
EDIT
I just stumbled upon thisin the MySQL docs, which may be helpful.
编辑
我只是在 MySQL 文档中偶然发现了这一点，这可能会有所帮助。
回答by akazakou
By default MySQL use ISO9075 format for datetime
MySQL 默认使用 ISO9075 格式作为日期时间
The possible values for the first and second arguments result in several possible format strings (for the specifiers used, see the table in the DATE_FORMAT() function description). ISO format refers to ISO 9075, not ISO 8601.
第一个和第二个参数的可能值导致几种可能的格式字符串（对于使用的说明符，请参阅 DATE_FORMAT() 函数描述中的表）。ISO 格式是指 ISO 9075，而不是 ISO 8601。
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_get-format
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_get-format
