How can I simulate an array variable in MySQL?

Question

It appears that MySQL doesn't have array variables. What should I use instead?

---

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

Solution 1

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:

DROP TEMPORARY TABLE IF EXISTS my_temp_table;
CREATE TEMPORARY TABLE my_temp_table
    SELECT first_name FROM people WHERE last_name = 'Smith';

(See also Create temporary table from select statement without using Create Table.)

Solution 2

You can achieve this in MySQL using WHILE loop:

SET @myArrayOfValue = '2,5,2,23,6,';

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

    INSERT INTO `EXEMPLE` VALUES(@value, 'hello');
END WHILE;

EDIT: Alternatively you can do it using UNION ALL:

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
 ...
);

Solution 3

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

SET @c = 'xxx,yyy,zzz';

SELECT * from countries 
WHERE FIND_IN_SET(countryname,@c);

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

Solution 4

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:

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

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

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 + 1;
  END WHILE;

  RETURN _result;
END //
DELIMITER ;

SELECT example();

Solution 5

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 FIND_IN_SET() function.

View more solutions

Comments

[einpoklum]

Aren't loops possible only in stored procedures?

[Omesh]

yes right, its possible inside stored procedures, functions and triggers.

[einpoklum]

So I can't use the code you provided... I need something that's applicable more generally.

[Omesh]

You can write a sample stored procedure and CALL it.

[Omesh]

I don't think arrays are needed. You can easily do it using temporary tables or UNION ALL without using procedure.

[einpoklum]

Can you please write another answer demonstration the use of UNION ALL in this context?

[Omesh]

Ya sure. See updated answer, it's similar to two dimensional array.

[einpoklum]

I'm not working with PHP, so that's not really relevant for me.

[Akshay Vishnoi]

If i want to find subset in a set, is there any way?

[wormhit]

Sorry! I'm not sure it is possible.

[einpoklum]

Umm, I don't want associative arrays, just arrays.

[Pavle Lekic]

you can use a temporary memory table with only one column and then loop thru the values using cursors, that is the closest thing to using arrays and for/while loops in a non-declarative programming language

[einpoklum]

The language actually has this feature, i.e., there's no syntactic reason you shouldn't be able to select a vector into a variable just like you select a scalar into it.

[einpoklum]

What is 'this answer' you're referring to? Also, you don't get to have a CSV file.

[Igbanam]

Ohhh :o I didn't know SQL had this!! The tables are only alive for the scope of al queries being run. NEAT!

[Pacerier]

@Yasky, That's provided you do not reuse connection. Because indeed it will last for the entire session.

[Pacerier]

@Omesh, Your method of doing it is seriously indirect. Why not simply loop ELT(x, @myArrayOfValue); until you get null?

[John]

And you can't reuse a temporary table. So it's not very useful.

[einpoklum]

@John: How do you mean?

[John]

@einpoklum - dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.htm‌​l

[einpoklum]

@John: Yeah, well, you can reuse it, but not in the same query.

[John]

@einpoklum. No, unfortunately that did not work for me. I was trying two separate queries, which was the whole point of making a temporary table. The other funny part to me, was that the user has to have insert rights on the db for a temporary table, so the whole thing is a big hassle for seemingly no benefit (to me). I ended up just making a normal table.

[einpoklum]

Please explain how this code is to be used and how it answers the question.

[Rick James]

Use JSON instead of serialize. It is more generic and language-independent.

[Sagar Gangwal]

As here you make simple procedure which gives one by one element of that particular string which work as array in oracle.

[einpoklum]

Oracle? This question is not about Oracle. Also, it looks like you're defining an array within the procedure.

[Sagar Gangwal]

Kindly check Syntex it's only for mysql

[einpoklum]

Can you give an example of how I would do this?

[Nemo]

Beware of the length limits, which can be quite low: stackoverflow.com/q/2567000/1333493

[Kanagavelu Sugumar]

Are you saying ELT is slower or JSON is slower ?

[SeparateReality]

@Kanagavelu Sugumar: At the time of writing JSON is definitely slower. I edited the answer to make that clearer.

[einpoklum]

This is a comment, not an answer. I did not indicate that is what I want to do with the array.

[einpoklum]

I think I understand what you're suggesting, but this is quite unwieldy and probably also incredibly slow...

[Dave]

I can't endorse or dismiss it without stress testing it. It is basically a primary key lookup and insertion on a temporary table (or normal table). I will use it until I run into problems or find a better way; but I do weird things like write compilers and games completely in Oracle PL/SQL.

[Calvin]

You have the best solution

[Eagle_Eye]

Don't miss the last comma!

[einpoklum]

Actually, it isn't clear what you're saying here. This question from 7 years ago has several relevant answers. Consider removing your answer or explaining what you're trying to tell us directly/generally rather than through an example.

[einpoklum]

comma-delimited strings have already been suggested - years ago.

[Ulad Kasach]

@einpoklum yep - and here is another way of interacting with them

[Nico Haase]

Please share how to do this

[einpoklum]

My accepted solution is to use a temporary table.

[kraftydevil]

Of course. I then included this answer for anyone like me that got to this page looking for a way to create an array type - who didn't initially understand why arrays are not a type in MySQL. It's by design it seems. The general case wasn't represented here so I included what I learned for others to understand that arrays are not typically needed. I don't expect you to select my answer. It depends on the use case. You have your accepted answer for a specific use case and I'm providing this answer for the general use case.

[einpoklum]

Duplicate of Clinton's answer, I think.

[user2288580]

I wasn't referring to a CSV file, I was referring to a CSV value such as '1,2,3,4... etc'

[Aurast]

If you are reusing connections, you generally should DROP TEMPORARY TABLE IF EXISTS my_temp_table; before creating it.

[Fabien Haddadi]

ELT(1, <string>) will return that string, not a substring. What's the point? I thin kyou meant SELECT SUBSTRING(@myArrayOfValue, 1, LOCATE(',', @myArrayOfValue)-1);

[Daniil]

You've done a great job! Thanks to you I 've found out about 4 new SQL-functions!

[Clinton]

Delighted to have been able to help.