wake-up-neo.com

Wie kann ich eine Array-Variable in MySQL simulieren?

Erscheint , dass MySQL keine Array-Variablen hat. Was soll ich stattdessen verwenden? 


Es scheint zwei Alternativen zu geben: Ein Set-Typ-Skalar und temporäre Tabellen. Die Frage, auf die ich verwiesen habe, schlägt die erstere vor. Aber ist es eine gute Praxis, diese anstelle von Array-Variablen zu verwenden? Wenn ich mit Sets gehe, was wäre dann das set-based Idiom, das zu foreach äquivalent ist? 

63
einpoklum

Nun, ich habe temporäre Tabellen anstelle von Array-Variablen verwendet. Nicht die beste Lösung, aber es funktioniert.

Beachten Sie, dass Sie die Felder nicht formal definieren müssen. Erstellen Sie sie einfach mit SELECT:

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

(Siehe auch Temporäre Tabelle aus select-Anweisung erstellen, ohne Create Table zu verwenden.)

61
einpoklum

Sie können dies in MySQL mit WHILE loop erreichen:

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;

BEARBEITEN: Alternativ können Sie es auch mit UNION ALL tun:

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

Versuchen Sie es mit der Funktion FIND_IN_SET () von MySql

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

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

Anmerkung: In StoredProcedure müssen Sie keine Variable setzen, wenn Sie Parameter mit CSV-Werten übergeben.

16
Himalaya Garg

Sie kennen keine Arrays, aber es gibt eine Möglichkeit, durch Kommas getrennte Listen in der normalen VARCHAR-Spalte zu speichern.

Wenn Sie etwas in dieser Liste suchen müssen, können Sie die Funktion FIND_IN_SET () verwenden.

13
wormhit

Heutzutage wäre die Verwendung eines JSON-Arrays eine naheliegende Antwort.

Da dies eine alte, aber immer noch relevante Frage ist, habe ich ein kurzes Beispiel erstellt. JSON-Funktionen stehen seit mySQL 5.7.x/MariaDB 10.2.3 zur Verfügung

Ich bevorzuge diese Lösung gegenüber ELT (), weil sie eigentlich eher einem Array entspricht und dieses 'Array' im Code wiederverwendet werden kann.

Aber seien Sie vorsichtig: Es (JSON) ist sicherlich viel langsamer als eine temporäre Tabelle. Es ist nur praktischer. imo.

So verwenden Sie ein JSON-Array:

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

Und hier ein kleines Beispiel, um zu zeigen, wie es in einer Funktion/Prozedur funktioniert:

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

Erstellen Sie möglicherweise eine temporäre Speichertabelle mit Spalten (Schlüssel, Wert), wenn Sie assoziative Arrays wünschen. Eine Speichertabelle ist das, was Arrays in mysql am nächsten kommt

3
Pavle Lekic

So habe ich es gemacht.

Zuerst habe ich eine Funktion erstellt, die prüft, ob ein Long/Integer/welcher Wert in einer durch Kommas getrennten Werteliste enthalten ist:

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 können Sie jetzt in einer durch Kommas getrennten Liste von IDs nach einer ID suchen:

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

Und Sie können diese Funktion innerhalb einer WHERE-Klausel wie folgt verwenden:

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

Nur so habe ich einen "Array" -Parameter an einen PROCEDURE übergeben.

3
chuckedw
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 ;
3
Sagar Gangwal

Dies funktioniert gut für eine Liste von Werten:

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;
1
user2664904

Ist der Punkt von Arrays nicht effizient? Wenn Sie nur durch Werte iterieren, ist ein Cursor auf einer temporären (oder permanenten) Tabelle für mich sinnvoller als das Suchen nach Kommas. Auch sauberer. Nachschlagen "mysql DECLARE CURSOR".

Für den Direktzugriff eine temporäre Tabelle mit einem numerisch indizierten Primärschlüssel. Leider ist der schnellste Zugriff, den Sie erhalten, eine Hashtabelle, kein richtiger Direktzugriff.

1
Amaigus

Beide Versionen mit Sets funktionierten für mich nicht (getestet mit MySQL 5.5). Die Funktion ELT () gibt den gesamten Satz zurück. Da die WHILE-Anweisung nur im PROCEDURE-Kontext verfügbar ist, habe ich sie meiner Lösung hinzugefügt:

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

Um ehrlich zu sein, denke ich nicht, dass dies eine gute Praxis ist. Auch wenn es wirklich notwendig ist, ist dies kaum lesbar und ziemlich langsam. 

1
jmmeier

Ich bin überrascht, dass in keiner der Antworten ELT/FIELD erwähnt wird.

ELT/FIELD arbeitet einem Array sehr ähnlich, insbesondere wenn Sie statische Daten haben.

FIND_IN_SET funktioniert auch ähnlich, hat jedoch keine integrierte Funktion, es ist jedoch leicht genug, eine zu schreiben.

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('CC','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)
0
Jongab

Ich weiß, dass dies eine späte Antwort ist, aber ich musste kürzlich ein ähnliches Problem lösen und dachte, dass dies für andere nützlich sein könnte.

Hintergrund

Betrachten Sie die folgende Tabelle mit dem Namen 'mytable':

 Starting table

Das Problem bestand darin, nur die letzten 3 Datensätze zu behalten und ältere Datensätze zu löschen, deren System-ID = 1 ist (in der Tabelle könnten viele andere Datensätze mit anderen System-ID-Werten vorhanden sein).

Es wäre gut, wenn Sie dies einfach mit der Anweisung tun könnten 

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

Dies wird jedoch in MySQL noch nicht unterstützt. Wenn Sie dies versuchen, erhalten Sie eine Fehlermeldung wie 

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

Daher ist eine Problemumgehung erforderlich, bei der ein Array von Werten mithilfe der Variablen an den IN-Selektor übergeben wird. Da Variablen jedoch Einzelwerte sein müssen, müsste ich ein Array simulieren. Der Trick besteht darin, das Array als durch Kommas getrennte Liste von Werten (Zeichenfolge) zu erstellen und diese der Variablen wie folgt zuzuweisen

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

Das in @myvar gespeicherte Ergebnis lautet

5,6,7

Als nächstes wird der FIND_IN_SET-Selektor verwendet, um aus dem simulierten Array auszuwählen

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

Das kombinierte Endergebnis lautet wie folgt:

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

Mir ist bewusst, dass dies ein sehr konkreter Fall ist. Sie kann jedoch für jeden anderen Fall angepasst werden, in dem eine Variable ein Array von Werten speichern muss. 

Ich hoffe, dass das hilft.

0
Clinton

Inspiriert von der Funktion ELT (Indexnummer, String1, String2, String3,…), denke ich, dass das folgende Beispiel als Array-Beispiel funktioniert:

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

Ich hoffe es hilft.

0
edward

In MYSQL-Version nach 5.7.x können Sie einen JSON-Typ zum Speichern eines Arrays verwenden. Sie können den Wert eines Arrays durch einen Schlüssel über MYSQL abrufen.

0
nick darn