字符串转数字后无法写入的原因和解决方法

问题现象

OceanBase 数据库 MySQL 租户模式下，字符串转数字后无法写入报错 ERROR 5114 (42000): Invalid numeric。

示例，如下。

1. 切换使用的数据库。

   obclient(root@mysql001)[(none)]> use mysql;
   Database changed
   

2. 创建 test 表。

   obclient(root@mysql001)[mysql]> create table test(id int, col varchar(20));
   Query OK, 0 rows affected (1.106 sec)
   

3. 向 test 表插入数据。

   obclient(root@mysql001)[mysql]> insert into test values(1,'/');
   Query OK, 1 row affected (0.017 sec)
   
   obclient(root@mysql001)[mysql]> insert into test values(1,'10');
   Query OK, 1 row affected (0.001 sec)
   

4. 查询使用 convert 函数转换之后的数据。

   obclient(root@mysql001)[mysql]> select convert(col,decimal(9,2)) from test;
   +---------------------------+
   | convert(col,decimal(9,2)) |
   +---------------------------+
   |                      0.00 |
   |                     10.00 |
   +---------------------------+
   2 rows in set (0.003 sec)
   

5. 创建 test2 表。

   obclient(root@mysql001)[mysql]> create table test2(id int(11), col int(11));
   Query OK, 0 rows affected (0.169 sec)
   

6. 使用 insert into select 方式将 test 表中数据插入 test2 表中（直接报错）。

   obclient(root@mysql001)[mysql]> insert into test2 select id, convert(col, decimal(9,2)) from test;
   ERROR 5114 (42000): Invalid numeric
   

问题原因

在 sql_mode 为严格模式下，这种转换方式无法写入到目标表里，需要关闭严格模式。

影响租户

影响 OceanBase 数据库中的 MySQL 租户，对于 SYS 租户和 Oracle 租户无影响。

适用版本

OceanBase 数据所有版本。

解决方法

解决字符串转数字后无法写入报错 ERROR 5114 (42000): Invalid numeric 方法如下。

1. 将 sql_mode 设置为 NO_ZERO_IN_DATE 和 NO_AUTO_CREATE_USER。

   obclient(root@mysql001)[mysql]> set sql_mode="NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER";
   Query OK, 0 rows affected (0.000 sec)
   

2. 重新使用 insert into select 方式将 test 表中数据插入 test2 表中（无报错）。

   obclient(root@mysql001)[mysql]> insert into test2 select id, convert(col, decimal(9,2)) from test;
   Query OK, 2 rows affected (0.005 sec)
   Records: 2  Duplicates: 0  Warnings: 0
   

3. 查询 test2 表内容。

   obclient(root@mysql001)[mysql]> select * from test2;
   +------+------+
   | id   | col  |
   +------+------+
   |    1 |    0 |
   |    1 |   10 |
   +------+------+
   2 rows in set (0.004 sec)
   

有关如何设置 SQL 模式的详情，请参见：sql_mode 。