mysql变量中的多个值 [英] multiple values in mysql variable

问题描述

当变量中存储单个值时，以下各项将按预期工作.

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?

推荐答案

据我所知，您不能在MySQL用户定义的变量中存储多个值.您要做的是创建一个包含以下内容的字符串:

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:

'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?

这篇关于mysql变量中的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！