blocks|key|2860591|text|您可以使用WHILE循环在MySQL中实现这一点：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2860592|SET+@myArrayOfValue+=+'2,5,2,23,6,';

WHILE+(LOCATE(',',+@myArrayOfValue)+>+0)
DO
++++SET+@value+=+ELT(1,+@myArrayOfValue);
++++SET+@myArrayOfValue=+SUBSTRING(@myArrayOfValue,+LOCATE(',',@myArrayOfValue)+%2B+1);

++++INSERT+INTO+`EXEMPLE`+VALUES(@value,+'hello');
END+WHILE;|code-block|syntax|javascript|2860593|编辑:或者，您可以使用UNION+ALL进行编辑|2860594|INSERT+INTO+`EXEMPLE`
(
+`value`,+`message`
)
(
+SELECT+2+AS+`value`,+'hello'+AS+`message`
+UNION+ALL
+SELECT+5+AS+`value`,+'hello'+AS+`message`
+UNION+ALL
+SELECT+2+AS+`value`,+'hello'+AS+`message`
+UNION+ALL
+...
);|2860595|entityMap^0|5|5|0|0|B|9|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

You can achieve this in MySQL using <code>WHILE</code> loop:

<pre><code>SET @myArrayOfValue = '2,5,2,23,6,';

WHILE (LOCATE(',', @myArrayOfValue) &gt; 0)
DO
 SET @value = ELT(1, @myArrayOfValue);
 SET @myArrayOfValue= SUBSTRING(@myArrayOfValue, LOCATE(',',@myArrayOfValue) + 1);

 INSERT INTO `EXEMPLE` VALUES(@value, 'hello');
END WHILE;
</code></pre>

EDIT:
Alternatively you can do it using <code>UNION ALL</code>:

<pre><code>INSERT INTO `EXEMPLE`
(
 `value`, `message`
)
(
 SELECT 2 AS `value`, 'hello' AS `message`
 UNION ALL
 SELECT 5 AS `value`, 'hello' AS `message`
 UNION ALL
 SELECT 2 AS `value`, 'hello' AS `message`
 UNION ALL
 ...
);
</code></pre>

blocks|key|3074029|text|我不知道数组，但是有一种方法可以将逗号分隔的列表存储在普通的VARCHAR列中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074030|当您需要在该列表中查找内容时，可以使用FIND_IN_SET()函数。|offset|length|3074031|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set^0|0|J|D|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Dont know about the arrays, but there is a way to store comma-separated lists in normal VARCHAR column.

And when you need to find something in that list you can use the <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set" rel="noreferrer">FIND_IN_SET()</a> function.

blocks|key|2860660|text|我一直在使用临时表，而不是数组变量。这不是最好的解决方案，但它是有效的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2860661|请注意，您不需要正式定义它们的字段，只需使用SELECT创建它们：|2860662|DROP+TEMPORARY+TABLE+IF+EXISTS+my_temp_table;
CREATE+TEMPORARY+TABLE+my_temp_table
++++SELECT+first_name+FROM+people+WHERE+last_name+=+'Smith';|code-block|syntax|javascript|2860663|(另请参阅Create+temporary+table+from+select+statement+without+using+Create+Table。)|offset|length|2860664|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/q/5859391/1593077^0|0|0|0|5|1Z|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Well, I've been using temporary tables instead of array variables. Not the greatest solution, but it works.
Note that you don't need to formally define their fields, just create them using a SELECT:
<pre><code>DROP TEMPORARY TABLE IF EXISTS my_temp_table;
CREATE TEMPORARY TABLE my_temp_table
 SELECT first_name FROM people WHERE last_name = 'Smith';
</code></pre>
(See also <a href="https://stackoverflow.com/q/5859391/1593077">Create temporary table from select statement without using Create Table</a>.)

blocks|key|217919|text|您是否尝试过使用PHP的serialize()？这允许您将变量数组的内容存储在PHP可以理解的字符串中，并且对数据库是安全的(假设您首先对其进行了转义)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|217920|$array+=+array(
++++1+=>+'some+data',
++++2+=>+'some+more'
);

//Assuming+you're+already+connected+to+the+database
$sql+=+sprintf("INSERT+INTO+`yourTable`+(`rowID`,+`rowContent`)+VALUES+(NULL,+'%25s')"
+++++,++serialize(mysql_real_escape_string($array,+$dbConnection)));
mysql_query($sql,+$dbConnection)+or+die(mysql_error());|code-block|syntax|javascript|217921|您也可以在不使用编号数组的情况下执行完全相同的操作|217922|$array2+=+array(
++++'something'+=>+'something+else'
);|217923|或|217924|$array3+=+array(
++++'somethingNew'
);|217925|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Have you tried using PHP's serialize()?
That allows you to store the contents of a variable's array in a string PHP understands and is safe for the database (assuming you've escaped it first).

<pre><code>$array = array(
 1 =&gt; 'some data',
 2 =&gt; 'some more'
);

//Assuming you're already connected to the database
$sql = sprintf("INSERT INTO `yourTable` (`rowID`, `rowContent`) VALUES (NULL, '%s')"
 , serialize(mysql_real_escape_string($array, $dbConnection)));
mysql_query($sql, $dbConnection) or die(mysql_error());
</code></pre>

You can also do the exact same without a numbered array

<pre><code>$array2 = array(
 'something' =&gt; 'something else'
);
</code></pre>

or

<pre><code>$array3 = array(
 'somethingNew'
);
</code></pre>

blocks|key|2860723|text|如果需要关联数组，可以创建一个包含列(键、值)的临时内存表。在mysql中，拥有内存表是最接近于拥有数组的东西|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2860724|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Maybe create a temporary memory table with columns (key, value) if you want associative arrays. Having a memory table is the closest thing to having arrays in mysql

blocks|key|215922|text|这对值列表很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215923|SET+@myArrayOfValue+=+'2,5,2,23,6,';

WHILE+(LOCATE(',',+@myArrayOfValue)+>+0)
DO
SET+@value+=+ELT(1,+@myArrayOfValue);
++++SET+@STR+=+SUBSTRING(@myArrayOfValue,+1,+LOCATE(',',@myArrayOfValue)-1);
++++SET+@myArrayOfValue+=+SUBSTRING(@myArrayOfValue,+LOCATE(',',+@myArrayOfValue)+%2B+1);

