MySQL-TIMESTAMP(3)的默认值 [英] MySQL - default value for TIMESTAMP(3)
问题描述
我的数据库是MySql 5.6.
My database is MySql 5.6.
我想使用CURRENT_TIMESTAMP作为类型为TIMESTAMP(3)的属性的默认值.
I want to use CURRENT_TIMESTAMP as the default value an attribute which is type of TIMESTAMP(3).
但是我得到了错误:
错误1067(42000):更新"的默认值无效
ERROR 1067 (42000): Invalid default value for 'updated'
我认为这是因为CURRENT_TIMESTAMP
仅精确到秒.
I think it is because CURRENT_TIMESTAMP
is only in precision of second.
如何将小数部分timestamp
的当前时间设置为默认值?
How can I set current time as the default value for a timestamp
with fractional part?
推荐答案
根据timestamp
和datetime
类型列上的文档:
As per documentation on timestamp
and datetime
type columns:
如果
TIMESTAMP
或DATETIME
列定义在任何地方都包含显式的小数秒精度值,则在整个列定义中都必须使用相同的值.
If a
TIMESTAMP
orDATETIME
column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition.
这是允许的:
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
其他示例:
mysql> create table tbl_so_q23671222_1( ts timestamp(3) default now() );
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> create table tbl_so_q23671222_1( ts timestamp(3) default now(3) );
Query OK, 0 rows affected (0.59 sec)
mysql> create table tbl_so_q23671222_2( ts timestamp(3) default current_timestamp );
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> create table tbl_so_q23671222_2( ts timestamp(3) default current_timestamp(3) );
Query OK, 0 rows affected (0.38 sec)
mysql> desc tbl_so_q23671222_1;
+-------+--------------+------+-----+----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+----------------------+-------+
| ts | timestamp(3) | NO | | CURRENT_TIMESTAMP(3) | |
+-------+--------------+------+-----+----------------------+-------+
1 row in set (0.01 sec)
mysql> desc tbl_so_q23671222_2;
+-------+--------------+------+-----+----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+----------------------+-------+
| ts | timestamp(3) | NO | | CURRENT_TIMESTAMP(3) | |
+-------+--------------+------+-----+----------------------+-------+
1 row in set (0.01 sec)
参考:
TIMESTAMP和DATETIME的初始化和更新 >
这篇关于MySQL-TIMESTAMP(3)的默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!