oracle查出某個欄位帶空格的資料

賀子_DBA時代發表於2017-10-28
Oracle
問題描述：把一個表從oracle同步到阿里雲 rds,居然報錯違反唯一鍵，很是詫異，然後具體檢視報錯的那條資料，結果如下：果真有兩條。
MySQL [bidinfo]> select record_id ,login_id from meminfo.t_member_info where login_id='woaini1314';
+-----------+-------------+
| record_id | login_id |
+-----------+-------------+
| 15142804 | woaini1314 |
| 78124319 | woaini1314 |
+-----------+-------------+
2 rows in set (0.00 sec)
然後根據id在oracle中查詢，發現其中一個帶空格，一個不帶空格，
select record_id ,login_id from infoservice.t_member_info where record_id in(15142804 ,78124319 );
這就麻煩了，這可是使用者資訊，不能隨便刪除，猜測是使用者在註冊的時候，多打了個空格，然後發現登陸不上去，就從新註冊了個不帶空格的相同名字的使用者，然而oracle能識別到空格，但是mysql識別不到空格，正好我們有會員登陸日誌，經查詢確實是猜想這樣。那麼下來怎麼統計出帶空格的使用者的主鍵record_id呢？
具體描述：
liuwenhe 和liuwenhe+空格
由於oracle中識別空格，以下兩條sql查的結果不一樣。
select record_id ,login_id from t_member_info where login_id='woaini1314';
select record_id ,login_id from t_member_info where login_id='woaini1314 ';
但是mysql中不識別空格，以下查詢的結果是一樣的。都是兩條
MySQL [bidinfo]> select record_id ,login_id from meminfo.t_member_info where login_id='woaini1314';
+-----------+-------------+
| record_id | login_id |
+-----------+-------------+
| 15142804 | woaini1314 |
| 78124319 | woaini1314 |
+-----------+-------------+
2 rows in set (0.00 sec)
MySQL [bidinfo]> select record_id ,login_id from meminfo.t_member_info where login_id='woaini1314 '; +-----------+-------------+
| record_id | login_id |
+-----------+-------------+
| 15142804 | woaini1314 |
| 78124319 | woaini1314 |
+-----------+-------------+
2 rows in set (0.00 sec)
問題出現了，這個login_id是個唯一鍵， oracle中不衝突，遷移到rds就違反了唯一鍵了，
如下怎麼查出oracle中帶空格的record_id,以便於從mysql中刪除帶空格的違反唯一鍵的資料。
一：先查出有問題的login_id（去掉空格之後的login_id）
select count(record_id),trim(login_id) from infoservice.t_member_info group by trim(login_id) having count(record_id)>1 ；
二：然後查出所有有問題的login_id（包括帶空格的和不帶空格的）,放到一個臨時表
create table liuwenhe.t_mmeber_info_bak as select record_id ,login_id from t_member_info where trim(login_id) in (select login from (
select count(record_id),trim(login_id) login from infoservice.t_member_info group by trim(login_id) having count(record_id)>1 ))
三：最後利用前兩個步驟查出的結果求出有問題的login_id中帶空格的那條資料的對應的record_id.
select record_id from t_mmeber_info_bak where login_id not in (select trim(login_id) from t_mmeber_info_bak) ；
四：導成txt檔案，然後利用lord 進mysql。
最後根據record_id在mysql中刪除相應的資料，即可，



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2146545/，如需轉載，請註明出處，否則將追究法律責任。

相關文章