【产品名称】oceanbase
【产品版本】3.1.1
【问题描述】load data 文件中数值类型字段内容为NULL报错,替换成\N解决,原来写入mysql是正常的,请问有更好的解决办法吗
【产品名称】oceanbase
【产品版本】3.1.1
【问题描述】load data 文件中数值类型字段内容为NULL报错,替换成\N解决,原来写入mysql是正常的,请问有更好的解决办法吗
不需要特殊的处理,测试如下:
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