¿Cómo puedo simular una variable de matriz en MySQL?

Parece que MySQL no tiene variables de matriz. ¿Qué debería usar en su lugar?


Parece haber dos alternativas sugeridas: un escalar de tipo conjunto y tablas temporales . La pregunta a la que me he vinculado sugiere la primera. ¿Pero es una buena práctica usar estas variables en lugar de las variables de matriz? Alternativamente, si voy con sets, ¿cuál sería el modismo basado en conjunto equivalente a foreach ?

Bueno, he estado usando tablas temporales en lugar de variables de matriz. No es la mejor solución, pero funciona.

Tenga en cuenta que no necesita definir formalmente sus campos, simplemente créelos usando un SELECT:

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

(Consulte también Crear tabla temporal a partir de una statement seleccionada sin usar Crear tabla ).

Puede lograr esto en MySQL utilizando el bucle 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; 

EDITAR: Alternativamente puedes hacerlo 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 ... ); 

Intente usar la función FIND_IN_SET () de MySql, por ejemplo

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

Nota: No es necesario configurar la variable en StoredProcedure si está pasando parámetros con valores CSV.

No sé sobre las matrices, pero hay una manera de almacenar listas separadas por comas en la columna VARCHAR normal.

Y cuando necesite encontrar algo en esa lista, puede usar la función FIND_IN_SET () .

Así es como lo hice.

Primero, creé una función que verifica si un valor Long / Integer / whatever está en una lista de valores separados por comas:

 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; 

Entonces, ahora puede buscar una ID en una lista de ID separada por comas, como esta:

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

Y puede usar esta función dentro de una cláusula WHERE, como esta:

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

Esta fue la única forma que encontré para pasar un parámetro de “matriz” a un PROCEDIMIENTO.

 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 ; 

Tal vez cree una tabla de memoria temporal con columnas (clave, valor) si desea matrices asociativas. Tener una tabla de memoria es lo más parecido a tener matrices en mysql

Hoy en día, usar una matriz JSON sería una respuesta obvia.

Dado que esta es una pregunta antigua pero relevante, he producido un pequeño ejemplo. Las funciones JSON están disponibles desde mySQL 5.7.x / MariaDB 10.2.3

Prefiero esta solución sobre ELT () porque realmente se parece más a una matriz y esta ‘matriz’ se puede reutilizar en el código.

Pero ten cuidado: es mucho más lento que utilizar una tabla temporal. Es simplemente más útil. imo.

Aquí es cómo usar una matriz 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 

Y aquí un pequeño ejemplo para mostrar cómo funciona en una función / procedimiento:

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

Esto funciona bien para la 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 versiones que usaban sets no funcionaban para mí (probado con MySQL 5.5). La función ELT () devuelve todo el conjunto. Teniendo en cuenta que la statement WHILE solo está disponible en el contexto PROCEDURE, la agregué a mi solución:

 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, no creo que esta sea una buena práctica. Incluso si es realmente necesario, esto es apenas legible y bastante lento.

En la versión MYSQL después de 5.7.x, puede usar el tipo JSON para almacenar una matriz. Puede obtener el valor de una matriz mediante una clave a través de MYSQL.

Inspirado por la función ELT (número de índice, cadena1, cadena2, cadena3, …), creo que el siguiente ejemplo funciona como un ejemplo 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 ayude

Sé que esta es una respuesta tardía, pero recientemente tuve que resolver un problema similar y pensé que esto podría ser útil para otros.

Fondo

Considere la siguiente tabla llamada ‘mytable’:

Mesa de inicio

El problema era mantener solo los últimos 3 registros y eliminar cualquier registro anterior cuyo systemid = 1 (podría haber muchos otros registros en la tabla con otros valores de systemid)

Sería bueno que pudieras hacer esto simplemente usando la statement

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

Sin embargo, esto aún no es compatible con MySQL y si intenta esto, obtendrá un error como

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

Por lo tanto, se necesita una solución alternativa mediante la cual se transfiere una matriz de valores al selector IN utilizando la variable. Sin embargo, como las variables deben ser valores únicos, necesitaría simular una matriz . El truco es crear la matriz como una lista de valores separados por comas (cadena) y asignar esto a la variable de la siguiente manera

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

El resultado almacenado en @myvar es

5,6,7

A continuación, el selector FIND_IN_SET se usa para seleccionar de la matriz simulada

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

El resultado final combinado es el siguiente:

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

Soy consciente de que este es un caso muy específico. Sin embargo, se puede modificar para adaptarse a cualquier otro caso en el que una variable necesite almacenar una matriz de valores.

Espero que esto ayude.

Creo que puedo mejorar esta respuesta. Prueba esto:

El parámetro ‘Bromas’ es un CSV. es decir. ‘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; 

Este método hace que la cadena de valores CSV buscada sea progresivamente más corta con cada iteración del ciclo, lo que creo que sería mejor para la optimización.

¿Has intentado utilizar la serialización de PHP ()? Eso le permite almacenar los contenidos de la matriz de una variable en una cadena que PHP entiende y es segura para la base de datos (suponiendo que haya escapado primero).

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

También puede hacer exactamente lo mismo sin una matriz numerada

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

o

 $array3 = array( 'somethingNew' ); 
    Intereting Posts