++++INSERT+INTO+`Demo`+VALUES(@STR,+'hello');
END+WHILE;|code-block|syntax|javascript|215924|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This works fine for list of values:

<pre><code>SET @myArrayOfValue = '2,5,2,23,6,';

WHILE (LOCATE(',', @myArrayOfValue) &gt; 0)
DO
SET @value = ELT(1, @myArrayOfValue);
 SET @STR = SUBSTRING(@myArrayOfValue, 1, LOCATE(',',@myArrayOfValue)-1);
 SET @myArrayOfValue = SUBSTRING(@myArrayOfValue, LOCATE(',', @myArrayOfValue) + 1);

 INSERT INTO `Demo` VALUES(@STR, 'hello');
END WHILE;
</code></pre>

blocks|key|217963|text|使用sets的两个版本都不适用于我(使用MySQL+5.5测试)。函数ELT()返回整个集合。考虑到WHILE语句只在过程上下文中可用，我将其添加到我的解决方案中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|217964|DROP+PROCEDURE+IF+EXISTS+__main__;

DELIMITER+$
CREATE+PROCEDURE+__main__()
BEGIN
++++SET+@myArrayOfValue+=+'2,5,2,23,6,';

++++WHILE+(LOCATE(',',+@myArrayOfValue)+>+0)
++++DO
++++++++SET+@value+=+LEFT(@myArrayOfValue,+LOCATE(',',@myArrayOfValue)+-+1);++++
++++++++SET+@myArrayOfValue+=+SUBSTRING(@myArrayOfValue,+LOCATE(',',@myArrayOfValue)+%2B+1);
++++END+WHILE;
END;
$
DELIMITER+;

CALL+__main__;|code-block|syntax|javascript|217965|老实说，我不认为这是一个好的做法。即使真的有必要，这也是很难读的，而且速度很慢。|217966|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Both versions using sets didn't work for me (tested with MySQL 5.5). The function ELT() returns the whole set. Considering the WHILE statement is only avaible in PROCEDURE context i added it to my solution:

<pre><code>DROP PROCEDURE IF EXISTS __main__;

DELIMITER $
CREATE PROCEDURE __main__()
BEGIN
 SET @myArrayOfValue = '2,5,2,23,6,';

 WHILE (LOCATE(',', @myArrayOfValue) &gt; 0)
 DO
 SET @value = LEFT(@myArrayOfValue, LOCATE(',',@myArrayOfValue) - 1); 
 SET @myArrayOfValue = SUBSTRING(@myArrayOfValue, LOCATE(',',@myArrayOfValue) + 1);
 END WHILE;
END;
$
DELIMITER ;

CALL __main__;
</code></pre>

To be honest, i don't think this is a good practice. Even if its realy necessary, this is barely readable and quite slow.

blocks|key|215723|text|我想我可以改进这个答案。试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215724|参数'Pranks‘是一个CSV。即。'1,2,3,4.....etc‘|215725|CREATE+PROCEDURE+AddRanks(
IN+Pranks+TEXT
)
BEGIN
++DECLARE+VCounter+INTEGER;
++DECLARE+VStringToAdd+VARCHAR(50);
++SET+VCounter+=+0;
++START+TRANSACTION;
++REPEAT
++++SET+VStringToAdd+=+(SELECT+TRIM(SUBSTRING_INDEX(Pranks,+',',+1)));
++++SET+Pranks+=+(SELECT+RIGHT(Pranks,+TRIM(LENGTH(Pranks)+-+LENGTH(SUBSTRING_INDEX(Pranks,+',',+1))-1)));
++++INSERT+INTO+tbl_rank_names(rank)
++++VALUES(VStringToAdd);
++++SET+VCounter+=+VCounter+%2B+1;
++UNTIL+(Pranks+=+'')
++END+REPEAT;
++SELECT+VCounter+AS+'Records+added';
++COMMIT;
END;|code-block|syntax|javascript|215726|这种方法使搜索到的CSV值字符串随着循环的每次迭代而逐渐变短，我相信这对优化会更好。|215727|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I Think I can improve on this answer. Try this:

The parameter 'Pranks' is a CSV. ie. '1,2,3,4.....etc'

<pre><code>CREATE PROCEDURE AddRanks(
IN Pranks TEXT
)
BEGIN
 DECLARE VCounter INTEGER;
 DECLARE VStringToAdd VARCHAR(50);
 SET VCounter = 0;
 START TRANSACTION;
 REPEAT
 SET VStringToAdd = (SELECT TRIM(SUBSTRING_INDEX(Pranks, ',', 1)));
 SET Pranks = (SELECT RIGHT(Pranks, TRIM(LENGTH(Pranks) - LENGTH(SUBSTRING_INDEX(Pranks, ',', 1))-1)));
 INSERT INTO tbl_rank_names(rank)
 VALUES(VStringToAdd);
 SET VCounter = VCounter + 1;
 UNTIL (Pranks = '')
 END REPEAT;
 SELECT VCounter AS 'Records added';
 COMMIT;
END;
</code></pre>

This method makes the searched string of CSV values progressively shorter with each iteration of the loop, which I believe would be better for optimization.

blocks|key|2860846|text|这就是我是如何做到的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2860847|首先，我创建了一个函数，用于检查以逗号分隔的值列表中是否存在Long/Integer/whatever值：|2860848|CREATE+DEFINER+=+'root'@'localhost'+FUNCTION+`is_id_in_ids`(
++++++++`strIDs`+VARCHAR(255),
++++++++`_id`+BIGINT
++++)
++++RETURNS+BIT(1)
++++NOT+DETERMINISTIC
++++CONTAINS+SQL
++++SQL+SECURITY+DEFINER
++++COMMENT+''
BEGIN

++DECLARE+strLen++++INT+DEFAULT+0;
++DECLARE+subStrLen+INT+DEFAULT+0;
++DECLARE+subs++++++VARCHAR(255);

++IF+strIDs+IS+NULL+THEN
++++SET+strIDs+=+'';
++END+IF;

++do_this:
++++LOOP
++++++SET+strLen+=+LENGTH(strIDs);
++++++SET+subs+=+SUBSTRING_INDEX(strIDs,+',',+1);

++++++if+(+CAST(subs+AS+UNSIGNED)+=+_id+)+THEN
++++++++--+founded
++++++++return(1);
++++++END+IF;

++++++SET+subStrLen+=+LENGTH(SUBSTRING_INDEX(strIDs,+',',+1));
++++++SET+strIDs+=+MID(strIDs,+subStrLen%2B2,+strLen);

