mysql find_in_set
如果我的表里面,有一个字段是tag,它是由一些数字或字符串用 "," 组合起来的,现在我需要统计一下这个表里面tag字段含有关键字key的数目
比如我现在tag是一些数字用 "," 组合起来的,我现在想查询tag包含数字1的行有多少条,一开始的查询语句是这样的:
select count(*) from comment where tag like '%1%';
对于tag的范围在0-9不会出现什么问题,但是呢,如果tag包含11、12、13等,这个统计就会多出几条
想过要优化下查询语句,改成:
select count(*) from comment wheretag like ',1%' or '%1,';
但是这个这样好像也会统计出错
后面看到了mysql的find_in_set函数,可以完美的解决这个问题
先看下官方文档:
Returns a value in the range of 1 to N
if the string str
is in the string list strlist
consisting of N
substrings. A string list is a string composed of substrings separated by ,
characters. If the first argument is a constant string and the second is a column of type SET
, the FIND_IN_SET()
function is optimized to use bit arithmetic. Returns 0
if str
is not in strlist
or if strlist
is the empty string. Returns NULL
if either argument is NULL
. This function does not work properly if the first argument contains a comma (,
) character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
使用find_in_set效率相对like要高不少,而且支持字符串的匹配,附上查询语句:
select count(*) from comment where find_in_set('1',tag);
select count(*) from comment where find_in_set('abc',tag);
拓展:
如果需要统计多种类型的数目,一次一次查太费劲了,使用sum跟if一次性查出符合要求的数据
select sum(if(find_in_set('1',tag),1,0)) as 'a', sum(if(find_in_set('2',tag),1,0)) as 'b', sum(if(find_in_set('3',tag),1,0)) as 'c', sum(if(find_in_set('4',tag),1,0)) as 'd', sum(if(find_in_set('5',tag),1,0)) as 'e', sum(if(find_in_set('6',tag),1,0)) as 'f', sum(if(find_in_set('7',tag),1,0)) as 'g', sum(if(find_in_set('8',tag),1,0)) as 'h', sum(if(find_in_set('9',tag),1,0)) as 'i', sum(if(find_in_set('10',tag),1,0)) as 'j' from comment;
->