Shulou(Shulou.com)05/31 Report--

This article introduces you how to deal with MYSQL special characters, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Confusion caused by single quotation marks and slashes at the end of the line:

This time, let's start directly from the actual work:

Job description: there are a number of users ID stored in the file, need to be deleted from the database?

There may be many ways to do this:

1. Import ID into the database and use SQL to do table association directly to delete.

2. Write a Mini Program in SHELL (or other languages), make a FOR loop based on the ID in the file, and then delete it in MYSQL.

3. Use sed to convert ID directly into delete statement, and then run it directly.

[@ more@]

Due to the large amount of data (160 million), obviously, I will use lazy and simple method 3:

-

[root@im_ctuallot1 tmp] # cat loginid.txt

Xouqun76818

Ogku15mtb7c

Jinlongkaikai@163.com

Zeng Pushao 283902

I paid 061129 for the light dance.

[root@im_ctuallot1 tmp] # sed-e "s / ^ / delete from ctulog.db_allot_center_64 where long_id='/g"-e "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=' Zeng Pu Shao 283902'

Delete from ctulog.db_allot_center_64 where long_id=' danced softly and paid 061129.

Mysql-uroot-h227.0.0.1-- default-character-set=latin1-- force ctulogdb

< loginid.sql --force 是防止某个SQL出现错误，而导致整个任务终止； --------------------------------------------- 搞定。 这看似非常简单的方法，也暴露出很多的问题，结果1.6行数据只成功删除了3300W,任务失败； 其实是我把这个任务想得太简单了： 在用户ID中存在任何可能的字符 ，如： bao'pijkl tingting831118 注意，在用户ID中有" ' ", 在行末尾有：" "; 我们把这样的语句渗杂到其他ID中，我们看会有怎么的效果； [root@im_ctuallot1 tmp]# cat loginid.txt xouqun76818 bao'pijkl ogku15mtb7c jinlongkaikai@163.com 曾朴绍283902 tingting831118 轻舞飞扬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=' Zeng Pu Shao 283902'

Delete from ctulog.db_allot_center_64 where long_id='tingting831118'

Delete from ctulog.db_allot_center_64 where long_id=' danced softly and paid 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 会出现一堆这样的错误； 如果你手动把以上SQL贴到MYSQL中去执行： 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=' Zeng Pu Shao 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=' Dance Feiyang 061129 paid'

Query OK, 0 rows affected (0.00 sec)

Root@127.0.0.1: ctulog 15:59:10 >

Only the first one and the last one were successfully implemented.

At this point, I think you should understand.

The most important thing is that the mismatch of single quotation marks causes MYSQL to fail to understand the complete SQL correctly.

Note that this problem occurs when a single quotation mark appears in the user ID or ends with "".

The problem is found. In fact, the solution is very simple, which is to convert the single quotation marks and "$" in the user ID first:

[root@im_ctuallot1 tmp] # cat loginid.txt

Xouqun76818

Bao'pijkl

Ogku15mtb7c

Jinlongkaikai@163.com

Zeng Pushao 283902

Tingting831118

I paid 061129 for the light dance.

[root@im_ctuallot1 tmp] # sed-e "sracket racer g"-e "s raceme raceme plaza g"-e "s / delete from ctulog.db_allot_center_64 where long_id='/g"-e "raceme" 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=' Zeng Pu Shao 283902'

Delete from ctulog.db_allot_center_64 where long_id='tingting831118'

Delete from ctulog.db_allot_center_64 where long_id=' danced softly and paid 061129.

[root@im_ctuallot1 tmp] #

Well, there won't be any problems with SQL execution.

It takes 5 hours to ask such a simple question, so it's better to SHARE so that we don't waste time here.

On how to deal with MYSQL special characters to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.