MySQL之LOAD DATA INFILE和Oracle之SPOOL过滤字段空格

本文主要是介绍MySQL之LOAD DATA INFILE和Oracle之SPOOL过滤字段空格，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

18:15分接到电话说要配合项目更新做定时的数据同步导入操作，在晚饭前赶着做完了等他们测试，结果饭还没扒几口，说数据有空格导致的问题，改咯（要么在导出端改，要么导入端改）：

1. Oracle之SPOOL过滤字段空格

数据源库是Oracle，使用SPOOL导出为TXT格式：

set linesize 1000
set pagesize 0
set echo off
set heading off
set feedback off
set colsep "||"
SET trims ON
set term off
SET trimspool ON
SET trimout ON
spool '/mnt/upload/wxspool/fansstat.txt'
SELECT to_char(trunc(sysdate - 1), 'yyyy-MM-dd') as statDate, trim(sex), trim(country), trim(province), trim(city), count(1) as fansCntFROM tb_sdgame_fans
WHERE orignalid = 'gh_82be7859af49' AND subscribe = 1
GROUP BY sex, country, province, city;

注意：以上的导出脚本并不能解决导出每列数据的去空格问题。
trim()函数的去空格功能在SPOOL导出数据得不到体现，SQL*PLUS命令窗口执行SELECT时，如果不用COL命令去显示每列的宽度，即便trim()了空格，也会按照字段原始定义的宽度以空格形式补全列值，参详如下例子：

SQL> desc tb_sdgame_fans;Name					   Null?    Type----------------------------------------- -------- ----------------------------CITY						    VARCHAR2(256)COUNTRY					    VARCHAR2(256)SQL> SELECT country,city FROM tb_sdgame_fans WHERE orignalid = 'gh_82be7859af49' AND subscribe = 1 AND ROWNUM<5;COUNTRY
--------------------------------------------------------------------------------
CITY
--------------------------------------------------------------------------------
中国
海口安道尔中国
大兴COUNTRY
--------------------------------------------------------------------------------
CITY
--------------------------------------------------------------------------------
中国
西安SQL> SELECT trim(country),trim(city) FROM tb_sdgame_fans WHERE orignalid = 'gh_82be7859af49' AND subscribe = 1 AND ROWNUM<5;TRIM(COUNTRY)
--------------------------------------------------------------------------------
TRIM(CITY)
--------------------------------------------------------------------------------
中国
海口安道尔中国
大兴TRIM(COUNTRY)
--------------------------------------------------------------------------------
TRIM(CITY)
--------------------------------------------------------------------------------
中国
西安
SQL> col country for a10;
SQL> col city for a10;
SQL> SELECT country,city FROM tb_sdgame_fans WHERE orignalid = 'gh_82be7859af49' AND subscribe = 1 AND ROWNUM<5;
COUNTRY    CITY
---------- ----------
中国       海口
安道尔
中国       大兴
中国       西安

换一种思路，将**各列值连接**来解决问题：

set linesize 1000
set pagesize 0
set echo off
set heading off
set feedback off
--set colsep "||"
SET trims ON
set term off
SET trimspool ON
SET trimout ON
spool '/mnt/upload/wxspool/fansstat.txt'
--如下select以“,”分隔各列值，上面的set colsep "||"就无需定义了
SELECT to_char(trunc(sysdate - 1), 'yyyy-MM-dd')||','||sex||','||country||','||province||','||city||','||count(1) as fansCnt 
FROM tb_wxyx_fans 
WHERE orignalid = 'gh_82be7859af49' AND subscribe = 1 
GROUP BY sex, country, province, city;
spool off;

2. MySQL之LOAD DATA INFILE过滤字段空格

目标库是MySQL，使用LOAD DATA INFILE导入，在已有空格的TXT数据文件基础上使用trim()函数来实现过滤空格：

load data local infile "/mnt/upload/wxspool/userstat.txt" into table tb_user_dimensions 
fields terminated by '||' (statDate,sex,country, province,city,fansCnt) 
set statDate=statDate,sex=trim(sex),country=trim(country), province=trim(province),city=trim(city),fansCnt=fansCnt;