非空列的数据加载

最新推荐文章于 2024-09-12 17:13:11 发布

m0_49291452

最新推荐文章于 2024-09-12 17:13:11 发布

阅读量426

点赞数

文章标签： java 数据库服务器

本文链接：https://blog.youkuaiyun.com/m0_49291452/article/details/128075889

版权

本文介绍了在GBase数据库中遇到非空列含有空格数据加载时的问题及解决方法。通过设置加载参数PRESERVEBLANKS、修改表结构允许字段为NULL或默认为空字符串，解决了数据加载冲突。详细步骤包括添加保留空格参数、修改表结构以及调整数据文件格式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题现象

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

解决方法

数据文件：

[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)

[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)

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