mysql 精确毫秒_mysql 精确到毫秒

现在很多团队在做去o的项目，迁移mysql时，为了便于binlog分析，请务必对每张表都增加ts字段。

条件：mysql5.6

My.cnf中修改参数

explicit_defaults_for_timestamp = 0

且该字段需要精确到毫秒，而now()函数是不支持毫秒的。

且建表时，只设置ts字段为timstamp类型，其他时间字段请用datetime类型，mysql只允许第一个timestamp类型的字段可以精确到毫秒。

由此，需要建表的时候这样定义字段：

create table presort_test(id int,ts timestamp(6) not null);

然后在insert时不需要给ts字段赋值：

insert into presort_test(id) values(3);

另外关于datetime & timestamp的区别如下：

datetime

1. 占用8个字节

2. 允许为空值，可以自定义值，系统不会自动修改其值。

3. 实际格式储存(Just stores what you have stored and retrieves the same thing which you have stored.)

4. 与时区无关(It has nothing to deal with the TIMEZONE and Conversion.)

5. 不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据。

6. 可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。

timestamp

1. 占用4个字节

2. 允许为空值，但是不可以自定义值，所以为空值时没有任何意义。

3. TIMESTAMP值不能早于1970或晚于2037。这说明一个日期，例如'1968-01-01'，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP值却无效，如果分配给这样一个对象将被转换为0。

4.值以UTC格式保存( it stores the number of milliseconds)

5.时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。

6. 默认值为CURRENT_TIMESTAMP()，其实也就是当前的系统时间。

请大家建表时注意，以下是我做测试的代码，给大家参考：

[root@PT-17089 bin]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2125755

Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test

Database changed

mysql> create table presort_test(id int,ts timestamp(6) not null);

Query OK, 0 rows affected (0.02 sec)

mysql> insert into presort_test values(1,now());

Query OK, 1 row affected (0.00 sec)

mysql> select * from presort_test \G;

*************************** 1. row ***************************

id: 1

ts: 2016-07-14 18:06:35.000000

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> insert into presort_test values(2,now());

Query OK, 1 row affected (0.00 sec)

mysql> select * from presort_test \G;

*************************** 1. row ***************************

id: 1

ts: 2016-07-14 18:06:35.000000

*************************** 2. row ***************************

id: 2

ts: 2016-07-14 18:16:17.000000

2 rows in set (0.00 sec)

mysql> insert into presort_test(id) values(3);

Query OK, 1 row affected (0.00 sec)

mysql> select * from presort_test

-> ;

+------+----------------------------+

| id | ts |

+------+----------------------------+

| 1 | 2016-07-14 18:06:35.000000 |

| 2 | 2016-07-14 18:16:17.000000 |

| 3 | 2016-07-14 18:23:19.562072 |

+------+----------------------------+

3 rows in set (0.00 sec)

mysql> insert into presort_test(id) values(4);

Query OK, 1 row affected (0.00 sec)

mysql> select * from presort_test;

+------+----------------------------+

| id | ts |

+------+----------------------------+

| 1 | 2016-07-14 18:06:35.000000 |

| 2 | 2016-07-14 18:16:17.000000 |

| 3 | 2016-07-14 18:23:19.562072 |

| 4 | 2016-07-14 18:25:08.529071 |

+------+----------------------------+

4 rows in set (0.00 sec)

--------------------------------------------------------------------------------------

只设置ts字段为timstamp类型，其他时间字段请用datetime类型，mysql只允许第一个timestamp类型的字段可以精确到毫秒。

mysql> alter table presort_test add column ts1 timestamp(6) not null;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into presort_test(id) values(5);

Query OK, 1 row affected (0.00 sec)

mysql> select * from presort_test;

+------+----------------------------+----------------------------+

| id | ts | ts1 |

+------+----------------------------+----------------------------+

| 1 | 2016-07-14 18:06:35.000000 | 0000-00-00 00:00:00.000000 |

| 2 | 2016-07-14 18:16:17.000000 | 0000-00-00 00:00:00.000000 |

| 3 | 2016-07-14 18:23:19.562072 | 0000-00-00 00:00:00.000000 |

| 4 | 2016-07-14 18:25:08.529071 | 0000-00-00 00:00:00.000000 |

| 5 | 2016-07-14 18:35:29.702059 | 0000-00-00 00:00:00.000000 |

+------+----------------------------+----------------------------+

5 rows in set (0.00 sec)