++++++IF+strIDs+=+NULL+or+trim(strIds)+=+''+THEN
++++++++LEAVE+do_this;
++++++END+IF;

++END+LOOP+do_this;

+++--+not+founded
++return(0);

END;|code-block|syntax|javascript|2860849|因此，现在您可以在逗号分隔的ID列表中搜索ID，如下所示：|2860850|select+`is_id_in_ids`('1001,1002,1003',1002);|2860851|您可以在WHERE子句中使用此函数，如下所示：|2860852|SELECT+*+FROM+table1+WHERE+`is_id_in_ids`('1001,1002,1003',table1_id);|2860853|这是我发现的将"array“参数传递给过程的唯一方法。|2860854|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Here’s how I did it.

First, I created a function that checks whether a Long/Integer/whatever value is in a list of values separated by commas:

<pre><code>CREATE DEFINER = 'root'@'localhost' FUNCTION `is_id_in_ids`(
 `strIDs` VARCHAR(255),
 `_id` BIGINT
 )
 RETURNS BIT(1)
 NOT DETERMINISTIC
 CONTAINS SQL
 SQL SECURITY DEFINER
 COMMENT ''
BEGIN

 DECLARE strLen INT DEFAULT 0;
 DECLARE subStrLen INT DEFAULT 0;
 DECLARE subs VARCHAR(255);

 IF strIDs IS NULL THEN
 SET strIDs = '';
 END IF;

 do_this:
 LOOP
 SET strLen = LENGTH(strIDs);
 SET subs = SUBSTRING_INDEX(strIDs, ',', 1);

 if ( CAST(subs AS UNSIGNED) = _id ) THEN
 -- founded
 return(1);
 END IF;

 SET subStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
 SET strIDs = MID(strIDs, subStrLen+2, strLen);

 IF strIDs = NULL or trim(strIds) = '' THEN
 LEAVE do_this;
 END IF;

 END LOOP do_this;

 -- not founded
 return(0);

END;
</code></pre>

So now you can search for an ID in a comma-separated list of IDs, like this:

<pre><code>select `is_id_in_ids`('1001,1002,1003',1002);
</code></pre>

And you can use this function inside a WHERE clause, like this:

<pre><code>SELECT * FROM table1 WHERE `is_id_in_ids`('1001,1002,1003',table1_id);
</code></pre>

This was the only way I found to pass an "array" parameter to a PROCEDURE.

blocks|key|216013|text|DELIMITER+$$
CREATE+DEFINER=`mysqldb`@`%25`+PROCEDURE+`abc`()
BEGIN
++BEGIN+
++++set+@value+:='11,2,3,1,';+
++++WHILE+(LOCATE(',',+@value)+>+0)+DO
++++++SET+@V_DESIGNATION+=+SUBSTRING(@value,1,+LOCATE(',',@value)-1);+
++++++SET+@value+=+SUBSTRING(@value,+LOCATE(',',@value)+%2B+1);+
++++++select+@V_DESIGNATION;
++++END+WHILE;
++END;
END$$
DELIMITER+;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|216014|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DELIMITER $$
CREATE DEFINER=`mysqldb`@`%` PROCEDURE `abc`()
BEGIN
 BEGIN 
 set @value :='11,2,3,1,'; 
 WHILE (LOCATE(',', @value) &gt; 0) DO
 SET @V_DESIGNATION = SUBSTRING(@value,1, LOCATE(',',@value)-1); 
 SET @value = SUBSTRING(@value, LOCATE(',',@value) + 1); 
 select @V_DESIGNATION;
 END WHILE;
 END;
END$$
DELIMITER ;
</code></pre>

blocks|key|218010|text|在MYSQL+5.7.x以后的版本中，可以使用JSON类型来存储数组。您可以通过MYSQL按键获取数组的值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218011|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In MYSQL version after 5.7.x, you can use JSON type to store an array. You can get value of an array by a key via MYSQL.

blocks|key|2860926|text|尝试使用MySql的FIND_IN_SET()函数，例如|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2860927|SET+@c+=+'xxx,yyy,zzz';

SELECT+*+from+countries+
WHERE+FIND_IN_SET(countryname,@c);|code-block|syntax|javascript|2860928|注意:如果要传递带有CSV值的参数，则不必在StoredProcedure中设置变量。|2860929|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try using FIND_IN_SET() function of MySql
e.g. 

<pre><code>SET @c = 'xxx,yyy,zzz';

SELECT * from countries 
WHERE FIND_IN_SET(countryname,@c);
</code></pre>

Note: You don't have to SET variable in StoredProcedure if you are passing parameter with CSV values.

blocks|key|218036|text|灵感来自于函数ELT(index+number，string1，string2，string3，…)，我认为下面的示例可以作为数组示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218037|set+@i+:=+1;
while+@i+<=+3
do
++insert+into+table(val)+values+(ELT(@i+,'val1','val2','val3'...));
set+@i+=+@i+%2B+1;
end+while;|code-block|syntax|javascript|218038|希望能有所帮助。|218039|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Inspired by the function ELT(index number, string1, string2, string3,…),I think the following example works as an array example:

<pre><code>set @i := 1;
while @i &lt;= 3
do
 insert into table(val) values (ELT(@i ,'val1','val2','val3'...));
set @i = @i + 1;
end while;
</code></pre>

Hope it help.

blocks|key|2860990|text|现在，使用JSON数组将是一个显而易见的解决方案。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2860991|由于这是一个古老但仍然相关的问题，我给出了一个简短的例子。MariaDB函数从mySQL+5.7.x+/+JSON+10.2.3开始可用|2860992|与ELT()相比，我更喜欢这种解决方案，因为它更像一个数组，并且这个“数组”可以在代码中重用。|2860993|但是要小心:它(JSON)肯定比使用临时表慢得多。它只是更方便而已。国际海事组织。|2860994|下面是如何使用JSON数组：|2860995|SET+@myjson+=+'["gmail.com","mail.ru","arcor.de","gmx.de","t-online.de",
++++++++++++++++"web.de","googlemail.com","freenet.de","yahoo.de","gmx.net",
++++++++++++++++"me.com","bluewin.ch","hotmail.com","hotmail.de","live.de",
++++++++++++++++"icloud.com","hotmail.co.uk","yahoo.co.jp","yandex.ru"]';

