How to loop with an array in MySQL?

Question

I would like to create a stored procedure or a regular request 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 will not work, but I would like to know how to achieve this.

Answer 1

Try to do this without a stored procedure -

 SET @arr = 'hi,hello,good'; -- your array SELECT COUNT(*) FROM tablex WHERE FIND_IN_SET (name, @arr); -- calculate count 

Answer 2

None of the existing answers worked for me, so I ended up implementing my own MySQL routine. This is pretty easy.

 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 

Answer 3

Relational databases do not execute arrays; they execute scalars, rows, and tables. SQL is a declarative rather than a procedural language.

To count entries in a table, use the COUNT function:

 SELECT COUNT(*) FROM tablex WHERE name IN ("hi", "hello", "good", ...) 

If you need to handle a variable number of values ​​that need to be matched in one of the statements, you can create a temporary table to store the values ​​instead of using IN :

 SELECT COUNT(*) FROM tablex JOIN names ON tablex.name=names.name 

Answer 4

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; 

Answer 5

An example of a WHILE loop inside a stored procedure:

 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 article for examples of arrays.

Answer 6

I assume you just want:

 SELECT * FROM tablex WHERE name IN ('hi', 'hello', 'good', ...) 

Do you have a problem passing an array to a procedure?

Answer 7

If you can create a table to store array values, you can do this without writing a loop. Use the 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;