• 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)
  • 相关阅读:
    spring boot整合mybatis，mybatis generator ，自定义typhandler
    leetcode：1579. 保证图可完全遍历【并查集思路】
    海藻酸钠-聚乳酸|PLA-alginate|海藻酸钠-PEG-聚乳酸/聚已内酯PCL
    【深度学习】【caffe】【python】【docker】微信 二维码检测，手写数字识别
    ORACLE 基础
    【附源码】Python计算机毕业设计商场会员管理系统
    day14_集合
    Edge 浏览器『版本回退』和『关闭更新』
    04、JavaWeb启程——数据库
    tap栏切换
  • 原文地址：https://blog.csdn.net/ls_hong/article/details/127606717