mysql 变量中的多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3156481/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
multiple values in mysql variable
提问by shantanuo
The following works as expected when there is a single value stored in a variable.
当变量中存储单个值时，以下内容按预期工作。
SET @a := "20100630";
SELECT * FROM wordbase WHERE verified = @a;
But it does not work when there are multiple values stored in a variable.
但是当变量中存储了多个值时，它不起作用。
SET @a := "'20100630', '20100701' ";
SELECT * FROM wordbase WHERE verified in (@a);
Do I need to use prepared statements for this?
我需要为此使用准备好的语句吗？
采纳答案by Mike
You cannot (as far as I am aware) store multiple values in a MySQL user defined variable. What you have done is create a string which contains:
您不能（据我所知）在 MySQL 用户定义的变量中存储多个值。您所做的是创建一个包含以下内容的字符串：
'20100630', '20100701'
That is not two separate values, but a single string value, just as this is a single string value:
这不是两个单独的值，而是单个字符串值，就像这是单个字符串值一样：
SET @a := "It's a single string, and that's the problem";
You need to use two separate variables, or prepare a statement, like this:
您需要使用两个单独的变量，或者准备一个语句，如下所示：
SET @a := "20100630";
SET @b := "20100701";
SET @sql = CONCAT(
'SELECT * FROM wordbase WHERE verified IN (',
@a,
',',
@b,
')'
);
SELECT @sql;
+--------------------------------------------------------------+
| @sql |
+--------------------------------------------------------------+
| SELECT * FROM wordbase WHERE verified IN (20100630,20100701) |
+--------------------------------------------------------------+
PREPARE stmt FROM @sql;
EXECUTE stmt;
But that's kinda messy. Why do you need to use variables?
但这有点乱。为什么需要使用变量？
回答by grinderX19
There's good solution described here: https://stackoverflow.com/a/11957706/1523961
这里描述了很好的解决方案：https: //stackoverflow.com/a/11957706/1523961
So can use something like this:
所以可以使用这样的东西：
SET @a := '20100630,20100701';
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);
Also, if you're selecting the ids for @afrom another table, you can come up with following:
此外，如果您@a从另一个表中选择 id ，您可以想出以下内容：
SET @a := (SELECT GROUP_CONCAT(id) FROM someTable where yourBooleanExpressionHere);
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);
回答by Beshoy Girgis
Using GROUP_CONCATand GROUP BYone could pull all values ( i.e. an id ) into a variable like so:
使用GROUP_CONCATandGROUP BY可以将所有值（即 id ）放入一个变量中，如下所示：
SET @var := (SELECT GROUP_CONCAT(id) FROM `table` WHERE `verified` = @verified GROUP BY verified);
回答by shantanuo
Something like this should work. Is it ok to use prepared statements to create temporary tables like this?
像这样的事情应该有效。可以使用准备好的语句来创建这样的临时表吗？
SET @a := "'20100630', '20100701'";
SET @sql = CONCAT('create temporary table pn1 SELECT * FROM wordbase WHERE verified IN (', @a, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
select * from pn1;
回答by Marcello Telles
SELECT GROUP_CONCAT(field_table1 SEPARATOR ',') FROM table1 into @var;
then
然后
SELECT * FROM table2 WHERE field_table2 in(@var);
works fine for me
对我来说很好用
