load data 文件中数值类型字段内容为NULL报错，替换成\N解决，请问有更好的解决办法吗

OceanBase
#1

【产品名称】oceanbase

【产品版本】3.1.1

【问题描述】load data 文件中数值类型字段内容为NULL报错，替换成\N解决，原来写入mysql是正常的，请问有更好的解决办法吗

#2

不需要特殊的处理，测试如下：

mysql> select * from test;
Empty set (0.01 sec)


mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` float(3,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT 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.00 sec)


mysql> load data /*+ parallel(4) */ infile '/tmp/1.txt' into table test fields terminated by ',' enclosed by '''' lines terminated by '\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from test;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | NULL | 1.20 |
|  2 |    1 | NULL |
|  3 |    3 | 1.50 |
+----+------+------+
3 rows in set (0.00 sec)



mysql> system cat /tmp/1.txt
1,NULL,1.2
2,1,NULL
3,3,1.5
mysql> exit
1 个赞