GBASE 8A v953报错集锦51--非空列的数据加载

问题现象

对非空列进行加载，数据为空格时如何处理？

解决方法

数据文件：

[root@pst-red214 gbase]# cat t1.txt

aa| |bb

建表语句：

create table t1(c1 varchar(10),c2 varchar(10) not null,c3 varchar(10));

执行加载，数据跳过：

gbase> load data infile 'sftp://gbase@192.168.105.214/home/gbase/t1.txt' into

table t1 fields terminated by '|';

Query OK, 0 rows affected (Elapsed: 00:00:00.66)

Task 126 finished, Loaded 0 records, Skipped 1 records

问题分析：

加载参数 PRESERVE BLANKS ：用于设定是否保留字段内容两端的空格，默认不保

留空格。

不保留空格的情况下将数据中的空格当做 null 处理，与表列定义中 not null 矛盾，

所以加载报错。

问题解决：

1. 加上参数 PRESERVE BLANKS，保留空格：

gbase> load data infile

'sftp://gbase@192.168.105.214/home/gbase/t1.txt' into table t1

fields terminated by '|' PRESERVE BLANKS;

Query OK, 1 row affected (Elapsed: 00:00:01.37)

Task 127 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;

+------+----+------+

| c1 | c2 | c3 |

+------+----+------+

| aa | | bb |

+------+----+------+

1 row in set (Elapsed: 00:00:00.03)

2. 数据文件中增加包围符：

[root@pst-red214 gbase]# cat t1.txt

'aa'|' '|'bb'

gbase> load data infile

'sftp://gbase@192.168.105.214/home/gbase/t1.txt' into table t1

fields terminated by '|' enclosed by '''';

Query OK, 1 row affected (Elapsed: 00:00:01.22)

Task 132 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;

+------+----+------+

| c1 | c2 | c3 |

+------+----+------+

| aa | | bb |

+------+----+------+

1 row in set (Elapsed: 00:00:00.02)

3. 修改表结构字段为允许 null ， default null:

gbase> create table t1(c1 varchar(10),c2 varchar(10),c3 varchar(10));

Query OK, 0 rows affected (Elapsed: 00:00:01.28)

gbase> show create table t1;

+-------+------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------+

| Table | Create Table

|

+-------+------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------+

| t1 | CREATE TABLE "t1" (

"c1" varchar(10) DEFAULT NULL,

"c2" varchar(10) DEFAULT NULL,

"c3" varchar(10) DEFAULT NULL

) ENGINE=EXPRESS DEFAULT CHARSET=utf8

TABLESPACE='sys_tablespace' |

+-------+------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------+

1 row in set (Elapsed: 00:00:00.00)

gbase> load data infile 'sftp://gbase@192.168.105.214

/home/gbase/t1.txt' into table t1 fields terminated by '|';

Query OK, 1 row affected (Elapsed: 00:00:01.23)

Task 135 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;

+------+------+------+

| c1 | c2 | c3 |

+------+------+------+

| aa | NULL | bb |

+------+------+------+

1 row in set (Elapsed: 00:00:00.02)

4. 修改表结构字段为允许 null ， default '' ：

gbase> create table t1(c1 varchar(10),c2 varchar(10) default '',c3

varchar(10));

Query OK, 0 rows affected (Elapsed: 00:00:01.34)

gbase> show create table t1;

+-------+---------------------------------------------------------

------------------------------------------------------------------

------------------------------------------------------------+

| Table | Create Table

|

+-------+---------------------------------------------------------

------------------------------------------------------------------

------------------------------------------------------------+

| t1 | CREATE TABLE "t1" (

"c1" varchar(10) DEFAULT NULL,

"c2" varchar(10) DEFAULT '',

"c3" varchar(10) DEFAULT NULL

) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |

+-------+---------------------------------------------------------

------------------------------------------------------------------

------------------------------------------------------------+

1 row in set (Elapsed: 00:00:00.01)

gbase> load data infile 'sftp://gbase@192.168.105.214

/home/gbase/t1.txt' into table t1 fields terminated by '|';

Query OK, 1 row affected (Elapsed: 00:00:01.26)

Task 140 finished, Loaded 1 records, Skipped 0 records

gbase> select * from t1;

+------+------+------+

| c1 | c2 | c3 |

+------+------+------+

| aa | | bb |

+------+------+------+

row in set (Elapsed: 00:00:00.05)