会写入全零的数据,
(1)最后一位微秒写入1,其余全零
mysql> select * from user;
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 0000-00-01 00:00:00.000000 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
| 2015-03-15 12:12:15.000000 | 222 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
| 2015-03-15 12:12:15.000000 | 444 | 2015-03-15 12:12:15.22001 | 2015-03-15 12:12:15.4711 | 2015-03-15 12:12:15.481 |
| 2015-03-15 12:12:15.000000 | ccc | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
4 rows in set (0.00 sec)
mysql> update user set gmt_create = "00-00-00 00:00:00.000001" where name = "111";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 2000-00-00 00:00:00.000001 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
| 2015-03-15 12:12:15.000000 | 222 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
| 2015-03-15 12:12:15.000000 | 444 | 2015-03-15 12:12:15.22001 | 2015-03-15 12:12:15.4711 | 2015-03-15 12:12:15.481 |
| 2015-03-15 12:12:15.000000 | ccc | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
4 rows in set (0.00 sec)(2)4位年数字写成3个0,结果是4位全0的年
mysql> update user set gmt_create = "000-00-00 00:00:00.000001" where name = "111";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user where name="111";
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 0000-00-00 00:00:00.000001 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
1 row in set (0.00 sec)
(3)4位年数写成两个0,居然年份写成了2000,惊讶!!
mysql> update user set gmt_create = "00-00-00 00:00:00.000001" where name = "111";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user where name="111";
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 2000-00-00 00:00:00.000001 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+(4)4位年数写成一个0,再试试,结果是年字段是4个0
mysql> update user set gmt_create = "0-00-00 00:00:00.000001" where name = "111";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user where name="111";
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 0000-00-00 00:00:00.000001 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
(5)月和日的写成一个0,试试年月会自动补全成00:00格式
mysql> update user set gmt_create = "0000-0-0 00:00:00.000001" where name = "111";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from user where name="111";
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 0000-00-00 00:00:00.000001 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
(6)时分秒分别改成一个0写入,结果时分秒也会补全成两个0的格式
mysql> update user set gmt_create = "0-00-00 0:0:0.000001" where name = "111";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from user where name="111";
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 0000-00-00 00:00:00.000001 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
(7)毫秒字段写5个0,结果会补全成数据类型声明的datetime(6)的
mysql> update user set gmt_create = "0000-0-0 00:00:00.00000" where name = "111";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user where name="111";
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 0000-00-00 00:00:00.000000 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
(8)精度会精确到毫秒
mysql> update user set gmt_create = "0-00-00 0:0:0.00001" where name = "111";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user where name="111";
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| gmt_create | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+
| 0000-00-00 00:00:00.000010 | 111 | 2015-03-15 12:12:15.00000 | 2015-03-15 12:12:15.0000 | 2015-03-15 12:12:15.000 |
+----------------------------+------+---------------------------+--------------------------+-------------------------+