如何创建具有列时间戳默认 current_date 的 mysql 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34418077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create mysql table with column timestamp default current_date?
提问by maia bardavelidze
I need to create mysql table with default value on column CURRENT_DATE()
我需要在列 CURRENT_DATE() 上创建具有默认值的 mysql 表
I try
我试试
DROP TABLE IF EXISTS `visitors`;
CREATE TABLE `visitors` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`ip` VARCHAR(32) NOT NULL,
`browser` VARCHAR(500) NOT NULL,
`version` VARCHAR(500) NOT NULL,
`platform` ENUM('w','l','m') NOT NULL,
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_DATE(),
PRIMARY KEY (`id`),
UNIQUE KEY `person` (`ip`,`date`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
but it is writting an error
但它写了一个错误
Query: CREATE TABLE `visitors` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `ip` VARCHAR(32) NOT NULL, `browser` VARCHAR(500) NO...
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_DATE() NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `person` (`ip`,`date`)
) ENGINE=INNODB AUTO_INCREMENT=1' at line 7
Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0.063 sec
what is the problem?
问题是什么？
I need to uniques only date not with full time info...
我只需要唯一日期而不是全职信息...
can you help me?
你能帮助我吗？
回答by Saharsh Shah
Use CURRENT_TIMESTAMPfunction instead of CURRENT_DATE()function
使用CURRENT_TIMESTAMP函数代替CURRENT_DATE()函数
Try this:
尝试这个：
DROP TABLE IF EXISTS `visitors`;
CREATE TABLE `visitors` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`ip` VARCHAR(32) NOT NULL,
`browser` VARCHAR(500) NOT NULL,
`version` VARCHAR(500) NOT NULL,
`platform` ENUM('w','l','m') NOT NULL,
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `person` (`ip`,`date`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
回答by J.P.A
you just have to replace CURRENT_DATE() by NOW() in your query. I tried it and it's look ok.
你只需要在你的查询中用 NOW() 替换 CURRENT_DATE() 。我试过了，看起来没问题。
回答by omostan
This may be a little late but I think it might be helpful to someone else.
这可能有点晚了，但我认为它可能对其他人有帮助。
My approach has been to use getdate() like this:
我的方法是像这样使用 getdate() ：
[TimeStamp] [datetime] NOT NULL CONSTRAINT [DF_myTable_TimeStamp] DEFAULT (getdate()).
[TimeStamp] [datetime] NOT NULL CONSTRAINT [DF_myTable_TimeStamp] DEFAULT (getdate())。
Where [TimeStamp] is the column in question.
其中 [TimeStamp] 是相关列。
The result is for example: 2017-11-02 11:58:34.203
结果例如：2017-11-02 11:58:34.203
To trim this, I use the following
为了修剪这个，我使用以下
declare @mydate datetime
声明@mydate 日期时间
set @mydate = '2017-11-02 11:58:34.203'
设置@mydate = '2017-11-02 11:58:34.203'
SELECT try_convert(nvarchar(20), @mydate, 120)
选择 try_convert(nvarchar(20)，@mydate，120)
This final result is: 2017-11-02 11:58:34
这个最终结果是：2017-11-02 11:58:34
