英文:
what is different between using Variable and not in Mariadb?
问题
我只想得到一些提示。是否有类型问题？
问题案例。
SET @ids = '4094,8562,11144,3017,5815,11121,1957,4095,8563,11145,3018,5816,8527,11122,1959,4096,8564,3020,5817,8528,11123,1961,4097,8571,3021,6020,8535,11128,1962,5181,8572,3581,6021';
这个@ids的值实际上是通过GROUP_CONCAT()从子查询中收集的；
SELECT
ifnull(sum(case when a.student IS NOT NULL then total END), 0)
from
tb_class a
WHERE
a.id IN (@ids)
and a.date >= '2023-02-01' AND a.DATE <= '2023-02-02'
==> 0
正确的情况2。
SELECT
ifnull(sum(case when a.student IS NOT NULL then total END), 0)
from
tb_class a
WHERE
a.id IN (4094,8562,11144,3017,5815,11121,1957,4095,8563,11145,3018,5816,8527,11122,1959,4096,8564,3020,5817,8528,11123,1961,4097,8571,3021,6020,8535,11128,1962,5181,8572,3581,6021)
and a.date >= '2023-02-01' AND a.DATE <= '2023-02-02'
==> 54
英文:
I just want to get some hint. Is there a type issue?
issue case.
SET @ids = '4094,8562,11144,3017,5815,11121,1957,4095,8563,11145,3018,5816,8527,11122,1959,4096,8564,3020,5817,8528,11123,1961,4097,8571,3021,6020,8535,11128,1962,5181,8572,3581,6021';
this @ids value is actually collected by GROUP_CONCAT() from the subquery;
SELECT
ifnull(sum(case when a.student IS NOT NULL then total END), 0)
from
tb_class a
WHERE
a.id IN (@ids)
and a.date >= '2023-02-01' AND a.DATE <= '2023-02-02'
==> 0
correct case2.
SELECT
ifnull(sum(case when a.student IS NOT NULL then total END), 0)
from
tb_class a
WHERE
a.id IN (4094,8562,11144,3017,5815,11121,1957,4095,8563,11145,3018,5816,8527,11122,1959,4096,8564,3020,5817,8528,11123,1961,4097,8571,3021,6020,8535,11128,1962,5181,8572,3581,6021)
and a.date >= '2023-02-01' AND a.DATE <= '2023-02-02'
==> 54
答案1
得分: 1
我从谷歌搜索得到了答案。使用函数FIND_IN_SET()
SELECT
IFNULL(SUM(CASE WHEN a.student IS NOT NULL THEN total END), 0)
FROM
tb_class a
WHERE
FIND_IN_SET(a.id, @ids)
AND a.date >= '2023-02-01' AND a.date <= '2023-02-02'
英文:
I got answer from googling. use function FIND_IN_SET()
SELECT
ifnull(sum(case when a.student IS NOT NULL then total END), 0)
from
tb_class a
WHERE
FIND_IN_SET(a.id, @ids)
and a.date >= '2023-02-01' AND a.DATE <= '2023-02-02'
答案2
得分: 1
变量只能存储单个值，而不是列表。您的@ids只是一个字符串，其中包含逗号分隔的数字列表。IN操作符只能与显式列表进行比较；您所做的与a.id = @ids没有任何区别（如果id是数值类型，该语句实际上会为列表中的第一个数字返回true，并显示警告，因为字符串将被转换为数字，尾部的非数值部分将被丢弃）。
有时，您确实希望使用包含id列表的字符串，例如，如果您有一个查询要读取许多行，您希望使用这些行生成要更新的小型id列表，而不希望更新锁定所有已读取的行。那么您可以使用动态SQL：
SET @ids = '4094,8562,...';
SET @sql = concat('select * from a where a.id in (', @ids, ')');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
或者，在Mariadb中自10.2版起，您可以使用EXECUTE IMMEDIATE：
EXECUTE IMMEDIATE concat('select * from a where a.id in (', @ids, ')');
另一种选择是使用FIND_IN_SET，如另一个答案所示，但是这种方法不会使用索引查找id，可能效率较低。
英文:
Variables store single values, not lists. Your @ids is just a string that happens to have a comma separated list of numbers. The IN operator only compares against an explicit list; what you are doing is no different than a.id = @ids (which will actually be true, with a warning, for the first number in the list if id is a numeric type, since the string will be converted to a number and the trailing non-numeric portion discarded).
Sometimes you do want to work with a string containing a list of ids such as this, for instance if you have a query that reads many rows that you want to use to produce a small list of ids to update, without the update locking those all the rows read. Then you can use dynamic sql:
SET @ids = '4094,8562,...';
SET @sql = concat('select * from a where a.id in (',@ids,')');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
Or, in mariadb since 10.2,
EXECUTE IMMEDIATE concat('select * from a where a.id in (',@ids,')');
Another alternative is to use FIND_IN_SET, as shown in another answer, but that will not use an index to look up ids, so may be inefficient.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论