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