mysql查询-插入数据unix_timestamp(now())问题 [英] mysql query - insert data unix_timestamp ( now ( ) ) issue

问题描述

我有一个INT(11)列，用于存储当前时间戳(以秒为单位).查询如下:

I have an INT (11) column for storing the current timestamp in seconds. The query looks like:

INSERT INTO `abc` (id, timestamp) VALUES ('', UNIX_TIMESTAMP ( NOW () ) )

我不知道为什么，但是日期没有改变.当我发送查询时，列值没有更改都没有关系.它的价值为1342692014，但我不知道为什么.

I don't know why, but the date isn't changed. It doesn't matter when I send the query, the column value isn't changed. It has 1342692014 value, but I don't know why.

时间戳是否有任何选项或其他功能?我必须以秒为单位存储日期.

Is there any option or other function for timestamps? I must store dates in seconds.

推荐答案

您永远不会在查询中引用timestamp 列.您只有一个字符串:

You never refer to the timestamp column in your query. You only have a string:

INSERT INTO `abc` (id, 'timestamp') VALUES ('', UNIX_TIMESTAMP ( NOW () ) )
                       ^^^^^^^^^^^

---

我通过您的更新代码得到了这个

I get this with your updated code:

错误1630(42000):功能测试.现在不存在.检查 参考手册中的函数名称解析和解析"部分

ERROR 1630 (42000): FUNCTION test.NOW does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

假设它仍然不是实际的代码，并且在修复语法错误后，我无法重现您的结果.我的有根据的猜测是id是一个自动递增的整数主键，您当前的SQL模式正在使MySQL将''用作NULL并插入新行...但是我还没有真正检验过这个假设.

Assuming it's not still the actual code and after fixing the syntax error, I can't reproduce your results. My educated guess is that id is an auto-incremented integer primary key, your current SQL mode is making MySQL take '' as NULL and inserting a new row... But I haven't really tested this hypothesis.

我的工作代码是这样:

CREATE TABLE `abc` (
    `pk` INT(10) NOT NULL AUTO_INCREMENT,
    `id` VARCHAR(10) NULL DEFAULT NULL,
    `timestamp` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`pk`)
)
ENGINE=InnoDB;

INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());
-- Wait a few seconds
INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());
-- Wait a few seconds
INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());

SELECT timestamp FROM abc WHERE id='';

...并返回以下内容:

... and returns this:

+------------+
| timestamp  |
+------------+
| 1342694445 |
| 1342694448 |
| 1342694450 |
+------------+
3 rows in set (0.00 sec)

这篇关于mysql查询-插入数据unix_timestamp(now())问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！