您好,登錄后才能下訂單哦!
這篇文章給大家介紹如何進(jìn)行MYSQL特殊字符的處理,內(nèi)容非常詳細(xì),感興趣的小伙伴們可以參考借鑒,希望對(duì)大家能有所幫助。
單引號(hào),以及行尾的斜杠引起的困惑:
這一次的問題,我們直接從實(shí)際的工作中說起:
工作內(nèi)容簡介: 有一批用戶ID存在文件里,需要從數(shù)據(jù)庫里刪除?
做這個(gè)事情,可能有很多的方法:
1, 把ID導(dǎo)入到數(shù)據(jù)庫中,用SQL直接做表關(guān)聯(lián)去刪除 ;
2, 用SHELL(或其他語言)寫個(gè)小程序,根據(jù)文件里的ID做一個(gè)FOR 循環(huán),然后在MYSQL中去刪除 ;
3, 用sed直接把ID轉(zhuǎn)成delete語句,完了直接運(yùn)行即可;
[@more@]
由于數(shù)據(jù)量較大(1.6億),顯然,我會(huì)用偷懶以及簡單的方法3 :
----------------------------------------------
[root@im_ctuallot1 tmp]# cat loginid.txt
xouqun76818
ogku15mtb7c
jinlongkaikai@163.com
曾樸紹283902
輕舞飛揚(yáng)061129付了
[root@im_ctuallot1 tmp]# sed -e "s/^/delete from ctulog.db_allot_center_64 where long_id='/g" -e "s/$/';/g" loginid.txt > loginid.sql
[root@im_ctuallot1 tmp]# cat loginid.sql
delete from ctulog.db_allot_center_64 where long_id='xouqun76818';
delete from ctulog.db_allot_center_64 where long_id='ogku15mtb7c';
delete from ctulog.db_allot_center_64 where long_id='jinlongkaikai@163.com';
delete from ctulog.db_allot_center_64 where long_id='曾樸紹283902';
delete from ctulog.db_allot_center_64 where long_id='輕舞飛揚(yáng)061129付了';
mysql -uroot -h227.0.0.1 --default-character-set=latin1 --force ctulogdb < loginid.sql
--force 是防止某個(gè)SQL出現(xiàn)錯(cuò)誤,而導(dǎo)致整個(gè)任務(wù)終止;
---------------------------------------------
搞定。
這看似非常簡單的方法,也暴露出很多的問題,結(jié)果1.6行數(shù)據(jù)只成功刪除了3300W,任務(wù)失敗;
其實(shí)是我把這個(gè)任務(wù)想得太簡單了: 在用戶ID中存在任何可能的字符 ,如:
bao'pijkl
tingting831118
注意,在用戶ID中有" ' ", 在行末尾有:" ";
我們把這樣的語句滲雜到其他ID中,我們看會(huì)有怎么的效果;
[root@im_ctuallot1 tmp]# cat loginid.txt
xouqun76818
bao'pijkl
ogku15mtb7c
jinlongkaikai@163.com
曾樸紹283902
tingting831118
輕舞飛揚(yáng)061129付了
[root@im_ctuallot1 tmp]# sed -e "s/^/delete from ctulog.db_allot_center_64 where long_id='/g" -e "s/$/';/g" loginid.txt > loginid.sql
[root@im_ctuallot1 tmp]# cat loginid.sql
delete from ctulog.db_allot_center_64 where long_id='xouqun76818';
delete from ctulog.db_allot_center_64 where long_id='bao'pijkl';
delete from ctulog.db_allot_center_64 where long_id='ogku15mtb7c';
delete from ctulog.db_allot_center_64 where long_id='jinlongkaikai@163.com';
delete from ctulog.db_allot_center_64 where long_id='曾樸紹283902';
delete from ctulog.db_allot_center_64 where long_id='tingting831118';
delete from ctulog.db_allot_center_64 where long_id='輕舞飛揚(yáng)061129付了';
[root@im_ctuallot1 tmp]# mysql -uroot -h227.0.0.1 --default-character-set=latin1 --force ctulog < loginid.sql
ERROR at line 2: Unknown command '''.
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pijkl';
delete from ctulog.db_allot_center_64 where long_id='ogku15mtb7c';
delet' at line 1
會(huì)出現(xiàn)一堆這樣的錯(cuò)誤;
如果你手動(dòng)把以上SQL貼到MYSQL中去執(zhí)行:
root@127.0.0.1 : ctulog 15:59:04>
root@127.0.0.1 : ctulog 15:59:04> delete from ctulog.db_allot_center_64 where long_id='xouqun76818';
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1 : ctulog 15:59:05> delete from ctulog.db_allot_center_64 where long_id='bao'pijkl';
'> delete from ctulog.db_allot_center_64 where long_id='ogku15mtb7c';
'> delete from ctulog.db_allot_center_64 where long_id='jinlongkaikai@163.com';
'> delete from ctulog.db_allot_center_64 where long_id='曾樸紹283902';
'> delete from ctulog.db_allot_center_64 where long_id='tingting831118';
ERROR:
Unknown command '''.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pijkl';
delete from ctulog.db_allot_center_64 where long_id='ogku15mtb7c';
delet' at line 1
root@127.0.0.1 : ctulog 15:59:05> delete from ctulog.db_allot_center_64 where long_id='輕舞飛揚(yáng)061129付了';
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1 : ctulog 15:59:10>
只有第一條,最后一條執(zhí)行成功了;
到這里我想大家應(yīng)該明白了,
最關(guān)鍵的是單引號(hào)的不匹配導(dǎo)致MYSQL不能正確認(rèn)識(shí)完整的SQL;
注意,在用戶ID中,出現(xiàn) 單個(gè)單引號(hào),或以""結(jié)束,都會(huì)有這個(gè)問題;
問題找到了,其實(shí)解決很簡單,就是先把用戶ID中的單引號(hào)和"$"作一個(gè)轉(zhuǎn)換:
[root@im_ctuallot1 tmp]# cat loginid.txt
xouqun76818
bao'pijkl
ogku15mtb7c
jinlongkaikai@163.com
曾樸紹283902
tingting831118
輕舞飛揚(yáng)061129付了
[root@im_ctuallot1 tmp]# sed -e "s///g" -e "s/'/'/g" -e "s/^/delete from ctulog.db_allot_center_64 where long_id='/g" -e "s/$/';/g" loginid.txt > loginid.sql
[root@im_ctuallot1 tmp]# cat loginid.sql
delete from ctulog.db_allot_center_64 where long_id='xouqun76818';
delete from ctulog.db_allot_center_64 where long_id='bao'pijkl';
delete from ctulog.db_allot_center_64 where long_id='ogku15mtb7c';
delete from ctulog.db_allot_center_64 where long_id='jinlongkaikai@163.com';
delete from ctulog.db_allot_center_64 where long_id='曾樸紹283902';
delete from ctulog.db_allot_center_64 where long_id='tingting831118';
delete from ctulog.db_allot_center_64 where long_id='輕舞飛揚(yáng)061129付了';
[root@im_ctuallot1 tmp]#
是這樣的SQL執(zhí)行就不會(huì)有任何問題。
就這么簡單的問題,耗了5個(gè)小時(shí),還是SHARE一下以免大家在這里浪費(fèi)時(shí)間。
關(guān)于如何進(jìn)行MYSQL特殊字符的處理就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò),可以把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。