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?
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.)
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
...
);
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.
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.
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();
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
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.
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 ;
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;
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.
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.
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)
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':
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.
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.
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.