How can I simulate an array variable in MySQL?

它的 出现了，MySQL 没有数组变量。我应该用什么来代替？

似乎有两个选择建议: 一个 集合型标量集合型标量和 临时桌子临时桌子临时桌子。我提出的问题暗示了前者。但是，使用这些变量而不是数组变量是一种好的做法吗？或者，如果我使用集合，那么基于集合的习惯用法相当于 foreach是什么？

418235 次浏览
小开

您可以在 MySQL 中使用 WHILE循环来实现这一点:

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;

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

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

当您需要在该列表中查找某些内容时，可以使用 FIND _ IN _ SET ()函数。

小开
最佳答案

我一直在使用临时表而不是数组变量，这不是最好的解决方案，但是很有效。

注意，您不需要正式定义它们的字段，只需使用 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 Table 从 select 语句创建临时表.)

小开

Have you tried using PHP's 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());

您也可以在不使用编号数组的情况下进行相同的操作

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

或者

$array3 = array(
'somethingNew'
);
小开

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

小开

对于值列表，这种方法可以很好地工作:

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;
小开

使用 set 的两个版本都不适合我(使用 MySQL 5.5进行测试)。函数 ELT ()返回整个集合。考虑到 WHILE 语句只能在 PROCEDURE 上下文中使用，我将其添加到我的解决方案中:

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

说实话，我觉得这不是个好习惯。即使它真的有必要，这是几乎不可读和相当缓慢。

小开

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

参数‘ Pranks’是一个 CSV。即‘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;

这种方法使得搜索到的 CSV 值的字符串随着循环的每次迭代而逐渐变短，我相信这对于优化是更好的。

小开

我是这么做的。

首先，我创建了一个函数来检查 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;

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

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

这是我发现的向 PROCEDURE 传递“ array”参数的唯一方法。

小开 
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 ;
小开

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

小开

尝试使用 MySql 的 FIND _ IN _ SET ()函数 例如:。

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


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

注意: 如果使用 CSV 值传递参数，则不必在 StoredProcess 中使用 SET 变量。

小开

受到函数 ELT (索引号，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;

希望能有帮助。

小开

Nowadays using a JSON 数组 would be an obvious answer.

由于这是一个古老但仍然相关的问题，我举了一个简短的例子。 从 mySQL 5.7.x/MariaDB 10.2.3开始，JSON 函数就可用了

与 ELT ()相比，我更喜欢这个解决方案，因为它实际上更像一个数组，并且这个“数组”可以在代码中重用。

但是要小心: 它(JSON)当然比使用临时表慢得多。

下面是如何使用 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();
小开

我知道这是一个有点晚的反应，但我最近解决了一个类似的问题，并认为这可能对其他人有用。

Background

看看下面的表“ mytable”:

Starting table

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

如果您可以使用这个语句来完成这项工作，那就太好了

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

然而，这在 MySQL 中还不支持，如果你尝试这样做，你会得到一个错误，如

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

因此，需要使用变量将值数组传递给 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);

存储在@myvar 中的结果是

五，六，七

接下来，对 select from the simulated array使用 FIND _ IN _ SET 选择器

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

综合最后结果如下:

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

我知道这是一个非常特殊的案子。但是，可以对其进行修改，以适应变量需要存储值数组的任何其他情况。

我希望这能有所帮助。

小开

数组的意义不就是高效吗？如果您只是迭代值，我认为临时(或永久)表上的游标比寻找逗号更有意义，不是吗？也更干净。查找“ mysql 声明游标”。

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

小开

我很惊讶没有一个答案提到 ELT/FIELD。

ELT/FIELD 的工作方式非常类似于数组，特别是在有静态数据的情况下。

FIND _ IN _ SET 的工作原理也类似，但没有内置的补充 函数，但是编写一个函数很容易。

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('BB','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)
小开

我会在多个收藏品中尝试类似的东西。我是 MySQL 初学者。对于函数名，我很抱歉，我不能决定哪个名字最好。

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

从另一个角度来看同样的问题。 希望有用

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 >= 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,');
小开

下面是 MySQL 循环通过逗号分隔字符串的示例。

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

小开

与其将数据保存为数组或仅保存在一行中，不如为每个接收到的值创建不同的行。这将使理解起来更加简单，而不是将所有内容放在一起。

小开

真的有必要使用数组变量吗？

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.

但数据库是不同的。我想要一个数组作为一个变量，但结果是这不是一个常见的 MySQL 数据库实践。

Standard Practice

数组的替代解决方案是添加一个额外的表，然后使用外键引用原始表。

作为一个例子，让我们想象一个应用程序，它跟踪家庭中每个人都想在商店购买的所有商品。

创建表的命令如下所示:

#doesn't work
CREATE TABLE Person(
name VARCHAR(50) PRIMARY KEY
buy_list ARRAY
);

我想我设想的 buy _ list 是一个以逗号分隔的项目字符串或类似的东西。

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

这里我们定义了一个名为 fk _ person 的约束。它说 BuyList 中的“ person”字段是一个外键。换句话说，它是另一个表中的主键，特别是 Person 表中的“ name”字段，这就是 REFERENCES 所表示的。

我们还将 person 和 item 的组合定义为主键，但是从技术上讲，这是不必要的。

最后，如果您想获取某人列表中的所有项目，可以运行以下查询:

SELECT item FROM BuyList WHERE person='John';

这样就给出了 John 列表中的所有项目。不需要数组！

小开

如果我们有一张那样的桌子

mysql> select * from user_mail;
+------------+-------+
| email      | user |
+------------+-------+-
| email1@gmail |     1 |
| email2@gmail |     2 |
+------------+-------+--------+------------+

以及数组表:

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

我们可以使用 CONCAT 函数选择第二个表的行作为一个数组:

mysql> 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         |
+------------+-------+--------+------------+-------------+
小开

这是我使用包含元素列表的变量的解决方案。 您可以在简单查询中使用它(不需要使用存储过程或创建表)。

I found somewhere else on the site the trick to use the JSON_TABLE function (it works in mysql 8, I dunno of it works in other versions).

set @x = '1,2,3,4' ;


select c.NAME
from colors c
where
c.COD in (
select *
from json_table(
concat('[',@x,']'),
'$[*]' columns (id int path '$') ) t ) ;

此外，您可能需要管理一个或多个变量设置为 Void _ string 空字符串的情况。 在本例中，我添加了另一个技巧(即使 X、 嘿或两个 X 和 y都是空字符串，查询也不返回错误) :

set @x = '' ;
set @y = 'yellow' ;


select c.NAME
from colors
where
if(@y = '', 1 = 1, c.NAME = @y)
and if(@x = '', 1, c.COD) in (
select *
from json_table(
concat('[',if(@x = '', 1, @x),']'),
'$[*]' columns (id int path '$') ) t) ;

提交答案