MySQL 字段 DATETIME 截断 ISO8601 [英] MySQL field DATETIME truncates ISO8601

问题描述

像这样创建 MySQL/MariaDB 表

Having MySQL/MariaDB table created like

CREATE TABLE `testTable` (
  `id` int(11) NOT NULL,
  `timeinfo` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

并像这样插入完整的 ISO8601 值:

And inserted with full ISO8601 value like this:

MariaDB [test]> INSERT INTO testTable VALUES (1, '2500-12-31T00:00:00.000Z');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'timeinfo' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from testTable;
+----+---------------------+
| id | timeinfo            |
+----+---------------------+
|  1 | 2500-12-31 00:00:00 |
+----+---------------------+
1 row in set (0.01 sec)

我没有完全看到被截断的内容，ZULU TimeZone 中的Z"?

I don't exactly see what is being truncated, the "Z" as in ZULU TimeZone ?

是否可以将信息完全包含在 MySQL/MariaDB 字段类型中?

Is it possible to contain the information fully in MySQL/MariaDB field type?

如果是，应该使用哪种字段数据类型?

If so which field data type should be used?

推荐答案

数据库需要另一个输入，这就是您收到警告的原因

The database expects another input that is why you get the warning

在 MySQL 中 DATETIME 需要以下格式

DATETIME - 格式:YYYY-MM-DD HH:MI:SS

DATETIME - format: YYYY-MM-DD HH:MI:SS

这不会产生任何警告:

INSERT INTO testTable VALUES (, '2500-12-31 00:00:00');

即使有您的警告，数据库也会填写您的记录.

Even with your warnings the database will fill you record.

这篇关于MySQL 字段 DATETIME 截断 ISO8601的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！