Oracle兼容-语法-字符串列设置CURRENT_TIMESTAMP默认值

GreatSQL支持对 VARCHAR/VARCHAR2 类型列设置默认值为 CURRENT_TIMESTAMP。

1. 语法

1. VARCHAR(n) DEFAULT CURRENT_TIMESTAMP[([fsp])]

3. VARCHAR2(n) DEFAULT CURRENT_TIMESTAMP[([fsp])]

参数备注：

  1. 参数 n 值必须大于等于48。
  2. 参数 fsp 可以指定时间精度，最大值为6。

2. 示例

1. -- n值小于48会报错
2. greatsql> CREATE TABLE t1 (
3. id INT,
4. c1 VARCHAR(47) DEFAULT CURRENT_TIMESTAMP);
5. ERROR 1067 (42000): Invalid default value for 'c1'

7. greatsql> CREATE TABLE t1 (
8. id INT NOT NULL,
9. c1 VARCHAR(48) NOT NULL DEFAULT CURRENT_TIMESTAMP,
10. c2 VARCHAR2(48) NOT NULL DEFAULT CURRENT_TIMESTAMP);

12. greatsql> SET sql_mode = ORACLE;
13. greatsql> INSERT INTO t1(id) VALUES(1);
14. greatsql> INSERT INTO t1 VALUES (2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

16. greatsql> SET sql_mode = DEFAULT;
17. greatsql> INSERT INTO t1(id) VALUES(3);
18. greatsql> INSERT INTO t1 VALUES(4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

20. greatsql> SELECT * FROM t1;
21. +----+------------------------------------+------------------------------------+
22. | id | c1                                 | c2                                 |
23. +----+------------------------------------+------------------------------------+
24. |  1 | 23-11-23 03:33:05.672474 PM +08:00 | 23-11-23 03:33:05.672474 PM +08:00 |
25. |  2 | 23-11-23 03:33:05.673292 PM +08:00 | 23-11-23 03:33:05.673292 PM +08:00 |
26. |  3 | 2023-11-23 15:33:05                | 2023-11-23 15:33:05                |
27. |  4 | 2023-11-23 15:33:05                | 2023-11-23 15:33:05                |
28. +----+------------------------------------+------------------------------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx