主页 / dba / 问题 / 5331
Accepted
Just a learner
Asked: 2011-09-02 23:17:35 +0800 CST

如何将 addtime(current_timestamp, '15:00:00') 设置为 MySQL 时间戳列默认值？

  • 772

我想设置addtime(current_timestamp, '15:00:00')为一个 MySQL 表列的默认值。我尝试使用以下命令执行此操作，但失败了：

mysql> alter table mytable change c1 c2 timestamp null default addtime(current_timestamp, '15:00:00');
ERROR 1064 (42000): 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 'addtime(current_timestamp, '15:00:00')' at line 1

这个怎么做？谢谢。

mysql timestamp

2 个回答

  1. MySQL 不支持列默认值中的函数。CURRENT_TIMESTAMP是您发现的最接近异常的东西：

    DEFAULT 子句指定列的默认值。除了一个例外，默认值必须是一个常量；它不能是函数或表达式。例如，这意味着您不能将日期列的默认值设置为 NOW() 或 CURRENT_DATE 等函数的值。例外情况是您可以将 CURRENT_TIMESTAMP 指定为 TIMESTAMP 列的默认值。

    http://dev.mysql.com/doc/refman/5.5/en/create-table.html#id850368

    • 2
  2. Best Answer

    两件事情

    1. 尝试使用触发器
    2. 使用 DATETIME 而不是 TIMESTAMP

    我运行了这个示例代码：

    use test
drop table if exists rolando;
create table rolando
(
    id int not null auto_increment primary key,
    num int not null,
    c1 datetime
);
delimiter $$
CREATE TRIGGER `rolando_15min`
BEFORE INSERT ON `rolando` FOR EACH ROW
BEGIN
    SET NEW.c1 = now() + INTERVAL 15 minute;
END;
$$
DELIMITER ;
INSERT INTO rolando (num) VALUES (4),(7);
SELECT SLEEP(5);
INSERT INTO rolando (num) VALUES (5),(9);
SELECT *,now() FROM rolando;

    结果如下：

    mysql> use test
Database changed
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> create table rolando
    -> (
    ->     id int not null auto_increment primary key,
    ->     num int not null,
    ->     c1 datetime
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter $$
mysql> CREATE TRIGGER `rolando_15min`
    -> BEFORE INSERT ON `rolando` FOR EACH ROW
    -> BEGIN
    ->     SET NEW.c1 = now() + INTERVAL 15 minute;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> INSERT INTO rolando (num) VALUES (4),(7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+
1 row in set (5.01 sec)

mysql> INSERT INTO rolando (num) VALUES (5),(9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT *,now() FROM rolando;
+----+-----+---------------------+---------------------+
| id | num | c1                  | now()               |
+----+-----+---------------------+---------------------+
|  1 |   4 | 2011-09-02 04:05:56 | 2011-09-02 03:51:01 |
|  2 |   7 | 2011-09-02 04:05:56 | 2011-09-02 03:51:01 |
|  4 |   5 | 2011-09-02 04:06:01 | 2011-09-02 03:51:01 |
|  5 |   9 | 2011-09-02 04:06:01 | 2011-09-02 03:51:01 |
+----+-----+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

    试试看 ！！！

    警告：我尝试了至少 7 种方法来使时间戳像这样工作。不行！！！因此，您必须使用 DATETIME。

    • 2

相关问题