where_in和find_in_set之间的区别 [英] difference between where_in and find_in_set
问题描述
我正在处理一个联接查询,其中我必须从包含逗号分隔值的列中获取数据.例如allowed_activity
contains 1,2,3,4,5,6
这是允许的activity_id
.
I am working on a join query in which I have to get the data from a column which contain the comma separated values.like allowed_activity
contain 1,2,3,4,5,6
this the activity_id
which is allowed.
因此,在查询中,我正在检查是否允许当前活动,为此,我使用了where_in
并在where
条件下尝试了find_in_set
.
So In the query, I am checking that current activity is allowed or not and for that, I have used where_in
and also tried find_in_set
in where
condition.
这是查询的一部分:
$this->db->where_in('a.allowed_activity',$activity_id);
//And With FIND_IN_SET
$this->db->where("FIND_IN_SET($activity_id, a.allowed_activity) !=",0);
问题或困惑
当我使用where_in
时,它不会给我结果.但是,如果使用FIND_IN_SET
,它将返回所需的结果.我以为where_in
可以完成任务,但没有.
Problem or Confusion
When I use where_in
it doesn't give me the result. But if I use FIND_IN_SET
then it will return the desired result. I thought that where_in
can do the task but it didn't.
只是想知道为什么
find_in_set
的工作方式不同于where_in
由于这两个功能的工作原理与查找数据的方法相同,所以用逗号分隔列表中.
Just Want to know why
find_in_set
working differently thanwhere_in
Because of both the function work same as finding the data is in comma separated list.
这是完整的查询:
SELECT mycolumns
FROM `table1` as `ea`
LEFT OUTER JOIN `table2` as `a` ON `ea`.`package_id` = `a`.`id`
LEFT OUTER JOIN `table3` as `e` ON `ea`.`employer_id` = `e`.`id`
WHERE `ea`.`card_id` > 0
AND FIND_IN_SET(6, a.allowed_activity) !=0
AND `ea`.`id` = '6'
ORDER BY `ea`.`creation_date` DESC
我正在使用Codeigniter Active记录.
I am using Codeigniter Active record.
推荐答案
WHERE IN
要求在查询中按字面值指定一组值,而不是包含逗号分隔字符串的单个值.如果您写:
WHERE IN
requires the set of values to be specified literally in the query, not as a single value containing a comma-delimited string. If you write:
WHERE 6 IN (a.allowed_activity)
它将a.allowed_activity
视为单个值,并将其与6
进行比较,而不是将其作为多个要搜索的值的集合.
it will treat a.allowed_activity
as just a single value, and compare it with 6
, not as a set of multiple values to search.
FIND_IN_SET
在逗号分隔的字符串中搜索值.
FIND_IN_SET
searches a comma-delimited string for the value.
另一种查看方式是IN
是结合OR
的一堆=
测试的快捷方式:
Another way to view it is that IN
is a shortcut for a bunch of =
tests combined with OR
:
WHERE x IN (a, b, c, d)
是
WHERE x = a OR x = b OR x = c OR x = d
以这种方式重写它时,您可以清楚地知道为什么它不适用于包含逗号分隔的字符串的列.它只是翻译
When you rewrite it like this, you can see clearly why it won't work with a column containing a comma-delimited string. It simply translates
WHERE 6 IN (a.allowed_activity)
收件人:
WHERE 6 = a.allowed_activity
这篇关于where_in和find_in_set之间的区别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!