How can I simulate an array variable in MySQL?

Question

MySQL will appear to have no array variables. What should i use instead?

---

There seem to be two alternatives: A scalar type and temporary tables . The question that I linked suggests the first. But is it useful to use them instead of array variables? Alternatively, if I go with sets, what would be the set-based idiom equivalent to foreach ?

Answer 1

Well, I used temporary tables instead of array variables. Not the best solution, but it works.

Note that you do not need to formally define your fields, just create them using SELECT:

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

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

Answer 2

You can achieve this in MySQL using the 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 this with 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 ... ); 

Answer 3

Try using the FIND_IN_SET () function of MySql for example

 SET @c = 'xxx,yyy,zzz'; SELECT * from countries WHERE FIND_IN_SET(countryname,@c); 

Note. You do not need to set a variable in StoredProcedure if you pass a parameter with CSV values.

Answer 4

I don't know about arrays, but there is a way to store lists separated by commas in a regular VARCHAR column.

And when you need to find something in this list, you can use the FIND_IN_SET () function.

Answer 5

For now, using a JSON array would be the obvious answer.

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

I prefer this ELT () solution, because it really looks 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. It is just more convenient. imo.

Here's 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 is a small example to show how this 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, eg 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(); 

Answer 6

 DELIMITER $$ CREATE DEFINER='mysqldb'@'%' 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) + 1); select @V_DESIGNATION; END WHILE; END; END$$ DELIMITER ; 

Answer 7

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

Answer 8

Here is how I did it.

First I created a function that checks to see if the value Long / Integer / is any value in the comma separated list of values:

 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; 

So now you can search for an identifier in a list of identifiers separated by commas, for example:

 select `is_id_in_ids`('1001,1002,1003',1002); 

And you can use this function inside the WHERE clause, for example:

 SELECT * FROM table1 WHERE `is_id_in_ids`('1001,1002,1003',table1_id); 

This was the only way to pass the array parameter to the PROCEDURE.

Answer 9

I know this is a bit late reply, but recently I had to solve a similar problem and thought it might be useful to others.

Background

Consider the table below called "mytable":

The problem was to save only the last 3 entries and delete all old entries that have systemid = 1 (there can be many other entries in the table with different systemid values)

It would be nice if you could do it just using a statement

 DELETE FROM mytable WHERE id IN (SELECT id FROM 'mytable' WHERE systemid=1 ORDER BY id DESC LIMIT 3) 

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

 ...doesn't yet support 'LIMIT & IN/ALL/SOME subquery' 

Thus, a workaround is needed where an array of values ​​is passed to the IN selector using a variable. However, since the variables must be unit values, I will need to model the array. The trick is to create an array as a list of values ​​(strings), separated by commas, and assign it to a variable as follows

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

Result stored by @myvar:

5,6,7

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

 SELECT * FROM mytable WHERE FIND_IN_SET(id,@myvar); 

The total end result is as follows:

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

I know this is a very specific case. However, it can be changed to fit almost any other case where a variable must store an array of values.

I hope this helps.

Answer 10

Are arrays not efficient? If you just iterate over the values, I think the cursor on a temporary (or permanent) table makes more sense than finding commas, right? Also cleaner. Find "mysql DECLARE CURSOR".

For random access, a temporary table with a primary key with a numerical index. Unfortunately, the fastest access you get is a hash table, not random access.

Answer 11

This is great for a list of values:

 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) + 1); INSERT INTO `Demo` VALUES(@STR, 'hello'); END WHILE; 

Answer 12

Both versions using sets did not work for me (tested with MySQL 5.5). The ELT () function returns the entire set. Given that the WHILE statement is only available in the context of PROCEDURE, I added it to my solution:

 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) + 1); END WHILE; END; $ DELIMITER ; CALL __main__; 

Honestly, I do not think this is a good practice. Even if it is really necessary, it is barely readable and rather slow.

Answer 13

I am surprised that none of the answers mention ELT / FIELD.

ELT / FIELD works very much like an array, especially if you have static data.

FIND_IN_SET also works similarly, but does not have a built-in additional function, but it is quite easy to write.

 mysql> select elt(2,'AA','BB','CC'); +-----------------------+ | elt(2,'AA','BB','CC') | +-----------------------+ | BB | +-----------------------+ 1 row in set (0.00 sec) mysql> select field('BB','AA','BB','CC'); +----------------------------+ | field('BB','AA','BB','CC') | +----------------------------+ | 2 | +----------------------------+ 1 row in set (0.00 sec) mysql> select find_in_set('CC','AA,BB,CC'); +------------------------------+ | find_in_set('BB','AA,BB,CC') | +------------------------------+ | 2 | +------------------------------+ 1 row in set (0.00 sec) mysql> 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) 

Answer 14

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

Answer 15

Inspired by the ELT function (serial number, line1, line2, line3, ...), I think the following example works like an array example:

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

Hope this helps.

Answer 16

I think I can improve this answer. Try the following:

The Pranks parameter is CSV. i.e. '1,2,3,4 ..... etc.'

 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; 

This method makes the search for the required string of CSV values ​​gradually shorter with each iteration of the loop, which, in my opinion, would be better for optimization.

Answer 17

I would try something similar for several collections. I am new to MySQL. Sorry for the function names, I can not decide which names will be better.

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

Answer 18

Have you tried using PHP serialize ()? This allows you to store the contents of an array of variables in a PHP string, which is understandable and safe for the database (provided that you first avoided it).

 $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, '%s')" , serialize(mysql_real_escape_string($array, $dbConnection))); mysql_query($sql, $dbConnection) or die(mysql_error()); 

You can also do the same without a numbered array

 $array2 = array( 'something' => 'something else' ); 

or

 $array3 = array( 'somethingNew' );