原生 Oracle 数据库迁移到 OceanBase 数据库 MySQL 模式租户中存在 TIMESTAMP 精度问题的原因和解决方法

  SQL> create table test_timestamp (n1 int, t1 timestamp);

  Table created.

  SQL> insert into test_timestamp (n1,t1) values (1,current_timestamp);

  1 row created.

  SQL> commit;

  Commit complete.

  SQL> ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';

  Session altered.

  SQL> select * from test_timestamp;

  N1
  ----------
  T1
  --------------------------------
      1
  2024-02-06 14:45:29.799176

create table test_timestamp (n1 int, t1 timestamp);

Server version: 8.0.31 MySQL Community Server - GPL

-- 指定精度为 6 后则无精度丢失
mysql> create table test_timestamp3 (n1 int, t1 timestamp(6));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT into test_timestamp3 VALUES (2, '2099-04-01 12:30:45.123456' );
ERROR 1292 (22007): Incorrect datetime value: '2099-04-01 12:30:45.123456' for column 't1' at row 1

mysql> INSERT into test_timestamp3 VALUES (2, '2037-04-01 12:30:45.123456' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into test_timestamp3 VALUES (2, '2038-01-18 12:30:45.123456' );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT into test_timestamp3 VALUES (2, '2038-01-19 12:30:45.123456' );
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 12:30:45.123456' for column 't1' at row 1

mysql> INSERT into test_timestamp3 VALUES (2, '2038-04-01 12:30:45.123456' );
ERROR 1292 (22007): Incorrect datetime value: '2038-04-01 12:30:45.123456' for column 't1' at row 1

MySQL [test]> INSERT into test_timestamp3 VALUES (2, '9099-04-01 12:30:45.123456' );
Query OK, 1 row affected (0.005 sec)

MySQL [test]> SELECT * FROM test_timestamp3;
+------+----------------------------+
| n1   | t1                         |
+------+----------------------------+
|    1 | 2024-02-06 14:45:29.799176 |
|    2 | 2023-04-01 12:30:45.123456 |
|    2 | 2043-04-01 12:30:45.123456 |
|    2 | 2099-04-01 12:30:45.123456 |
|    2 | 9099-04-01 12:30:45.123456 |
+------+----------------------------+
5 rows in set (0.001 sec)