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

How can I simulate an array variable in MySQL?

提问by einpoklum

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

这似乎是MySQL没有数组变量。我应该用什么代替？

---

---

There seem to be two alternatives suggested: A set-type scalarand 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?

似乎建议了两种替代方案：集合类型标量和临时表。我链接到的问题表明前者。但是使用这些而不是数组变量是一种好习惯吗？或者，如果我使用集合，那么基于集合的习语相当于foreach什么？

采纳答案by einpoklum

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

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

（另请参阅在不使用 Create Table 的情况下从 select 语句创建临时表。）

回答by Omesh

You can achieve this in MySQL using WHILEloop:

您可以使用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
 ...
);

回答by Himalaya Garg

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 中设置变量。

回答by SeparateReality

Nowadays using a JSON arraywould 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

由于这是一个古老但仍然相关的问题，我制作了一个简短的例子。JSON 函数自 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.

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

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

但要小心：它（JSON）肯定比使用临时表慢得多。它只是更方便。海事组织。

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

回答by wormhit

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()函数。

回答by Sagar Gangwal

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 ;

回答by Clinton

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 条记录并删除 systemid=1 的所有旧记录（表中可能有许多其他记录具有其他 systemid 值）

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 variableas follows

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

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.

我希望这个对你有用。

回答by Pavle Lekic

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 中拥有数组

回答by chuckedw

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:

首先，我创建了一个函数来检查一个 Long/Integer/whatever 值是否在以逗号分隔的值列表中：

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.

这是我发现将“数组”参数传递给 PROCEDURE 的唯一方法。

回答by Amaigus

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

数组的重点不是高效吗？如果您只是遍历值，我认为临时（或永久）表上的光标比寻找逗号更有意义，不是吗？也比较干净。查找“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.

对于随机访问具有数字索引主键的临时表。不幸的是，您将获得的最快访问是哈希表，而不是真正的随机访问。