最近总感觉sql语句不对劲,所以就看了一些官方文档发现了一些以前没有注意的函数:感觉在查询的时候可以用得上,毕竟是内置函数,用起来效率应该会好一些的:
mysql>SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
如:SELECT FIND_IN_SET(a.roleId,r.roleName), a.* from t_admin a ,t_role r where a.roleId = r.roleId AND FIND_IN_SET(a.roleId,r.roleId);
代替in、not in
mysql>SELECT INSTR('foobarbar', 'bar');
-> 4
如:select a.* FROM t_admin a where INSTR(a.account,'发达');
可以用于模糊查询 代替like 提高效率
LOCATE(
, substr
,str
)LOCATE(
substr
,str
,pos
)
mysql>SELECT LOCATE('xbar', 'foobar');
-> 0
mysql>SELECT LOCATE('bar', 'foobarbar', 5);
-> 7
select a.* FROM t_admin a where LOCATE('发达',a.account);
可以用于模糊查询 代替like 提高效率
select
substring_index(email,'@',-1) as domain,
count(distinct email) as count,
count(distinct email)
/ (select count(distinct email) from t_user where email like '%@%')
* 100 as percent
from t_user
where email like '%@%'
group by 1
order by 3 ASC
limit 10;
select
substring_index(email,'@',-1) as domain,
count(distinct email) as count,
count(distinct email)
/ (select count(distinct email) from t_user where email like '%@%')
* 100 as percent
from t_user
where email like '%@%'
group by 1
order by 3 ASC
limit 10;