• 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)
  • 相关阅读:
    基础练习 圆的面积
    C++大神之路——环境篇
    关于 SAP UI5 SimpleForm 控件里的 ColumnsL 和 labelSpanXL 的测试
    56块钱搭建一个ubuntu 2204 linux 服务器
    springcloud--微服务
    手把手教你用Nodejs 实现一个简单的 Redis客户端
    可编程 USB 转串口适配器开发板 UART 转 SPI 应用
    关于Java Integer和Long使用equals直接比较
    [SpringMVC1]简介与快速入门案例详解
    记一次详细的实战渗透
  • 原文地址：https://blog.csdn.net/ls_hong/article/details/127606717