Como posso simular uma variável array no MySQL?

Parece que o MySQL não possui variables ​​de array. O que devo usar em vez disso?


Parece haver duas alternativas sugeridas: Uma tabela escalar e temporária do tipo set . A questão a que me liguei sugere a primeira. Mas é uma boa prática usá-los em vez de variables ​​de matriz? Alternativamente, se eu for com conjuntos, qual seria o idioma baseado em conjunto equivalente a foreach ?

Bem, eu tenho usado tabelas temporárias em vez de variables ​​de array. Não é a melhor solução, mas funciona.

Note que você não precisa definir formalmente seus campos, basta criá-los usando um SELECT:

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

(Consulte também Criar tabela temporária a partir da instrução select sem usar Criar tabela .)

Você pode conseguir isso no MySQL usando o loop 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; 

EDIT: Alternativamente, você pode fazê-lo usando 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 ... ); 

Tente usar a function FIND_IN_SET () do MySql, por exemplo

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

Nota: Você não precisa configurar a variável em StoredProcedure se estiver passando o parâmetro com valores CSV.

Não sei sobre os arrays, mas há uma maneira de armazenar listas separadas por vírgula na coluna VARCHAR normal.

E quando você precisar encontrar algo nessa lista, você pode usar a function FIND_IN_SET () .

Aqui está como eu fiz isso.

Primeiro, criei uma function que verifica se um valor Long / Integer / whatever está em uma lista de valores separados por vírgulas:

 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; 

Então agora você pode procurar por um ID em uma lista de IDs separados por vírgulas, assim:

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

E você pode usar essa function dentro de uma cláusula WHERE, assim:

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

Esta foi a única maneira que encontrei para passar um parâmetro “array” para um PROCEDIMENTO.

 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 ; 

Talvez crie uma tabela de memory temporária com colunas (chave, valor) se você quiser matrizes associativas. Ter uma tabela de memory é a coisa mais próxima de ter matrizes no mysql

Hoje em dia, usar um array JSON seria uma resposta óbvia.

Como essa é uma questão antiga, mas ainda relevante, eu produzi um breve exemplo. Funções JSON estão disponíveis desde mySQL 5.7.x / MariaDB 10.2.3

Eu prefiro esta solução sobre ELT () porque é realmente mais como uma matriz e este ‘array’ pode ser reutilizado no código.

Mas tenha cuidado: é certamente muito mais lento do que usar uma mesa temporária. É apenas mais útil. imo.

Aqui está como usar um 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 

E aqui um pequeno exemplo para mostrar como funciona em uma function / procedimento:

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

Isso funciona bem para a lista de valores:

 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; 

Ambas as versões usando conjuntos não funcionaram para mim (testadas com o MySQL 5.5). A function ELT () retorna o conjunto inteiro. Considerando que a instrução WHILE só está disponível no contexto do PROCEDURE, eu a adicionei à minha solução:

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

Para ser honesto, não acho que seja uma boa prática. Mesmo que seja realmente necessário, isso é dificilmente legível e bastante lento.

Na versão MYSQL após 5.7.x, você pode usar o tipo JSON para armazenar uma matriz. Você pode obter o valor de um array por uma chave via MYSQL.

Inspirado pela function ELT (número de índice, string1, string2, string3,…), acho que o exemplo a seguir funciona como um exemplo de matriz:

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

Espero que ajude.

Eu sei que esta é uma resposta tardia, mas recentemente tive que resolver um problema semelhante e pensei que isso poderia ser útil para os outros.

fundo

Considere a tabela abaixo chamada ‘mytable’:

Mesa de partida

O problema era manter apenas 3 registros mais recentes e excluir todos os registros antigos cujo systemid = 1 (pode haver muitos outros registros na tabela com outros valores de systemid)

Seria bom você poder fazer isso simplesmente usando a declaração

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

No entanto, isso ainda não é suportado no MySQL e se você tentar isso, então você receberá um erro como

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

Portanto, uma solução alternativa é necessária para que uma matriz de valores seja passada para o seletor IN usando a variável. No entanto, como as variables ​​precisam ser valores únicos, eu precisaria simular uma matriz . O truque é criar a matriz como uma lista de valores separados por vírgula (string) e atribuí-la à variável da seguinte forma

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

O resultado armazenado em @myvar é

5,6,7

Em seguida, o seletor FIND_IN_SET é usado para selecionar a matriz simulada

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

O resultado final combinado é o seguinte:

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

Estou ciente de que este é um caso muito específico. No entanto, ele pode ser modificado para se adequar a qualquer outro caso em que uma variável precise armazenar uma matriz de valores.

Espero que isso ajude.

Eu acho que posso melhorar esta resposta. Tente isto:

O parâmetro ‘Pranks’ é um 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; 

Esse método faz com que a string pesquisada de valores de CSV fique progressivamente mais curta a cada iteração do loop, o que, acredito, seria melhor para otimização.

Você já tentou usar o serialize () do PHP? Isso permite que você armazene o conteúdo do array de uma variável em uma string que o PHP entende e é seguro para o database (supondo que você tenha escapado primeiro).

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

Você também pode fazer exatamente o mesmo sem uma matriz numerada

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

ou

 $array3 = array( 'somethingNew' );