具有多个值的Mysql变量
具有多个值的Mysql变量
提问于 2015-10-15 17:12:04
当变量中存储了单个值时，以下代码按预期工作：
Set @var = 121;
select * from table where id = @var;如何设置具有多个值的变量，然后在查询中使用它。我试过了，但它不起作用：
set @var = (
117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191
)
select * from table where id = @var;回答 3
Stack Overflow用户
发布于 2015-10-15 17:48:43
set @var = '
117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191'
SELECT * FROM table WHERE FIND_IN_SET(id,@var);谢谢，伙计
Stack Overflow用户
发布于 2015-10-15 17:54:59
您可以通过将@var设置为逗号分隔的字符串并使用like来获取数据。这并不完美，但却是可行的，您必须注意@var中的值。不应该有任何像space这样的无效字符
Mysql> set @var = '117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,117,120,121,122,143,151,175,233,12';
Query OK, 0 rows affected (0.00 sec)
Hitesh> select * from test where ((@var like concat(id,',%')) or (@var like concat('%,',id)) or (@var like concat('%,',id,',%')) or (@var like id));
+----+------+-------+
| ID | NAME | VALUE |
+----+------+-------+
| 12 | Nee | NULL |
+----+------+-------+
1 row in set (0.00 sec)您也可以使用LOCATE函数来代替like。MySQL LOCATE()返回字符串中首次出现的字符串的位置。这两个字符串都作为参数传递。可选参数可用于指定将从字符串(即，要搜索的字符串)的哪个位置开始搜索。如果未提及此位置，则搜索从头开始。
语法LOCATE(substr,str)
mysql> SELECT LOCATE('st','myteststring');
+-----------------------------+
| LOCATE('st','myteststring') |
+-----------------------------+
| 5 |
+-----------------------------+FIND_IN_SET()非常适合您的情况。It must have string值为comma separated。
Stack Overflow用户
发布于 2015-10-15 17:32:08
你应该使用IN
select * from table where id IN ( @var );页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33144377
复制相关文章
相似问题
腾讯云开发者
