oceanbase [Feature]: MySQL8 compatibility: support column DEFAULT (UNIX

CREATE TABLE IF NOT EXISTS cronErr ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,cron_id INT UNSIGNED NOT NULL,code INT NOT NULL,msg MEDIUMBLOB NOT NULL,ts BIGINT UNSIGNED NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id) ) 这个在mysql中是可以的

5.7.25-OceanBase_CE-v4.2.1.3 MySQL模式报错

请问有没有什么可用的替代方案（不改变字段类型的情况下）

SqlError: (conn=3145734, no: 1067, SQLState: 42000) Invalid default value for 'ts' sql: CREATE TABLE IF NOT EXISTS cronErr ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,cron_id INT UNSIGNED NOT NULL,code INT NOT NULL,msg MEDIUMBLOB NOT NULL,ts BIGINT UNSIGNED NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id) );; at module.exports.createError (/root/i18n/srv/node_modules/mariadb/lib/misc/errors.js:64:10) at PacketNodeEncoded.readError (/root/i18n/srv/node_modules/mariadb/lib/io/packet.js:589:19) at Prepare.readPrepareResultPacket (/root/i18n/srv/node_modules/mariadb/lib/cmd/prepare.js:107:28) at PacketInputStream.receivePacketBasic (/root/i18n/srv/node_modules/mariadb/lib/io/packet-input-stream.js:85:9) at PacketInputStream.onData (/root/i18n/srv/node_modules/mariadb/lib/io/packet-input-stream.js:135:20) at Socket.emit (node:events:519:28) at addChunk (node:internal/streams/readable:559:12) at readableAddChunkPushByteMode (node:internal/streams/readable:510:3) at Readable.push (node:internal/streams/readable:390:5) at TCP.onStreamRead (node:internal/stream_base_commons:190:23) { sqlMessage: "Invalid default value for 'ts'", sql: 'CREATE TABLE IF NOT EXISTS cronErr (\n' + ' id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,cron_id INT UNSIGNED NOT NULL,code INT NOT NULL,msg MEDIUMBLOB NOT NULL,ts BIGINT UNSIGNED NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id)\n' + ');;', fatal: false, errno: 1067, sqlState: '42000', code: 'ER_INVALID_DEFAULT' }

Jan 24 '24 07:01 i18nsite

What's the mysql version did you try?

Jan 25 '24 02:01 hnwyllmm

https://stackoverflow.com/questions/5331026/is-it-possible-to-create-a-column-with-a-unix-timestamp-default-in-mysql

我用的mariadb最新版，我发现mysql的确不支持这种写法。

但是mysql8支持另外一种写法，也能满足我的需求，如下，

CREATE TABLE IF NOT EXISTS cronErr (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
cron_id INT UNSIGNED NOT NULL,
code INT NOT NULL,msg MEDIUMBLOB NOT NULL,
ts  BIGINT UNSIGNED NOT NULL DEFAULT (UNIX_TIMESTAMP()),
PRIMARY KEY (id)
)

OceanBase不支持

Jan 25 '24 02:01 i18nsite

Passed in mysql 8.0：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE IF NOT EXISTS cronErr (
    -> id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> cron_id INT UNSIGNED NOT NULL,
    -> code INT NOT NULL,msg MEDIUMBLOB NOT NULL,
    -> ts  BIGINT UNSIGNED NOT NULL DEFAULT (UNIX_TIMESTAMP()),
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>

Failed with OceanBase 4.3

Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221488124
Server version: OceanBase 4.3.0.0 (r1-954db5da45d850842e13114b329c35234ebdc3d6) (Built Jan 23 2024 11:01:52)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [test]> CREATE TABLE IF NOT EXISTS cronErr (
    -> id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> cron_id INT UNSIGNED NOT NULL,
    -> code INT NOT NULL,msg MEDIUMBLOB NOT NULL,
    -> ts  BIGINT UNSIGNED NOT NULL DEFAULT (UNIX_TIMESTAMP()),
    -> PRIMARY KEY (id)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '(UNIX_TIMESTAMP()),
PRIMARY KEY (id)
)' at line 5

Jan 25 '24 02:01 hnwyllmm

mysql 5.7 doesn't support this feature.

Jan 25 '24 03:01 hnwyllmm

是的，参见 https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

Now, with MySQL v8+, you can to do this just in parentheses:

CREATE TABLE t1 ( -- literal defaults i INT DEFAULT 0, c VARCHAR(10) DEFAULT '', -- expression defaults f FLOAT DEFAULT (RAND() * RAND()), b BINARY(16) DEFAULT (UUID_TO_BIN(UUID())), d DATE DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR), p POINT DEFAULT (Point(0,0)), j JSON DEFAULT (JSON_ARRAY()) );

Jan 25 '24 03:01 i18nsite

[Feature]: MySQL8 compatibility: support column DEFAULT (UNIX_TIMESTAMP())