SELECT+JSON_LENGTH(@myjson);
--+result:+19

SELECT+JSON_VALUE(@myjson,+'$[0]');
--+result:+gmail.com|code-block|syntax|javascript|2860996|下面是一个小示例，说明它在函数/过程中是如何工作的：|2860997|DELIMITER+//
CREATE+OR+REPLACE+FUNCTION+example()+RETURNS+varchar(1000)+DETERMINISTIC
BEGIN
++DECLARE+_result+varchar(1000)+DEFAULT+'';
++DECLARE+_counter+INT+DEFAULT+0;
++DECLARE+_value+varchar(50);

++SET+@myjson+=+'["gmail.com","mail.ru","arcor.de","gmx.de","t-online.de",
++++++++++++++++"web.de","googlemail.com","freenet.de","yahoo.de","gmx.net",
++++++++++++++++"me.com","bluewin.ch","hotmail.com","hotmail.de","live.de",
++++++++++++++++"icloud.com","hotmail.co.uk","yahoo.co.jp","yandex.ru"]';

++WHILE+_counter+<+JSON_LENGTH(@myjson)+DO
++++--+do+whatever,+e.g.+add-up+strings...
++++SET+_result+=+CONCAT(_result,+_counter,+'-',+JSON_VALUE(@myjson,+CONCAT('$[',_counter,']')),+'#');

++++SET+_counter+=+_counter+%2B+1;
++END+WHILE;

++RETURN+_result;
END+//
DELIMITER+;

SELECT+example();|2860998|entityMap^0|5|6|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|12|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|13|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|14|8|@]|D|@]|E|$]]|$1|N|3|O|5|P|7|15|8|@]|D|@]|E|$Q|R]]|$1|S|3|T|5|6|7|16|8|@]|D|@]|E|$]]|$1|U|3|V|5|P|7|17|8|@]|D|@]|E|$Q|R]]|$1|W|3|-4|5|6|7|18|8|@]|D|@]|E|$]]]|X|$]]

Nowadays using a JSON array would be an obvious answer.

Since this is an old but still relevant question I produced a short example.
JSON functions are available since mySQL 5.7.x / MariaDB 10.2.3

I prefer this solution over ELT() because it's really more like an array and this 'array' can be reused in the code.

But be careful: It (JSON) is certainly much slower than using a temporary table. Its just more handy. imo.

Here is how to use a JSON array:

<pre><code>SET @myjson = '["gmail.com","mail.ru","arcor.de","gmx.de","t-online.de",
 "web.de","googlemail.com","freenet.de","yahoo.de","gmx.net",
 "me.com","bluewin.ch","hotmail.com","hotmail.de","live.de",
 "icloud.com","hotmail.co.uk","yahoo.co.jp","yandex.ru"]';

SELECT JSON_LENGTH(@myjson);
-- result: 19

SELECT JSON_VALUE(@myjson, '$[0]');
-- result: gmail.com
</code></pre>

And here a little example to show how it works in a function/procedure:

<pre><code>DELIMITER //
CREATE OR REPLACE FUNCTION example() RETURNS varchar(1000) DETERMINISTIC
BEGIN
 DECLARE _result varchar(1000) DEFAULT '';
 DECLARE _counter INT DEFAULT 0;
 DECLARE _value varchar(50);

 SET @myjson = '["gmail.com","mail.ru","arcor.de","gmx.de","t-online.de",
 "web.de","googlemail.com","freenet.de","yahoo.de","gmx.net",
 "me.com","bluewin.ch","hotmail.com","hotmail.de","live.de",
 "icloud.com","hotmail.co.uk","yahoo.co.jp","yandex.ru"]';

 WHILE _counter &lt; JSON_LENGTH(@myjson) DO
 -- do whatever, e.g. add-up strings...
 SET _result = CONCAT(_result, _counter, '-', JSON_VALUE(@myjson, CONCAT('$[',_counter,']')), '#');

 SET _counter = _counter + 1;
 END WHILE;

 RETURN _result;
END //
DELIMITER ;

SELECT example();
</code></pre>

blocks|key|215825|text|我知道这有点晚了，但我最近不得不解决一个类似的问题，并认为这可能对其他人有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215826|背景|offset|length|style|BOLD|215827|考虑下面名为‘mytable’的表格：|215828|​|215829|📷|atomic|215830|215831|问题是只保留最新的3条记录，并删除其systemid=1的任何较早的记录(表中可能有许多具有其他systemid值的其他记录)|215832|如果您只需使用以下语句即可完成此操作，这将是件好事|215833|DELETE+FROM+mytable+WHERE+id+IN+(SELECT+id+FROM+`mytable`+WHERE+systemid=1+ORDER+BY+id+DESC+LIMIT+3)|code-block|syntax|javascript|215834|但是，在MySQL中还不支持这一点，如果您尝试这样做，将会得到如下错误消息|215835|...doesn't+yet+support+'LIMIT+&+IN/ALL/SOME+subquery'|215836|因此，需要一种变通方法，即使用变量将值的数组传递给IN选择器。但是，由于变量需要是单个值，所以我需要模拟一个数组。字符串的诀窍是将数组创建为逗号分隔的值列表()，并将其赋给变量，如下所示|215837|SET+@myvar+:=+(SELECT+GROUP_CONCAT(id+SEPARATOR+',')+AS+myval+FROM+(SELECT+*+FROM+`mytable`+WHERE+systemid=1+ORDER+BY+id+DESC+LIMIT+3+)+A+GROUP+BY+A.systemid);|215838|存储在@myvar中的结果是|215839|215840|+5、6、7|blockquote|215841|215842|215843|接下来，使用FIND_IN_SET选择器从模拟数组中进行选择|215844|SELECT+*+FROM+mytable+WHERE+FIND_IN_SET(id,@myvar);|215845|合并后的最终结果如下：|215846|SET+@myvar+:=+(SELECT+GROUP_CONCAT(id+SEPARATOR+',')+AS+myval+FROM+(SELECT+*+FROM+`mytable`+WHERE+systemid=1+ORDER+BY+id+DESC+LIMIT+3+)+A+GROUP+BY+A.systemid);
DELETE+FROM+mytable+WHERE+FIND_IN_SET(id,@myvar);|215847|我知道这是一个非常具体的案例。但是，可以对它进行修改，以适应变量需要存储值数组的任何其他情况。|215848|我希望这能有所帮助。|215849|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/4818c19d95f80e13a81240ed52b568d2.jpeg|imageAlt^0|0|0|2|0|0|0|0|1|0|0|0|0|0|0|0|0|27|9|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|20|8|@$D|21|E|22|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|23|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|24|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|25|8|@]|9|@$D|26|E|27|1|28]]|A|$]]|$1|O|3|K|5|6|7|29|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|2A|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|2B|8|@]|9|@]|A|$]]|$1|T|3|U|5|V|7|2C|8|@]|9|@]|A|$W|X]]|$1|Y|3|Z|5|6|7|2D|8|@]|9|@]|A|$]]|$1|10|3|11|5|V|7|2E|8|@]|9|@]|A|$W|X]]|$1|12|3|13|5|6|7|2F|8|@$D|2G|E|2H|F|G]]|9|@]|A|$]]|$1|14|3|15|5|V|7|2I|8|@]|9|@]|A|$W|X]]|$1|16|3|17|5|6|7|2J|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|2K|8|@]|9|@]|A|$]]|$1|19|3|1A|5|1B|7|2L|8|@]|9|@]|A|$]]|$1|1C|3|-4|5|6|7|2M|8|@]|9|@]|A|$]]|$1|1D|3|-4|5|6|7|2N|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|2O|8|@]|9|@]|A|$]]|$1|1G|3|1H|5|V|7|2P|8|@]|9|@]|A|$W|X]]|$1|1I|3|1J|5|6|7|2Q|8|@]|9|@]|A|$]]|$1|1K|3|1L|5|V|7|2R|8|@]|9|@]|A|$W|X]]|$1|1M|3|1N|5|6|7|2S|8|@]|9|@]|A|$]]|$1|1O|3|1P|5|6|7|2T|8|@]|9|@]|A|$]]|$1|1Q|3|-4|5|6|7|2U|8|@]|9|@]|A|$]]]|1R|$1S|$5|1T|1U|1V|A|$1W|1X|1Y|-4]]]]

