MySQL插入DATETIME：使用ISO :: 8601格式是否安全？

小编典典

MySQL插入DATETIME：使用ISO :: 8601格式是否安全？

mysql

在我们的项目中，我们使用Zend Framework
Model生成器，该生成器会生成类似这样的内容，以将存储在DB（MySQL）中的属性设置为DATETIME字段：

public function setObjectDatetime($data) {
  if (! $data instanceof Zend_Date) { ... some conversion code ... }
  $this->objectDatetime = $data->toString(Zend_Date::ISO_8601);
}

因此，ISO :: 8601格式的字符串（例如‘2012-06-15T18：33：00 + 03：00’）实际上是作为属性存储的。

当我们尝试使用save此模型并将该字符串传递给MySQL（版本5.5.16）时，就会出现问题：它会发出警告，但仍会插入/更新具有正确结果的相应行。很容易检查问题是由MySQL引起的，而不是某些驱动程序的行为引起的：只需发出如下查询即可…

UPDATE table_name SET datetime_field = '2012-06-15T18:33:00+03:00' WHERE id = 1;

......其结果将是1 row affected, 1 warning，与

1264 | Out of range value for column 'dt' at row 1

警告（由显示SHOW WARNINGS）。

令我大失所望的是，phpMyAdmin根本不显示任何警告。并且所有服务器端代码都将此查询作为可靠的查询进行处理。）

因此，问题是：我们是否应该将我们存储在模型中的内容重新格式化为另一种字符串格式（例如，’YY-MM-dd
HH：mm：ss’？）还是只是将解决一些奇怪的MySQL行为迟早？

阅读 371

2020-05-17

共1个答案

小编典典

这个问题的简短答案似乎是“不，这并不安全”-这个结论是通过对MySQL Shell进行的一系列实验得出的。不过，仍然会希望有一个更“理论上”的答案。

显然，MySQL引擎（默认情况下）在它作为Datetime文字接受的方面是相当自由的，即使将其sql_mode设置为STRICT_ALL_TABLES：不仅接受各种分隔符，它们也可能有所不同：

INSERT INTO t(dt) VALUES('2012-01,03.04:05@06'); -- Query OK, 1 row affected

此外，如果字符串太短，则会用零填充…，但可能会令人惊讶：

INSERT INTO t(dt) VALUES('2012011'); -- 2020-12-01 01:00:00 is what's inserted

可悲的是，字符串太长（当最后一个可分析的数字后跟空格以外的其他内容时）在严格模式下将被视为无效值：

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)

在传统模式下，解析更加轻松-但不够精确；此外，在严格模式下被认为不正确的字符串会发出“无声警告”，尽管操作会成功：

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 |
+---------------------+

最重要的是，我们必须重写一些与DAL相关的代码，以便日期（和日期时间）始终以“规范化”形式发送到数据库。我想知道为什么要做的是我们，而不是Zend_Db开发人员。我想那是另一个故事。）

2020-05-17