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

php send array to mysql stored procedure

Question

appears 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 ?

Answer 1

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

Answer 2

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 declaración 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.

Answer 3

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.

Answer 4

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 declaración seleccionada sin usar Crear tabla ).

Answer 5

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.

Answer 6

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.

Answer 7

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;

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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

Answer 12

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

Answer 13

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

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 declaración

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.

Answer 14

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

Answer 15

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 ;