I know that this is a bit of a late response, but I recently had to solve a similar problem and thought that this may be useful to others.

Background

Consider the table below called 'mytable':

<a href="https://i.stack.imgur.com/ATQvN.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/ATQvN.jpg" alt="Starting table"></a>

The problem was to keep only latest 3 records and delete any older records whose systemid=1 (there could be many other records in the table with other systemid values)

It would be good is you could do this simply using the statement 

<pre><code>DELETE FROM mytable WHERE id IN (SELECT id FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3)
</code></pre>

However this is not yet supported in MySQL and if you try this then you will get an error like 

<pre><code>...doesn't yet support 'LIMIT &amp; IN/ALL/SOME subquery'
</code></pre>

So a workaround is needed whereby an array of values is passed to the IN selector using variable. However, as variables need to be single values, I would need to simulate an array. The trick is to create the array as a comma separated list of values (string) and assign this to the variable as follows

<pre><code>SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
</code></pre>

The result stored in @myvar is

<blockquote>
 5,6,7
</blockquote>

Next, the FIND_IN_SET selector is used to select from the simulated array

<pre><code>SELECT * FROM mytable WHERE FIND_IN_SET(id,@myvar);
</code></pre>

The combined final result is as follows:

<pre><code>SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
DELETE FROM mytable WHERE FIND_IN_SET(id,@myvar);
</code></pre>

I am aware that this is a very specific case. However it can be modified to suit just about any other case where a variable needs to store an array of values. 

I hope that this helps.

blocks|key|215860|text|数组的目的不是为了提高效率吗？如果您只是在遍历值，我认为临时(或永久)表上的游标比查找逗号更有意义，不是吗？也很干净。查找"mysql+DECLARE+CURSOR“|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215861|对于随机访问，使用具有数字索引的主键的临时表。不幸的是，你能得到的最快的访问是哈希表，而不是真正的随机访问。|215862|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Isn't the point of arrays to be efficient? If you're just iterating through values, I think a cursor on a temporary (or permanent) table makes more sense than seeking commas, no? Also cleaner. Lookup "mysql DECLARE CURSOR".

For random access a temporary table with numerically indexed primary key. Unfortunately the fastest access you'll get is a hash table, not true random access.

blocks|key|218100|text|令我惊讶的是，答案中没有提到ELT/FIELD。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218101|ELT/FIELD的工作方式非常类似于数组，特别是当你有静态数据的时候。|218102|FIND_IN_SET也有类似的功能，但是没有内置的补充功能，但是编写一个非常简单。|218103|mysql>+select+elt(2,'AA','BB','CC');
%2B-----------------------%2B
%7C+elt(2,'AA','BB','CC')+%7C
%2B-----------------------%2B
%7C+BB++++++++++++++++++++%7C
%2B-----------------------%2B
1+row+in+set+(0.00+sec)

mysql>+select+field('BB','AA','BB','CC');
%2B----------------------------%2B
%7C+field('BB','AA','BB','CC')+%7C
%2B----------------------------%2B
%7C++++++++++++++++++++++++++2+%7C
%2B----------------------------%2B
1+row+in+set+(0.00+sec)

mysql>+select+find_in_set('BB','AA,BB,CC');
%2B------------------------------%2B
%7C+find_in_set('BB','AA,BB,CC')+%7C
%2B------------------------------%2B
%7C++++++++++++++++++++++++++++2+%7C
%2B------------------------------%2B
1+row+in+set+(0.00+sec)

mysql>++SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX('AA,BB,CC',',',2),',',-1);
%2B-----------------------------------------------------------%2B
%7C+SUBSTRING_INDEX(SUBSTRING_INDEX('AA,BB,CC',',',2),',',-1)+%7C
%2B-----------------------------------------------------------%2B
%7C+BB++++++++++++++++++++++++++++++++++++++++++++++++++++++++%7C
%2B-----------------------------------------------------------%2B
1+row+in+set+(0.01+sec)|code-block|syntax|javascript|218104|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I'm surprised none of the answers mention ELT/FIELD.

ELT/FIELD works very similar to an array especially if you have static data.

FIND_IN_SET also works similar but doesn't have a built in complementary 
function but it's easy enough to write one.

<pre><code>mysql&gt; select elt(2,'AA','BB','CC');
+-----------------------+
| elt(2,'AA','BB','CC') |
+-----------------------+
| BB |
+-----------------------+
1 row in set (0.00 sec)

mysql&gt; select field('BB','AA','BB','CC');
+----------------------------+
| field('BB','AA','BB','CC') |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)

