如何將 addtime(current_timestamp, ‘15:00:00’) 設置為 MySQL 時間戳列預設值？

我想設置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

這個怎麼做？謝謝。

兩件事情

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。

引用自：https://dba.stackexchange.com/questions/5331