数据文件中空格加载入not null的表列中

数据文件
[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


3 回答

加载参数PRESERVE BLANKS:用于设定是否保留字段内容两端的空格,默认不保留空格。
不保留空格的情况下将数据中的空格当做null处理,与表列定义中not null矛盾,所以加载报错。

2018-7-3 15:28
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)修改表结构字段为允许nulldefault 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)
gbase>
4)修改表结构字段为允许nulldefault ''
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)

2018-7-3 15:29
解决完毕
2018-7-10 16:04

撰写回答

您需要登录后才可以回帖 登录 | 立即注册

提问者

发布62
回答178

相关资料