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

User · Answer

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  lt  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

User · Answer

In MYSQL version after 5 7 x  you can use JSON type to store an array  You can get value of an array by a key via MYSQL

User · Answer

Is an array variable really necessary  I ask because I originally landed here wanting to add an array as a MySQL table variable  I was relatively new to database design and trying to think of how I d do it in a typical programming language fashion  But databases are different  I thought I wanted an array as a variable  but it turns out that s just not a common MySQL database practice  Standard Practice The alternative solution to arrays is to add an additional table  and then reference your original table with a foreign key  As an example  let s imagine an application that keeps track of all the items every person in a household wants to buy at the store  The commands for creating the table I originally envisioned would have looked something like this   doesn t work CREATE TABLE Person    name VARCHAR 50  PRIMARY KEY   buy list ARRAY     I think I envisioned buy list to be a comma-separated string of items or something like that  But MySQL doesn t have an array type field  so I really needed something like this  CREATE TABLE Person    name VARCHAR 50  PRIMARY KEY    CREATE TABLE BuyList    person VARCHAR 50     item VARCHAR 50     PRIMARY KEY  person  item     CONSTRAINT fk person FOREIGN KEY  person  REFERENCES Person name      Here we define a constraint named fk person  It says that the  person  field in BuyList is a foreign key  In other words  it s a primary key in another table  specifically the  name  field in the Person table  which is what REFERENCES denotes  We also defined the combination of person and item to be the primary key  but technically that s not necessary  Finally  if you want to get all the items on a person s list  you can run this query  SELECT item FROM BuyList WHERE person  John    This gives you all the items on John s list  No arrays necessary

User · Answer

Here   s how I did it   First  I created a function that checks whether a Long Integer whatever value is in a list of values separated by commas   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 ID in a comma-separated list of IDs  like this   select  is id in ids   1001 1002 1003  1002     And you can use this function inside a WHERE clause  like this   SELECT   FROM table1 WHERE  is id in ids   1001 1002 1003  table1 id     This was the only way I found to pass an  array  parameter to a PROCEDURE

User · Answer

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

User · Answer

Both versions using sets didn t work for me  tested with MySQL 5 5   The function ELT   returns the whole set  Considering the WHILE statement is only avaible in PROCEDURE context 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   gt  0      DO         SET  value   LEFT  myArrayOfValue  LOCATE      myArrayOfValue  - 1               SET  myArrayOfValue   SUBSTRING  myArrayOfValue  LOCATE      myArrayOfValue    1       END WHILE  END    DELIMITER    CALL   main      To be honest  i don t think this is a good practice  Even if its realy necessary  this is barely readable and quite slow

User · Answer

This works fine for list of values   SET  myArrayOfValue    2 5 2 23 6     WHILE  LOCATE       myArrayOfValue   gt  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

User · Answer

Rather than Saving data as a array or in one row only you should be making diffrent rows for every value received  This will make it much simpler to understand rather than putting all together

User · Answer

If we have one table like that  mysql gt  select   from user mail   ------------ -------    email        user     ------------ ------- -   email1 gmail       1      email2 gmail       2    ------------ ------- -------- ------------    and the array table   mysql gt  select   from user mail array   ------------ ------- -------------    email        user   preferences    ------------ ------- -------------    email1 gmail       1             1     email1 gmail       1             2     email1 gmail       1             3     email1 gmail       1             4     email2 gmail       2             5     email2 gmail       2             6     We can select the rows of the second table as one array with CONCAT function   mysql gt  SELECT t1    GROUP CONCAT t2 preferences  AS preferences      FROM user mail t1 user mail array t2        where t1 email t2 email and t1 user t2 user      GROUP BY t1 email t1 user    ------------ ------- -------- ------------ -------------    email        user   preferences    ------------ ------- -------- ------------ -------------   email1 gmail       1   1 3 2 4        email2 gmail       2   5 6            ------------ ------- -------- ------------ -------------

User · Answer

DELIMITER    CREATE DEFINER  mysqldb      PROCEDURE  abc    BEGIN   BEGIN      set  value    11 2 3 1         WHILE  LOCATE       value   gt  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

User · Answer

