mysql 不加单引号 mysql中的单引号

单引号，以及行尾的斜杠引起的困惑：

这一次的问题，我们直接从实际的工作中说起：

工作内容简介： 有一批用户ID存在文件里，需要从数据库里删除？

做这个事情，可能有很多的方法：
1， 把ID导入到数据库中，用SQL直接做表关联去删除 ；
2， 用SHELL（或其他语言）写个小程序，根据文件里的ID做一个FOR 循环，然后在MYSQL中去删除 ；
3， 用sed直接把ID转成delete语句，完了直接运行即可；

[@more@]

由于数据量较大（1.6亿），显然，我会用偷懒以及简单的方法3 ：
----------------------------------------------

[root@im_ctuallot1 tmp]# cat loginid.txt 
xouqun76818
ogku15mtb7c
[e-mail]曾朴绍283902
轻舞飞扬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 我们把这样的语句渗杂到其他ID中，我们看会有怎么的效果；
[root@im_ctuallot1 tmp]# cat loginid.txt
xouqun76818
bao'pijkl
[e-mail]曾朴绍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 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 root@127.0.0.1 : ctulog 15:59:05> delete from ctulog.db_allot_center_64 where long_id='轻舞飞扬061129付了';
Query OK, 0 rows affected (0.00 sec)root@127.0.0.1 : ctulog 15:59:10> 
只有第一条，最后一条执行成功了；　
到这里我想大家应该明白了，
最关键的是单引号的不匹配导致MYSQL不能正确认识完整的SQL；
注意，在用户ID中，出现 单个单引号，或以""结束，都会有这个问题；问题找到了，其实解决很简单，就是先把用户ID中的单引号和"$"作一个转换：
[root@im_ctuallot1 tmp]# cat loginid.txt 
xouqun76818
bao'pijkl
ogku15mtb7c
[e-mail]
曾朴绍283902
tingting831118
轻舞飞扬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

[root@im_ctuallot1 tmp]# cat loginid.txt 
xouqun76818
ogku15mtb7c
[e-mail]曾朴绍283902
轻舞飞扬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 我们把这样的语句渗杂到其他ID中，我们看会有怎么的效果；
[root@im_ctuallot1 tmp]# cat loginid.txt
xouqun76818
bao'pijkl
[e-mail]曾朴绍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 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 root@127.0.0.1 : ctulog 15:59:05> delete from ctulog.db_allot_center_64 where long_id='轻舞飞扬061129付了';
Query OK, 0 rows affected (0.00 sec)root@127.0.0.1 : ctulog 15:59:10> 
只有第一条，最后一条执行成功了；　
到这里我想大家应该明白了，
最关键的是单引号的不匹配导致MYSQL不能正确认识完整的SQL；
注意，在用户ID中，出现 单个单引号，或以""结束，都会有这个问题；问题找到了，其实解决很简单，就是先把用户ID中的单引号和"$"作一个转换：
[root@im_ctuallot1 tmp]# cat loginid.txt 
xouqun76818
bao'pijkl
ogku15mtb7c
[e-mail]
曾朴绍283902
tingting831118
轻舞飞扬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