如何在 MySQL 中循环使用数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8851418/
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
How to cycle with an array in MySQL?
提问by Davide
I'd like to create a stored procedure or a normal query with values passed with an array.
我想用数组传递的值创建一个存储过程或一个普通查询。
Example:
例子：
CREATE PROCEDURE proc()
BEGIN
DECLARE cont INTEGER;
DECLARE var ARRAY;
SET cont = 0;
SET var = ("hi", "hello", "good", ...)
WHILE cont < 12 DO
SELECT * FROM tablex
WHERE name = var[cont];
SET cont = cont + 1;
END WHILE;
END;
Obviously this is will not work, but I'd like to know how to achieve this.
显然这是行不通的，但我想知道如何实现这一点。
回答by Devart
Try to do it without stored routine -
尝试在没有存储例程的情况下进行 -
SET @arr = 'hi,hello,good'; -- your array
SELECT COUNT(*) FROM tablex
WHERE FIND_IN_SET (name, @arr); -- calculate count
回答by Neurotransmitter
Neither of existing answers worked for me, so I ended up implementing my very own (and very first) MySQL procedure.
现有的答案都不适合我，所以我最终实现了我自己的（也是第一个）MySQL 程序。
PROCEDURE db.loop_through_array()
BEGIN
DECLARE var varchar(150) DEFAULT 'hi,hello,good';
DECLARE element varchar(150);
WHILE var != '' DO
SET element = SUBSTRING_INDEX(var, ',', 1);
SELECT * FROM tablex WHERE name = element;
IF LOCATE(',', var) > 0 THEN
SET var = SUBSTRING(var, LOCATE(',', var) + 1);
ELSE
SET var = '';
END IF;
END WHILE;
END
回答by outis
Relational databases don't do arrays, they do scalars, rows and tables. SQL is largely a declarative, rather than procedural, language.
关系数据库不做数组，他们做标量、行和表。SQL 在很大程度上是一种声明性语言，而不是过程性语言。
To count entries in a table, use the COUNTaggregate function:
要计算表中的条目，请使用COUNT聚合函数：
SELECT COUNT(*)
FROM tablex
WHERE name IN ("hi", "hello", "good", ...)
If you need to handle a variable number of values to match against in a single statement, you can create a temporary table to hold the values instead of using IN:
如果您需要在单个语句中处理要匹配的可变数量的值，您可以创建一个临时表来保存这些值，而不是使用IN：
SELECT COUNT(*)
FROM tablex
JOIN names ON tablex.name=names.name
回答by Che
Try something like this:
尝试这样的事情：
CREATE PROCEDURE proc()
BEGIN
DECLARE cont INTEGER;
SET cont = 0;
CREATE TEMPORARY TABLE array_table (idx INT, value VARCHAR(20));
INSERT INTO array_table (idx, value) VALUES (1,"hi"),(2,"hello"),(3,"good"),...;
WHILE cont < 12 DO
SELECT * FROM tablex
WHERE name IN (SELECT value FROM array_table WHERE idx = cont);
SET cont = cont + 1;
END WHILE;
END;
回答by John Woo
an example of WHILEloop inside stored procedure:
WHILE存储过程中的循环示例：
DELIMITER $$
DROP PROCEDURE IF EXISTS WhileLoopProc$$
CREATE PROCEDURE WhileLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
SELECT str;
END$$
DELIMITER ;
you can check this articlefor examples of arrays.
您可以查看本文以获取数组示例。
回答by ypercube??
I guess that you just want to:
我猜你只是想：
SELECT * FROM tablex
WHERE name IN ('hi', 'hello', 'good', ...)
Do you have a problem with how to pass an array to a procedure?
您对如何将数组传递给过程有疑问吗？
回答by Isaac Han
If you can create a table to store the array values you can do it without writing a loop. Use in() operator.
如果您可以创建一个表来存储数组值，则无需编写循环即可完成。使用 in() 运算符。
CREATE TABLE test_strings (element CHAR(6));
INSERT INTO test_strings (element) VALUES ('hi'),('hello'),('good');
SELECT * FROM tablex t
WHERE name IN(SELECT element FROM test_strings)
ORDER BY t.name;
