How to cycle with an array in MySQL?

Question

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.

score 14 · Accepted Answer · answered Jan 13 '12 at 14:04

14

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

answered Jan 13 '12 at 14:04

Devart

119,203
23
166
186

Neurotransmitter · Answer 2 · 2021-06-25T12:58:59.830

12

Neither of existing answers worked for me, so I ended up implementing my very own (and very first) MySQL procedure.

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

edited Jun 25 '21 at 12:58

answered Aug 02 '16 at 15:47

Neurotransmitter

6,289
2
51
38

1

The answer from Devart is very efficient for the example. In my case, I am looking to add accounts , usernames, passords and the same set of permissions. The solution from Neurotransmitter works well for this case. – BenDavid Aug 20 '20 at 17:49
Would this be possible to make tablex as variable from element? When I try to do the same, mysql think its a table and says table schema.element doesnt exist. How to do the same with variable as a table and var would contain definition of all tables? – bennes Aug 19 '21 at 14:18

score 3 · Answer 3 · answered Apr 19 '13 at 09:43

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;

score 1 · Answer 4 · answered Jan 13 '12 at 13:47

an example of WHILE loop inside 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.

score 1 · Answer 5 · answered Jan 13 '12 at 14:03

1

Relational databases don't do arrays, they do scalars, rows and tables. SQL is largely a declarative, rather than procedural, language.

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

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:

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

answered Jan 13 '12 at 14:03

outis

75,655
22
151
221

That's not what I was looking for. I don't need to count entries in a table but, instead, to cycle a select statement for each array entry. – Davide Jan 15 '12 at 17:49
@Davide: What do you mean by "cycle a select statement"? Please make the [example](http://sscce.org/) in your question complete by including sample table schema (as a `CREATE TABLE` statement), data (as an `INSERT ... INTO` statement) and desired results. – outis Jan 16 '12 at 00:26
As in a programming language
x = new Array("a", "b", "c"); – Davide Jan 17 '12 at 09:38
@Davide: do you mean "loop"? A loop isn't what you need, that's just your [solution](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) to some other problem you've yet to describe. Edit your question and explain what you [really need](http://catb.org/~esr/faqs/smart-questions.html#goal). SQL isn't really a programming language, it's a declarative data language. It has procedural elements, but these are tacked-on and should never be the primary means of expression. – outis Jan 17 '12 at 10:38

score -1 · Answer 6 · answered Dec 23 '17 at 10:39

If you can create a table to store the array values you can do it without writing a loop. Use in() operator.

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;

score -2 · Answer 7 · answered Jan 13 '12 at 14:13

-2

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?

answered Jan 13 '12 at 14:13

ypercubeᵀᴹ

113,259
19
174
235

The problem is that I need to cycle for each array entry. – Davide Jan 15 '12 at 17:48

How to cycle with an array in MySQL?

7 Answers7

Linked