mysql查询时关于结果集中存在_~问题怎么解决
select * from userInfo where uname like ‘%TT%’ 查询出来的结果是ok的
但是如果是select * from userInfo where uname like ‘%T_T%’ 结果中就有问题，包括 like ‘%san~%’ 和 like ‘%gren%’
userInfo表中 uname 的值有 TTai t_t 0T_T0 san~qiang sanqiang gerenyouxi grenyouxi
~和\，转义都是用斜杠
通配符 描述
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]
或者
[!charlist]
不在字符列中的任何单一字符
~ 应该不需要，你直接查。
改成 \ like ‘%gren\%’
-
SQL code
mysql> create table test(a varchar(30));
Query OK, 0 rows affected (0.34 sec)
mysql> insert into test values('TTai'), ('t_t'), ('0T_T0'), ('san~qiang'), ('sanqiang'), ('gerenyouxi'), ('gren\youxi');
Query OK, 7 rows affected (0.08 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------------+
| a |
+------------+
| TTai |
| t_t |
| 0T_T0 |
| san~qiang |
| sanqiang |
| gerenyouxi |
| grenyouxi |
+------------+
7 rows in set (0.02 sec)
mysql> select * from test where a like '%T_T%';
+-------+
| a |
+-------+
| t_t |
| 0T_T0 |
+-------+
2 rows in set (0.00 sec)
mysql> select * from test where a like '%san~%';
+-----------+
| a |
+-----------+
| san~qiang |
+-----------+
1 row in set (0.00 sec)