I know that this is a bit of a late response  but I recently had to solve a similar problem and thought that this may be useful to others   Background  Consider the table below called  mytable      The problem was to keep only latest 3 records and delete any older records whose systemid 1  there could be many other records in the table with other systemid values   It would be good is you could do this simply using the 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 then you will get an error like      doesn t yet support  LIMIT  amp  IN ALL SOME subquery    So a workaround is needed whereby an array of values is passed to the IN selector using variable  However  as variables need to be single values  I would need to simulate an array  The trick is to create the array as a comma separated list of values  string  and assign this to the 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     The result stored in  myvar is     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 combined final 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 am aware that this is a very specific case  However it can be modified to suit just about any other case where a variable needs to store an array of values    I hope that this helps

User · Answer

Have you tried using PHP s serialize    That allows you to store the contents of a variable s array in a string PHP understands and is safe for the database  assuming you ve escaped it first     array   array      1   gt   some data       2   gt   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 exact same without a numbered array   array2   array       something    gt   something else       or   array3   array       somethingNew

User · Answer

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

User · Answer

I m surprised none of the answers mention ELT FIELD   ELT FIELD works very similar to an array especially if you have static data   FIND IN SET also works similar but doesn t have a built in complementary  function but it s easy enough to write one   mysql gt  select elt 2  AA   BB   CC     -----------------------    elt 2  AA   BB   CC      -----------------------    BB                       -----------------------  1 row in set  0 00 sec   mysql gt  select field  BB   AA   BB   CC     ----------------------------    field  BB   AA   BB   CC      ----------------------------                             2    ----------------------------  1 row in set  0 00 sec   mysql gt  select find in set  BB   AA BB CC     ------------------------------    find in set  BB   AA BB CC      ------------------------------                               2    ------------------------------  1 row in set  0 00 sec   mysql gt   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

User · Answer

Inspired by the function ELT index number  string1  string2  string3      I think  the following example works as an array example   set  i    1  while  i  lt   3 do   insert into table val  values  ELT  i   val1   val2   val3        set  i    i   1  end while    Hope it help

User · Answer

Another way to see the same problem  Hope helpfull  DELIMITER    CREATE PROCEDURE ARR v value VARCHAR 100   BEGIN  DECLARE v tam VARCHAR 100   DECLARE v pos VARCHAR 100    CREATE TEMPORARY TABLE IF NOT EXISTS split  split VARCHAR 50     SET v tam    SELECT  LENGTH v value  - LENGTH REPLACE v value             SET v pos   1   WHILE  v tam  gt   v pos  DO     INSERT INTO split      SELECT SUBSTRING INDEX SUBSTRING INDEX v value     v pos       -1       SET v pos   v pos   1  END WHILE   SELECT   FROM split   DROP TEMPORARY TABLE split   END     CALL ARR  1006212 1006404 1003404 1006505 444

User · Answer

I Think I can improve on this answer  Try this   The parameter  Pranks  is a CSV  ie   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 searched string of CSV values progressively shorter with each iteration of the loop  which I believe would be better for optimization

User · Answer

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

User · Answer

Isn t the point of arrays to be efficient  If you re just iterating through values  I think a cursor on a temporary  or permanent  table makes more sense than seeking commas  no  Also cleaner   Lookup  mysql DECLARE CURSOR    For random access a temporary table with numerically indexed primary key  Unfortunately the fastest access you ll get is a hash table  not true random access

User · Answer

Here is an example for MySQL for looping through a comma delimited string    DECLARE v delimited string access index INT  DECLARE v delimited string access value VARCHAR 255   DECLARE v can still find values in delimited string BOOLEAN   SET v can still find values in delimited string   true  SET v delimited string access index   0  WHILE  v can still find values in delimited string  DO   SET v delimited string access value   get from delimiter split string in array       v delimited string access index   -- get value from string   SET v delimited string access index   v delimited string access index   1    IF  v delimited string access value       THEN     SET v can still find values in delimited string   false  -- no value at this index  stop looping   ELSE     -- DO WHAT YOU WANT WITH v delimited string access value HERE   END IF  END WHILE    this uses the get from delimiter split string function defined here  https   stackoverflow com a 59666211 3068233

User · Answer

You can achieve this in MySQL using WHILE loop   SET  myArrayOfValue    2 5 2 23 6     WHILE  LOCATE       myArrayOfValue   gt  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

User · Answer

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

User · Answer

I would try something like this for multiple collections  I m a MySQL beginner  Sorry about the function names  couldn t decide on what names would be best   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

[mysql] How can I simulate an array variable in MySQL?

Examples related to mysql

Examples related to arrays

Examples related to variables

Examples related to set

Examples related to temp-tables