mysql> select * from tb ; +-------------+ | col | +-------------+ | 钢笔 789 \a | | 钢笔 45\\6 | +-------------+ 2 rows in set (0.00 sec)mysql> select * from tb where col like '%?\%' escape '?'; +-------------+ | col | +-------------+ | 钢笔 789 \a | | 钢笔 45\\6 | +-------------+ 2 rows in set (0.00 sec)
我又试了下,有如下记录mysql> select ms from tmp where ms like '%?%'; +-----------+ | ms | +-----------+ | 钢笔 99?9 | | 钢笔 8??8 | +-----------+匹配一个通配符? mysql> select ms from tmp where ms like '%??%' escape '?'; +-----------+ | ms | +-----------+ | 钢笔 99?9 | | 钢笔 8??8 | +-----------+ 2 rows in set (0.00 sec)匹配俩个?? mysql> select ms from tmp where ms like '%????%' escape '?'; +-----------+ | ms | +-----------+ | 钢笔 8??8 | +-----------+ 1 row in set (0.00 sec)自定义转义符,就是我想要的结果 但是自定义转义符对\,结果又不是想象的那样 ?\?\
mysql> select * from t_Ronaldo09 -> ; +-------------+ | id | +-------------+ | 钢笔 789 \a | | 钢笔 45\\6 | | 钢笔 99?9 | | 钢笔 8??8 | +-------------+ 4 rows in set (0.02 sec)mysql> select * from t_Ronaldo09 where instr(id ,'\\'); +-------------+ | id | +-------------+ | 钢笔 789 \a | | 钢笔 45\\6 | +-------------+ 2 rows in set (0.00 sec)mysql> select * from t_Ronaldo09 where instr(id ,'\\\\'); +-------------+ | id | +-------------+ | 钢笔 45\\6 | +-------------+ 1 row in set (0.00 sec)mysql> select * from t_Ronaldo09 where instr(id ,'?'); +-----------+ | id | +-----------+ | 钢笔 99?9 | | 钢笔 8??8 | +-----------+ 2 rows in set (0.00 sec)mysql> select * from t_Ronaldo09 where instr(id ,'??'); +-----------+ | id | +-----------+ | 钢笔 8??8 | +-----------+ 1 row in set (0.00 sec)mysql>
mysql> select * from t_Ronaldo09 where id regexp '[\\]'; +-------------+ | id | +-------------+ | 钢笔 789 \a | | 钢笔 45\\6 | +-------------+ 2 rows in set (0.00 sec)mysql> mysql> select * from t_Ronaldo09 where id regexp '[\\][\\]'; +-------------+ | id | +-------------+ | 钢笔 45\\6 | +-------------+ 1 row in set (0.00 sec)mysql> mysql> select * from t_Ronaldo09 where id regexp '[?]'; +-----------+ | id | +-----------+ | 钢笔 99?9 | | 钢笔 8??8 | +-----------+ 2 rows in set (0.00 sec)mysql> mysql> select * from t_Ronaldo09 where id regexp '[?][?]'; +-----------+ | id | +-----------+ | 钢笔 8??8 | +-----------+ 1 row in set (0.00 sec)mysql>
mysql> select * from t_Ronaldo09 where id like '%\\\\%'; +-------------+ | id | +-------------+ | 钢笔 789 \a | | 钢笔 45\\6 | +-------------+ 2 rows in set (0.00 sec)mysql> select * from t_Ronaldo09 where id like '%\\\\\\\\ +-------------+ | id | +-------------+ | 钢笔 45\\6 | +-------------+ 1 row in set (0.00 sec)mysql> select * from t_Ronaldo09 where id like '%?%'; +-----------+ | id | +-----------+ | 钢笔 99?9 | | 钢笔 8??8 | +-----------+ 2 rows in set (0.02 sec)mysql> select * from t_Ronaldo09 where id like '%??%'; +-----------+ | id | +-----------+ | 钢笔 8??8 | +-----------+ 1 row in set (0.00 sec)mysql>
经过自己的反复试验,对于连续的转义字符\\.... 得到如下结论 要匹配的连续\个数 %\\\..%模式匹配中\的个数 1 3 2 7 3 11 数学归纳猜想 n*3+n-1=4n-1 验证 4 15 5 19mysql> SELECT ms from tmp where ms like '%\\\%'; +-----------------------+ | ms | +-----------------------+ | 单面刀片 飞鹰 20片\盒 | | 钢笔 1\2\\3 \\ | | 钢笔 45\\6 | | 钢笔 78\9 \ | | 钢笔 77\\\7 | | 钢笔 66\\\\6 | | 钢笔 55\\\\\5 | +-----------------------+ 7 rows in set (0.00 sec)mysql> SELECT ms from tmp where ms like '%\\\\\\\%'; +----------------+ | ms | +----------------+ | 钢笔 1\2\\3 \\ | | 钢笔 45\\6 | | 钢笔 77\\\7 | | 钢笔 66\\\\6 | | 钢笔 55\\\\\5 | +----------------+ 5 rows in set (0.00 sec) mysql> SELECT ms from tmp where ms like '%\\\\\\\\\\\%'; +---------------+ | ms | +---------------+ | 钢笔 77\\\7 | | 钢笔 66\\\\6 | | 钢笔 55\\\\\5 | +---------------+ 3 rows in set (0.00 sec) mysql> SELECT ms from tmp where ms like '%\\\\\\\\\\\\\\\%'; +---------------+ | ms | +---------------+ | 钢笔 66\\\\6 | | 钢笔 55\\\\\5 | +---------------+ 2 rows in set (0.00 sec) mysql> SELECT ms from tmp where ms like '%\\\\\\\\\\\\\\\\\\\%'; +---------------+ | ms | +---------------+ | 钢笔 55\\\\\5 | +---------------+ 1 row in set (0.00 sec)问题已经解决,但是不知道为什么,希望高人指点,十分感谢
十分感谢版主,用regexp确实方便很多,我查到手册上有这么一句 注释:由于在字符串中, MySQL使用 C 转义语法 (例如, 用‘\n’来代表换行字符 ),在REGEXP字符串中必须将用到的‘\’ 双写。这下豁然了,但是对于 like 用 4n-1 还是很想知道究竟,呵呵
mysql> select * from t_Ronaldo09 where id like '%\\\\\\\\%'; +-------------+ | id | +-------------+ | 钢笔 45\\6 | +-------------+ 1 row in set (0.00 sec)以这个来分析 id like '%\\\\\\\\%';首先你的命令行会把这个 '%\\\\\\\\%'; 解释成 '%\\\\%'; 提交到服务器端。然后在LIKE运算的的时候 '%\\\\%';会变成 '%\\%';
+-------------+
| col |
+-------------+
| 钢笔 789 \a |
| 钢笔 45\\6 |
+-------------+
2 rows in set (0.00 sec)mysql> select * from tb where col like '%?\%' escape '?';
+-------------+
| col |
+-------------+
| 钢笔 789 \a |
| 钢笔 45\\6 |
+-------------+
2 rows in set (0.00 sec)
+-----------+
| ms |
+-----------+
| 钢笔 99?9 |
| 钢笔 8??8 |
+-----------+匹配一个通配符?
mysql> select ms from tmp where ms like '%??%' escape '?';
+-----------+
| ms |
+-----------+
| 钢笔 99?9 |
| 钢笔 8??8 |
+-----------+
2 rows in set (0.00 sec)匹配俩个??
mysql> select ms from tmp where ms like '%????%' escape '?';
+-----------+
| ms |
+-----------+
| 钢笔 8??8 |
+-----------+
1 row in set (0.00 sec)自定义转义符,就是我想要的结果
但是自定义转义符对\,结果又不是想象的那样 ?\?\
-> ;
+-------------+
| id |
+-------------+
| 钢笔 789 \a |
| 钢笔 45\\6 |
| 钢笔 99?9 |
| 钢笔 8??8 |
+-------------+
4 rows in set (0.02 sec)mysql> select * from t_Ronaldo09 where instr(id ,'\\');
+-------------+
| id |
+-------------+
| 钢笔 789 \a |
| 钢笔 45\\6 |
+-------------+
2 rows in set (0.00 sec)mysql> select * from t_Ronaldo09 where instr(id ,'\\\\');
+-------------+
| id |
+-------------+
| 钢笔 45\\6 |
+-------------+
1 row in set (0.00 sec)mysql> select * from t_Ronaldo09 where instr(id ,'?');
+-----------+
| id |
+-----------+
| 钢笔 99?9 |
| 钢笔 8??8 |
+-----------+
2 rows in set (0.00 sec)mysql> select * from t_Ronaldo09 where instr(id ,'??');
+-----------+
| id |
+-----------+
| 钢笔 8??8 |
+-----------+
1 row in set (0.00 sec)mysql>
+-------------+
| id |
+-------------+
| 钢笔 789 \a |
| 钢笔 45\\6 |
+-------------+
2 rows in set (0.00 sec)mysql>
mysql> select * from t_Ronaldo09 where id regexp '[\\][\\]';
+-------------+
| id |
+-------------+
| 钢笔 45\\6 |
+-------------+
1 row in set (0.00 sec)mysql>
mysql> select * from t_Ronaldo09 where id regexp '[?]';
+-----------+
| id |
+-----------+
| 钢笔 99?9 |
| 钢笔 8??8 |
+-----------+
2 rows in set (0.00 sec)mysql>
mysql> select * from t_Ronaldo09 where id regexp '[?][?]';
+-----------+
| id |
+-----------+
| 钢笔 8??8 |
+-----------+
1 row in set (0.00 sec)mysql>
+-------------+
| id |
+-------------+
| 钢笔 789 \a |
| 钢笔 45\\6 |
+-------------+
2 rows in set (0.00 sec)mysql> select * from t_Ronaldo09 where id like '%\\\\\\\\
+-------------+
| id |
+-------------+
| 钢笔 45\\6 |
+-------------+
1 row in set (0.00 sec)mysql> select * from t_Ronaldo09 where id like '%?%';
+-----------+
| id |
+-----------+
| 钢笔 99?9 |
| 钢笔 8??8 |
+-----------+
2 rows in set (0.02 sec)mysql> select * from t_Ronaldo09 where id like '%??%';
+-----------+
| id |
+-----------+
| 钢笔 8??8 |
+-----------+
1 row in set (0.00 sec)mysql>
得到如下结论
要匹配的连续\个数 %\\\..%模式匹配中\的个数
1 3
2 7
3 11
数学归纳猜想 n*3+n-1=4n-1
验证
4 15
5 19mysql> SELECT ms from tmp where ms like '%\\\%';
+-----------------------+
| ms |
+-----------------------+
| 单面刀片 飞鹰 20片\盒 |
| 钢笔 1\2\\3 \\ |
| 钢笔 45\\6 |
| 钢笔 78\9 \ |
| 钢笔 77\\\7 |
| 钢笔 66\\\\6 |
| 钢笔 55\\\\\5 |
+-----------------------+
7 rows in set (0.00 sec)mysql> SELECT ms from tmp where ms like '%\\\\\\\%';
+----------------+
| ms |
+----------------+
| 钢笔 1\2\\3 \\ |
| 钢笔 45\\6 |
| 钢笔 77\\\7 |
| 钢笔 66\\\\6 |
| 钢笔 55\\\\\5 |
+----------------+
5 rows in set (0.00 sec)
mysql> SELECT ms from tmp where ms like '%\\\\\\\\\\\%';
+---------------+
| ms |
+---------------+
| 钢笔 77\\\7 |
| 钢笔 66\\\\6 |
| 钢笔 55\\\\\5 |
+---------------+
3 rows in set (0.00 sec)
mysql> SELECT ms from tmp where ms like '%\\\\\\\\\\\\\\\%';
+---------------+
| ms |
+---------------+
| 钢笔 66\\\\6 |
| 钢笔 55\\\\\5 |
+---------------+
2 rows in set (0.00 sec)
mysql> SELECT ms from tmp where ms like '%\\\\\\\\\\\\\\\\\\\%';
+---------------+
| ms |
+---------------+
| 钢笔 55\\\\\5 |
+---------------+
1 row in set (0.00 sec)问题已经解决,但是不知道为什么,希望高人指点,十分感谢
十分感谢版主,用regexp确实方便很多,我查到手册上有这么一句
注释:由于在字符串中, MySQL使用 C 转义语法 (例如, 用‘\n’来代表换行字符 ),在REGEXP字符串中必须将用到的‘\’ 双写。这下豁然了,但是对于 like 用 4n-1 还是很想知道究竟,呵呵
+-------------+
| id |
+-------------+
| 钢笔 45\\6 |
+-------------+
1 row in set (0.00 sec)以这个来分析 id like '%\\\\\\\\%';首先你的命令行会把这个 '%\\\\\\\\%'; 解释成 '%\\\\%'; 提交到服务器端。然后在LIKE运算的的时候 '%\\\\%';会变成 '%\\%';
版主V5,找到比较合理的解释,而且我试了下,4n-1,修正为 4n,可以得到同样的结果,而且又能解释的通,十分感谢版主真是强大呵呵,谢谢了