OceanBase 数据库租户变量 explicit_defaults_for_timestamp 的使用说明

-- 在 OceanBase 数据库 MySQL 模式的租户中
MySQL [test]> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set (0.001 sec)

MySQL [test]> select * from information_schema.global_variables where variable_name='explicit_defaults_for_timestamp';
+---------------------------------+----------------+
| VARIABLE_NAME                   | VARIABLE_VALUE |
+---------------------------------+----------------+
| explicit_defaults_for_timestamp | ON             |
+---------------------------------+----------------+
1 row in set (0.001 sec)

-- 在当前 session 上开启该功能
set explicit_defaults_for_timestamp=ON;
set explicit_defaults_for_timestamp=1;
set session explicit_defaults_for_timestamp=on;
set session explicit_defaults_for_timestamp=1;
set @@explicit_defaults_for_timestamp=on;
set @@explicit_defaults_for_timestamp=1;
set @@session.explicit_defaults_for_timestamp=ON;
set @@session.explicit_defaults_for_timestamp=1;

-- 在全局级别开启该功能
set global explicit_defaults_for_timestamp=ON;
set global explicit_defaults_for_timestamp=1;
set @@global.explicit_defaults_for_timestamp=on;
set @@global.explicit_defaults_for_timestamp=1;

-- 在当前 session 上关闭该功能
set explicit_defaults_for_timestamp=OFF;
set explicit_defaults_for_timestamp=0;
set session explicit_defaults_for_timestamp=off;
set session explicit_defaults_for_timestamp=0;
set @@explicit_defaults_for_timestamp=off;
set @@explicit_defaults_for_timestamp=0;
set @@session.explicit_defaults_for_timestamp=OFF;
set @@session.explicit_defaults_for_timestamp=0;

-- 在全局级别关闭该功能
set global explicit_defaults_for_timestamp=OFF;
set global explicit_defaults_for_timestamp=0;
set @@global.explicit_defaults_for_timestamp=off;
set @@global.explicit_defaults_for_timestamp=0;

MySQL [test]> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.001 sec)

MySQL [test]> create table test_table (
  c1 timestamp,
  c2 timestamp,
  c3 timestamp default '2010-01-01 00:00:00'
);
Query OK, 0 rows affected (0.134 sec)

MySQL [test]> show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `c3` timestamp NOT NULL DEFAULT '2010-01-01 00:00:00'
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.015 sec)

MySQL [test]> create table test_table (id int, c1 timestamp not null default current_timestamp);
Query OK, 0 rows affected (0.102 sec)

MySQL [test]> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set (0.004 sec)

MySQL [test]> insert into test_table(id) values (1);
Query OK, 1 row affected (0.019 sec)

MySQL [test]> select * from test_table;
+------+---------------------+
| id   | c1                  |
+------+---------------------+
|    1 | 2025-03-31 16:05:23 |
+------+---------------------+
1 row in set (0.013 sec)

MySQL [test]> insert into test_table(id,c1) values (2,null);
ERROR 1048 (23000): Column 'c1' cannot be null
MySQL [test]> set explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected (0.000 sec)

MySQL [test]> insert into test_table(id,c1) values (2,null);
Query OK, 1 row affected (0.005 sec)

MySQL [test]> select * from test_table;
+------+---------------------+
| id   | c1                  |
+------+---------------------+
|    1 | 2025-03-31 16:05:23 |
|    2 | 2025-03-31 16:05:41 |
+------+---------------------+
2 rows in set (0.003 sec)