MySQL插入到DATETIME：使用ISO :: 8601格式是否安全？ [英] MySQL insert to DATETIME: is it safe to use ISO::8601 format?

问题描述

在我们的项目中，我们使用Zend Framework Model生成器，它生成这样的东西，将存储在DB（MySQL）中的属性设置为DATETIME字段：

  public function setObjectDatetime（$ data）{
 if（！$ data instanceof Zend_Date）{...某些转换代码...} 
 $ 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行受影响，1个警告，与

1264 |超出了第1行列dt的范围值

警告（以）。

对于我的amuzement，phpMyAdmin根本不显示任何警告;并且所有的服务器端代码都将这个查询作为一个实体处理。 ）

所以问题是：我们应该将我们在模型中存储的内容重新格式化为另一个字符串格式（'YY-MM-dd HH：mm：ss'，for例如？）还是只是MySQL的一些奇怪的行为，迟早会被修复？

解决方案

看起来像短这个问题的答案是不，不安全 - 这个结论跟随着一系列MySQL shell的实验。仍然会欣赏更多的理论答案，但是...

显然，MySQL引擎（默认情况下）在它接受的数据文本中甚至使用code> sql_mode 设置为STRICT_ALL_TABLES：不仅可以接受各种分隔符，它们也可能不同：

INSERT INTO t（dt）VALUES（'2012-01,03.04：05 @ 06'）; - 查询确定，1行受影响

此外，如果字符串太短，它将被填充为零...但是可能是惊喜：

INSERT INTO t（dt）VALUES（'2012011'）; - 2020-12-01 01:00:00是什么插入

可悲的是，字符串太长（最后一个以下是除空格之外的其他数字）将被视为严格模式下的无效值：

  mysql> INSERT INTO t（dt）VALUES（'2012-06-27T05：25Z'）; 
错误1292（22007）：日期时间值不正确：第1行dt列的2012-06-27T05：25Z
 mysql> INSERT INTO t（dt）VALUES（'2012-06-27T05：25'）; 
查询OK，1行受影响（0.10秒）
  

在传统模式下，解析甚至更放松 - 但不是更精确;此外，在严格模式下认为不正确的字符串会给出一些无声警告，但操作将会成功：

  mysql> INSERT INTO t（dt）VALUES（'2012-06-27T05：25Z'）; 
查询OK，1行受影响，1个警告（0.10秒）
 
 mysql>显示警告; 
 + --------- + ------ + ---------------------------- ----------------- + 
 |警告| 1264 |第1行列'dt'的范围值超出范围
 + --------- + ------ + ---------------------------- ----------------- + 
 
 mysql> SELECT dt FROM t; 
 + --------------------- + 
 | dt | 
 + --------------------- + 
 | 2012-06-27 05:25:00 | 
 + --------------------- + 
  

底线是我们必须重写一些与DAL相关的代码，以便日期（和数据时间）始终以标准化的形式发送到数据库。我不知道为什么我们是这样做的，而不是Zend_Db开发人员。但是，我想是另一个故事。 ）

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:

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.

The problem arises when we try to save this 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...

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

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

warning (shown by 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. )

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?

解决方案

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

Apparently MySQL engine is (by default) pretty liberal in what it accepts as a Datetime literal even with sql_mode set to STRICT_ALL_TABLES : not only various separators are accepted, they may differ as well:

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

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. )

这篇关于MySQL插入到DATETIME：使用ISO :: 8601格式是否安全？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！