mysql&gt; select find_in_set('BB','AA,BB,CC');
+------------------------------+
| find_in_set('BB','AA,BB,CC') |
+------------------------------+
| 2 |
+------------------------------+
1 row in set (0.00 sec)

mysql&gt; SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('AA,BB,CC',',',2),',',-1);
+-----------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('AA,BB,CC',',',2),',',-1) |
+-----------------------------------------------------------+
| BB |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
</code></pre>

blocks|key|3074737|text|对于多个集合，我会尝试这样的东西。我是一个MySQL初学者。对于函数名称的问题，我很抱歉，我无法决定哪个名称是最好的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074738|delimiter+//

drop++procedure+init_
//
create+procedure+init_()
begin
++CREATE+TEMPORARY+TABLE+if+not+exists+
++++val_store(++
++++realm++varchar(30)+
++++,++id++varchar(30)+
++++,++val+++varchar(255)+
++++,++primary+key+(+realm+,+id+)
++++);
end;
//

drop+function+if+exists+get_
//
create+function+get_(+p_realm+varchar(30)+,+p_id+varchar(30)+)
++returns+varchar(255)
++reads+sql+data
begin+
++declare+ret_val+varchar(255);
++declare+continue+handler+for+1146+set+ret_val+=+null;
++select+val+into+ret_val+from+val_store+where+id+=+p_id;
++return+ret_val;
end;
//

drop+procedure+if+exists+set_
//
create+procedure+set_(+p_realm+varchar(30)+,+p_id+varchar(30)+,+p_val+varchar(255)+)
begin
++call+init_();+
++insert+into+val_store+(realm,id,val)+values+(p_realm+,+p_id+,+p_val)+on+duplicate+key+update+val+=+p_val;
end;
//

drop+++procedure+if+exists+remove_
//
create+procedure+remove_(+p_realm+varchar(30)+,+p_id+varchar(30)+)
begin
++call+init_();
++delete+from+val_store+where+realm+=+p_realm+and+id+=+p_id;
end;
//

drop+++procedure+if+exists+erase_
//
create+procedure+erase_(+p_realm+varchar(30)+)+
begin
++call+init_();
++delete+from+val_store+where+realm+=+p_realm;
end;
//

call+set_('my_array_table_name','my_key','my_value');

select+get_('my_array_table_name','my_key');|code-block|syntax|javascript|3074739|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I would try something like this for multiple collections. I'm a MySQL beginner. Sorry about the function names, couldn't decide on what names would be best.

<pre><code>delimiter //

drop procedure init_
//
create procedure init_()
begin
 CREATE TEMPORARY TABLE if not exists 
 val_store( 
 realm varchar(30) 
 , id varchar(30) 
 , val varchar(255) 
 , primary key ( realm , id )
 );
end;
//

drop function if exists get_
//
create function get_( p_realm varchar(30) , p_id varchar(30) )
 returns varchar(255)
 reads sql data
begin 
 declare ret_val varchar(255);
 declare continue handler for 1146 set ret_val = null;
 select val into ret_val from val_store where id = p_id;
 return ret_val;
end;
//

drop procedure if exists set_
//
create procedure set_( p_realm varchar(30) , p_id varchar(30) , p_val varchar(255) )
begin
 call init_(); 
 insert into val_store (realm,id,val) values (p_realm , p_id , p_val) on duplicate key update val = p_val;
end;
//

drop procedure if exists remove_
//
create procedure remove_( p_realm varchar(30) , p_id varchar(30) )
begin
 call init_();
 delete from val_store where realm = p_realm and id = p_id;
end;
//

drop procedure if exists erase_
//
create procedure erase_( p_realm varchar(30) ) 
begin
 call init_();
 delete from val_store where realm = p_realm;
end;
//

call set_('my_array_table_name','my_key','my_value');

select get_('my_array_table_name','my_key');
</code></pre>

blocks|key|2861184|text|另一种看待同样问题的方式。希望能帮上忙|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2861185|DELIMITER+$$
CREATE+PROCEDURE+ARR(v_value+VARCHAR(100))
BEGIN

DECLARE+v_tam+VARCHAR(100);
DECLARE+v_pos+VARCHAR(100);

CREATE+TEMPORARY+TABLE+IF+NOT+EXISTS+split+(split+VARCHAR(50));

SET+v_tam+=+(SELECT+(LENGTH(v_value)+-+LENGTH(REPLACE(v_value,',',''))));
SET+v_pos+=+1;

WHILE+(v_tam+>=+v_pos)
DO
++++INSERT+INTO+split+
++++SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX(v_value,',',v_pos),',',+-1);
++++SET+v_pos+=+v_pos+%2B+1;
END+WHILE;

SELECT+*+FROM+split;

DROP+TEMPORARY+TABLE+split;

END$$


CALL+ARR('1006212,1006404,1003404,1006505,444,');|code-block|syntax|javascript|2861186|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another way to see the same problem.
Hope helpfull

<pre><code>DELIMITER $$
CREATE PROCEDURE ARR(v_value VARCHAR(100))
BEGIN

DECLARE v_tam VARCHAR(100);
DECLARE v_pos VARCHAR(100);

CREATE TEMPORARY TABLE IF NOT EXISTS split (split VARCHAR(50));

SET v_tam = (SELECT (LENGTH(v_value) - LENGTH(REPLACE(v_value,',',''))));
SET v_pos = 1;

WHILE (v_tam &gt;= v_pos)
DO
 INSERT INTO split 
 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(v_value,',',v_pos),',', -1);
 SET v_pos = v_pos + 1;
END WHILE;

SELECT * FROM split;

DROP TEMPORARY TABLE split;

END$$


CALL ARR('1006212,1006404,1003404,1006505,444,');
</code></pre>

blocks|key|3074789|text|下面是一个循环遍历逗号分隔字符串的MySQL示例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074790|DECLARE+v_delimited_string_access_index+INT;
DECLARE+v_delimited_string_access_value+VARCHAR(255);
DECLARE+v_can_still_find_values_in_delimited_string+BOOLEAN;

