MySql模糊查询like通配符使用详细介绍

时间:2022-03-24 09:50:23

转自:http://blog.csdn.net/my2010sam/article/details/19109235

MySql的like语句中的通配符:百分号、下划线和escape

%:表示任意个或多个字符。可匹配任意类型和长度的字符。
Sql代码

select * from user where username like '%huxiao';   

select * from user where username like 'huxiao%';

select * from user where username like '%huxiao%';

另外,如果需要找出u_name中既有“三”又有“猫”的记录,请使用and条件
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’
若使用 SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’
虽然能搜索出“三脚猫”,但不能搜索出符合条件的“张猫三”。

_:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句:(可以代表一个中文字符)

Sql代码

select * from user where username like '_';   

select * from user where username like 'huxia_';

select * from user where username like 'h_xiao';

如果我就真的要查%或者,怎么办呢?使用escape,转义字符后面的%或就不作为通配符了,注意前面没有转义字符的%和_仍然起通配符作用
Sql代码

select username from gg_user where username like '%xiao/_%' escape '/';   

select username from gg_user where username like '%xiao/%%' escape '/';
select Id,user,from_unixtime(updateTime),TagList,weight,state from list where weight>= '15' and state != 1 and TagList like '%#SHOW#%' order by Id asc;