南大通用GBase 8a加载通过null_value参数指定null空值的参数

GBase 8a集群数据库，通过load加载时，默认的数据null值是\N，可以通过null_value参数指定空值。

提示

null空值，和空字符串，在GBase数据库内是区分, 查询null需要用 is null, 而空字符串是 =''

指定null为空值

如下生成一行数据，其中name字段是null 表示空值. 通过load语句的null_value 'null' 指定字符串 null 代表空值。

gbase> create table t1(id int, name varchar(100),birth date);
Query OK, 0 rows affected (Elapsed: 00:00:00.15)

gbase> system echo "1,null,2020-01-02" > /home/gbase/t1.txt
gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.102/home/gbase/t1.txt' into table t1 null_value 'null' fields terminated by ',';
Query OK, 1 row affected (Elapsed: 00:00:01.28)
Task 13 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | NULL | 2020-01-02 |
+------+------+------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select * from t1 where name is null;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | NULL | 2020-01-02 |
+------+------+------------+
1 row in set (Elapsed: 00:00:00.01)

指定 \N为空值

如下用\N生成一行数据，并通过 null_value='\\N' 来加载。注意\是转义符，所以需要用\\来代表\, 否则会将原始数据\N直接加载到数据库内。

gbase> system echo "2,\N,2020-03-04" > /home/gbase/t1.txt
gbase> system cat /home/gbase/t1.txt
2,\N,2020-03-04

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.102/home/gbase/t1.txt' into table t1  fields terminated by ',';
Query OK, 1 row affected (Elapsed: 00:00:01.56)
Task 14 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1 where name is null;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | NULL | 2020-01-02 |
+------+------+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | NULL | 2020-01-02 |
|    2 | \N   | 2020-03-04 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.102/home/gbase/t1.txt' into table t1 null_value '\N' fields terminated by ',';
Query OK, 1 row affected (Elapsed: 00:00:01.19)
Task 15 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | NULL | 2020-01-02 |
|    2 | \N   | 2020-03-04 |
|    2 | \N   | 2020-03-04 |
+------+------+------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.102/home/gbase/t1.txt' into table t1 null_value '\\N' fields terminated by ',';
Query OK, 1 row affected (Elapsed: 00:00:01.40)
Task 16 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | NULL | 2020-01-02 |
|    2 | \N   | 2020-03-04 |
|    2 | \N   | 2020-03-04 |
|    2 | NULL | 2020-03-04 |
+------+------+------------+
4 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t1 where name is null;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | NULL | 2020-01-02 |
|    2 | NULL | 2020-03-04 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

GBase 8a 集群加载数据LOAD的方法