SET+v_can_still_find_values_in_delimited_string+=+true;
SET+v_delimited_string_access_index+=+0;
WHILE+(v_can_still_find_values_in_delimited_string)+DO
++SET+v_delimited_string_access_value+=+get_from_delimiter_split_string(in_array,+',',+v_delimited_string_access_index);+--+get+value+from+string
++SET+v_delimited_string_access_index+=+v_delimited_string_access_index+%2B+1;
++IF+(v_delimited_string_access_value+=+'')+THEN
++++SET+v_can_still_find_values_in_delimited_string+=+false;+--+no+value+at+this+index,+stop+looping
++ELSE
++++--+DO+WHAT+YOU+WANT+WITH+v_delimited_string_access_value+HERE
++END+IF;
END+WHILE;|code-block|syntax|javascript|3074791|它使用这里定义的get_from_delimiter_split_string函数：https://stackoverflow.com/a/59666211/3068233|offset|length|style|CODE|3074792|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/59666211/3068233^0|0|0|8|V|16|18|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Here is an example for MySQL for looping through a comma delimited string. 

<pre><code>DECLARE v_delimited_string_access_index INT;
DECLARE v_delimited_string_access_value VARCHAR(255);
DECLARE v_can_still_find_values_in_delimited_string BOOLEAN;

SET v_can_still_find_values_in_delimited_string = true;
SET v_delimited_string_access_index = 0;
WHILE (v_can_still_find_values_in_delimited_string) DO
 SET v_delimited_string_access_value = get_from_delimiter_split_string(in_array, ',', v_delimited_string_access_index); -- get value from string
 SET v_delimited_string_access_index = v_delimited_string_access_index + 1;
 IF (v_delimited_string_access_value = '') THEN
 SET v_can_still_find_values_in_delimited_string = false; -- no value at this index, stop looping
 ELSE
 -- DO WHAT YOU WANT WITH v_delimited_string_access_value HERE
 END IF;
END WHILE;
</code></pre>

this uses the <code>get_from_delimiter_split_string</code> function defined here: <a href="https://stackoverflow.com/a/59666211/3068233">https://stackoverflow.com/a/59666211/3068233</a>

blocks|key|3074804|text|与将数据保存为数组或仅保存在一行中不同，您应该为接收到的每个值创建不同的行。这将使它更容易理解，而不是将所有内容放在一起。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074805|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Rather than Saving data as a array or in one row only you should be making diffrent rows for every value received. This will make it much simpler to understand rather than putting all together.

blocks|key|215959|text|数组变量真的有必要吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215960|我之所以这样问，是因为我最初来到这里的目的是想添加一个数组作为MySQL表变量。我是数据库设计的新手，正在努力思考如何用一种典型的编程语言来设计数据库。|215961|但是数据库是不同的。我以为我想要一个数组作为变量，但事实证明这并不是常见的MySQL数据库实践。|215962|标准实践|215963|数组的替代解决方案是添加一个附加表，然后使用外键引用原始表。|215964|例如，让我们想象一个应用程序，它跟踪一个家庭中的每个人想要在商店购买的所有物品。|215965|我最初设想的用于创建表的命令应该如下所示：|215966|#doesn't+work
CREATE+TABLE+Person(
++name+VARCHAR(50)+PRIMARY+KEY
++buy_list+ARRAY
);|code-block|syntax|javascript|215967|我想我设想的buy_list是一个逗号分隔的项目字符串或类似的东西。|215968|但是MySQL没有数组类型字段，所以我真的需要这样的东西：|215969|CREATE+TABLE+Person(
++name+VARCHAR(50)+PRIMARY+KEY
);
CREATE+TABLE+BuyList(
++person+VARCHAR(50),
++item+VARCHAR(50),
++PRIMARY+KEY+(person,+item),
++CONSTRAINT+fk_person+FOREIGN+KEY+(person)+REFERENCES+Person(name)
);|215970|这里我们定义了一个名为fk_person的约束。它说明BuyList中的'person‘字段是一个外键。换句话说，它是另一个表中的主键，特别是Person表中的'name‘字段，这就是引用所表示的。|215971|我们还将person和item的组合定义为主键，但从技术上讲这并不是必需的。|215972|最后，如果您想要获取一个人列表上的所有项目，可以运行以下查询：|215973|SELECT+item+FROM+BuyList+WHERE+person='John';|215974|这将为您提供John列表中的所有项目。不需要数组！|215975|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1C|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1D|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1E|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1G|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1H|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|X|5|P|7|1K|8|@]|9|@]|A|$Q|R]]|$1|Y|3|Z|5|6|7|1L|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1M|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1N|8|@]|9|@]|A|$]]|$1|14|3|15|5|P|7|1O|8|@]|9|@]|A|$Q|R]]|$1|16|3|17|5|6|7|1P|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|19|$]]

<h2>Is an array variable really necessary?</h2>
I ask because I originally landed here wanting to add an array as a MySQL table variable. I was relatively new to database design and trying to think of how I'd do it in a typical programming language fashion.
But databases are different. I thought I wanted an array as a variable, but it turns out that's just not a common MySQL database practice.
<h2>Standard Practice</h2>
The alternative solution to arrays is to add an additional table, and then reference your original table with a foreign key.
As an example, let's imagine an application that keeps track of all the items every person in a household wants to buy at the store.
The commands for creating the table I originally envisioned would have looked something like this:
<pre><code>#doesn't work
CREATE TABLE Person(
 name VARCHAR(50) PRIMARY KEY
 buy_list ARRAY
);
</code></pre>
I think I envisioned buy_list to be a comma-separated string of items or something like that.
But MySQL doesn't have an array type field, so I really needed something like this:
<pre><code>CREATE TABLE Person(
 name VARCHAR(50) PRIMARY KEY
);
CREATE TABLE BuyList(
 person VARCHAR(50),
 item VARCHAR(50),
 PRIMARY KEY (person, item),
 CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES Person(name)
);
</code></pre>
Here we define a constraint named fk_person. It says that the 'person' field in BuyList is a foreign key. In other words, it's a primary key in another table, specifically the 'name' field in the Person table, which is what REFERENCES denotes.
We also defined the combination of person and item to be the primary key, but technically that's not necessary.
Finally, if you want to get all the items on a person's list, you can run this query:
<pre><code>SELECT item FROM BuyList WHERE person='John';
</code></pre>
This gives you all the items on John's list. No arrays necessary!

