MySQL:将日期时间插入其他日期时间字段

2022-01-09 00:00:00 datetime sql insert mysql

我有一个包含 DATETIME 列的表.我想选择这个日期时间值并将其插入另一列.

I have a table with a DATETIME column. I would like to SELECT this datetime value and INSERT it into another column.

我这样做了(注意:'2011-12-18 13:17:17' 是前 SELECT 从 DATETIME 字段中给我的值):

I did this (note: '2011-12-18 13:17:17' is the value the former SELECT gave me from the DATETIME field):

UPDATE products SET former_date=2011-12-18 13:17:17 WHERE id=1

得到

    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 '13:17:17 WHERE itemid=1' at line 1

好的,我知道在其中放一个不带引号的字符串是错误的,但是 DATETIME 首先只是一个字符串吗?我在里面放了什么做什么?我想要的只是将现有值可靠地转移到新的日期时间字段...

Ok, I understand it's wrong to put an unquoted string in there, but is DATETIME just a string in the first place? What do I put in there? All I want is reliably transfer the existing value over to a new datetime field...

我问的原因是:我有这个特殊的定义,DATETIME,不知何故,我认为它在处理日期时给了我一些安全性和其他优势.现在看来它只是一个专门的 VARCHAR,可以这么说.

The reason I ask is: I have this special definition, DATETIME, and somehow I thought it gives me some security and other advantages when handling dates. Now it seems it is simply a specialized VARCHAR, so to speak.

感谢您的回答,看来这确实是预期的行为.

Thanks for your answers, it seems this is indeed the intended behaviour.

推荐答案

根据 MySQL 文档,您应该能够将该日期时间字符串括在单引号中,('YYYY-MM-DD HH:MM:SS')它应该可以工作.看这里:日期和时间文字

According to MySQL documentation, you should be able to just enclose that datetime string in single quotes, ('YYYY-MM-DD HH:MM:SS') and it should work. Look here: Date and Time Literals

所以,在你的情况下,命令应该如下:

So, in your case, the command should be as follows:

UPDATE products SET former_date='2011-12-18 13:17:17' WHERE id=1

相关文章