如何在MySQL中模拟数组变量?

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

看起来MySQL没有数组变量。我应该用什么来代替呢?

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?

似乎有两种备选方案:set-type标量和临时表。我所联系到的问题是前者。但是使用这些而不是数组变量是否是个好习惯呢?或者，如果我使用集合，那么与foreach等价的基于集合的习语是什么呢?

15 个解决方案

#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:

注意，您不需要正式定义它们的字段，只需使用SELECT:

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

(参见从select语句中创建临时表，而不使用Create table。)

#2

You can achieve this in MySQL using WHILE loop:

使用WHILE循环可以在MySQL中实现:

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:

编辑:或者你可以使用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
 ...
);

#3

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

尝试使用MySql的FIND_IN_SET()函数。

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.

注意:如果要传递带有CSV值的参数，则不必在StoredProcedure中设置变量。

#4

Dont know about the arrays, but there is a way to store comma-separated lists in normal VARCHAR column.

不知道数组，但是有一种方法可以将逗号分隔的列表存储在普通的VARCHAR列中。

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

当您需要在该列表中找到一些内容时，您可以使用FIND_IN_SET()函数。

#5

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:

现在你可以在以逗号分隔的ID列表中搜索ID，如下所示:

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

And you can use this function inside a WHERE clause, like this:

你可以在WHERE子句中使用这个函数，比如:

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.

这是我找到的将“数组”参数传递给过程的唯一方法。

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

#7

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

如果需要关联数组，可以使用列(键、值)创建一个临时内存表。拥有一个内存表是最接近mysql中的数组的东西

#8

This works fine for 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;

#9

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:

使用set的两个版本对我都不起作用(使用MySQL 5.5测试)。函数ELT()返回整个集合。考虑到WHILE语句仅在过程上下文中可用，我将它添加到我的解决方案中:

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

To be honest, i don't think this is a good practice. Even if its realy necessary, this is barely readable and quite slow.

老实说，我认为这不是一个好的实践。即使是必要的，也很难读懂，而且很慢。

#10

Nowadays using a JSON array would be an obvious answer.

现在使用JSON数组将是一个明显的答案。

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

由于这是一个古老但仍然相关的问题，我举了一个简短的例子。从mySQL 5.7开始就可以使用JSON函数。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.

我更喜欢这个解决方案而不是ELT()，因为它更像一个数组，这个“数组”可以在代码中重用。

But be careful: It is certainly much slower than using a temporary table. Its just more handy. imo.

但是要小心:它肯定比使用临时表慢得多。只是更方便。国际海事组织。

Here is how to use a JSON array:

以下是如何使用JSON数组:

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

#11

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.

在MYSQL版本的5.7之后。x，可以使用JSON类型存储数组。您可以通过MYSQL键获取数组的值。

#12

Inspired by the function ELT(index number, string1, string2, string3,…),I think the following example works as an array example:

受该函数的启发(索引号、string1、string2、string3、…)，我认为下面的例子是一个数组示例:

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

Hope it help.

希望它帮助。

#13

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':

考虑下面称为“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)

问题是只保留最新的3条记录，并删除任何系统id=1的旧记录(表中可能有许多其他具有其他系统id值的记录)

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

但是MySQL中还没有支持，如果你尝试一下，你会得到一个错误。

...doesn't yet support 'LIMIT & 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

因此需要一个变通方法，使用变量将值数组传递给IN选择器。但是，由于变量需要是单个值，所以我需要模拟一个数组。诀窍是将数组创建为逗号分隔的值列表(string)，并将其分配给变量如下所示

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

存储在@myvar中的结果是

5,6,7

5、6、7

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

接下来，使用FIND_IN_SET选择器从模拟数组中选择

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.

我希望这能有所帮助。

#14

-1

I Think I can improve on this answer. Try this:

我想我可以改进这个答案。试试这个:

The parameter 'Pranks' is a CSV. ie. '1,2,3,4.....etc'

参数“恶作剧”是一个CSV。ie。“1、2、3、4 .....等”

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.

该方法使搜索的CSV值字符串随着循环的每次迭代逐渐变短，我认为这将更好地进行优化。

#15

-4

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

您尝试过使用PHP的serialize()吗?这允许您将变量数组的内容存储在PHP理解的字符串中，并且对数据库是安全的(假设您首先转义了它)。

$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 exact same without a numbered array

您也可以在没有编号的数组的情况下执行相同的操作

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

或

$array3 = array(
    'somethingNew'
);

#1