blocks|key|3074865|text|如果我们有一张这样的桌子|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074866|mysql>+select+*+from+user_mail;
%2B------------%2B-------%2B
%7C+email++++++%7C+user+%7C+
%2B------------%2B-------%2B-
%7C+email1@gmail+%7C+++++1+%7C+
%7C+email2@gmail+%7C+++++2+%7C
%2B------------%2B-------%2B--------%2B------------%2B|code-block|syntax|javascript|3074867|和数组表：|3074868|mysql>+select+*+from+user_mail_array;
%2B------------%2B-------%2B-------------%2B
%7C+email++++++%7C+user+%7C+preferences+%7C
%2B------------%2B-------%2B-------------%2B
%7C+email1@gmail+%7C+++++1+%7C+++++++++++1+%7C
%7C+email1@gmail+%7C+++++1+%7C+++++++++++2+%7C
%7C+email1@gmail+%7C+++++1+%7C+++++++++++3+%7C
%7C+email1@gmail+%7C+++++1+%7C+++++++++++4+%7C
%7C+email2@gmail+%7C+++++2+%7C+++++++++++5+%7C
%7C+email2@gmail+%7C+++++2+%7C+++++++++++6+%7C|3074869|我们可以使用CONCAT函数将第二个表的行选作一个数组：|3074870|mysql>+SELECT+t1.*,+GROUP_CONCAT(t2.preferences)+AS+preferences
+++++FROM+user_mail+t1,user_mail_array+t2
+++++++where+t1.email=t2.email+and+t1.user=t2.user
+++++GROUP+BY+t1.email,t1.user;

%2B------------%2B-------%2B--------%2B------------%2B-------------%2B
%7C+email++++++%7C+user+%7C+preferences+%7C
%2B------------%2B-------%2B--------%2B------------%2B-------------%2B
%7Cemail1@gmail+%7C+++++1+%7C+1,3,2,4+++++%7C
%7Cemail2@gmail+%7C+++++2+%7C+5,6+++++++++%7C
%2B------------%2B-------%2B--------%2B------------%2B-------------%2B|3074871|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

If we have one table like that

<pre><code>mysql&gt; select * from user_mail;
+------------+-------+
| email | user | 
+------------+-------+-
| email1@gmail | 1 | 
| email2@gmail | 2 |
+------------+-------+--------+------------+
</code></pre>

and the array table:

<pre><code>mysql&gt; select * from user_mail_array;
+------------+-------+-------------+
| email | user | preferences |
+------------+-------+-------------+
| email1@gmail | 1 | 1 |
| email1@gmail | 1 | 2 |
| email1@gmail | 1 | 3 |
| email1@gmail | 1 | 4 |
| email2@gmail | 2 | 5 |
| email2@gmail | 2 | 6 |
</code></pre>

We can select the rows of the second table as one array with CONCAT function:

<pre><code>mysql&gt; SELECT t1.*, GROUP_CONCAT(t2.preferences) AS preferences
 FROM user_mail t1,user_mail_array t2
 where t1.email=t2.email and t1.user=t2.user
 GROUP BY t1.email,t1.user;

+------------+-------+--------+------------+-------------+
| email | user | preferences |
+------------+-------+--------+------------+-------------+
|email1@gmail | 1 | 1,3,2,4 |
|email2@gmail | 2 | 5,6 |
+------------+-------+--------+------------+-------------+
</code></pre>

blocks|key|3074886|text|这是我的解决方案，使用一个包含元素列表的变量。您可以在简单的查询中使用它(不需要使用存储过程或创建表)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074887|我在这个网站的其他地方找到了使用JSON_TABLE函数的诀窍(它在MySQL8中有效，但我不知道它在其他版本中是否有效)。|3074888|set+@x+=+'1,2,3,4'+;

select+c.NAME
from+colors+c
where+
++c.COD+in+(+
++++select+*+
++++from+json_table(+
++++++concat('[',@x,']'),
++++++'$[*]'+columns+(id+int+path+'$')+)+t+)+;|code-block|syntax|javascript|3074889|此外，您可能需要管理将一个或多个变量设置为empty_string的情况。在本例中，我添加了另一个技巧(即使x、y或x和y都是空字符串，查询也不会返回错误)：|offset|length|style|BOLD|3074890|set+@x+=+''+;
set+@y+=+'yellow'+;

select+c.NAME
from+colors+
where+
++if(@y+=+'',+1+=+1,+c.NAME+=+@y)
++and+if(@x+=+'',+1,+c.COD)+in+(+
++++select+*+
++++from+json_table(
++++++concat('[',if(@x+=+'',+1,+@x),']'),
++++++'$[*]'+columns+(id+int+path+'$')+)+t)+;|3074891|entityMap^0|0|0|0|L|C|1I|1|1K|1|1M|3|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]|$K|Y|L|Z|M|N]|$K|10|L|11|M|N]|$K|12|L|13|M|N]]|9|@]|A|$]]|$1|O|3|P|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|R|$]]

This is my solution to use a variable containing a list of elements.
You can use it in simple queries (no need to use store procedures or create tables).
I found somewhere else on the site the trick to use the JSON_TABLE function (it works in mysql 8, I dunno of it works in other versions).
<pre><code>set @x = '1,2,3,4' ;

select c.NAME
from colors c
where 
 c.COD in ( 
 select * 
 from json_table( 
 concat('[',@x,']'),
 '$[*]' columns (id int path '$') ) t ) ;
</code></pre>
Also, you may need to manage the case of one or more variables set to empty_string.
In this case I added another trick (the query does not return error even if x, y, or both x and y are empty strings):
<pre><code>set @x = '' ;
set @y = 'yellow' ;

select c.NAME
from colors 
where 
 if(@y = '', 1 = 1, c.NAME = @y)
 and if(@x = '', 1, c.COD) in ( 
 select * 
 from json_table(
 concat('[',if(@x = '', 1, @x),']'),
 '$[*]' columns (id int path '$') ) t) ;
</code></pre>

It <a href="https://stackoverflow.com/q/1433978/1593077">appears</a> that MySQL doesn't have array variables. What should I use instead? 

<hr>

There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to use these instead of array variables? Alternatively, if I go with sets, what would be the set-based idiom equivalent to <code>foreach</code>?

How can I simulate an array variable in MySQL?

它表明MySQL没有数组变量。我应该用什么来代替呢？似乎提出了两种替代方案：集合型标量和临时表。我链接到的问题表明是前者。但是，使用这些变量而不是数组变量是一种好的做法吗？或者，如果我使用集合，基于集合的习惯用法是什么，等同于foreach

问如何在MySQL中模拟数组变量？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中模拟数组变量？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中模拟数组变量？
EN