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 fansCnt
    FROM 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;
           

  完畢！

2020年08月04日

  K4全身強化進行中，剛虐完兩組波比然後接到工作電話，害怕電話那端腦補喘氣音，急着解釋說在做運動，然後，空氣和信号都尴尬了，窒息啊！！！