Jak mogę zasymulować zmienną tablicową w MySQL?

To wydaje się , że MySQL nie ma zmiennych tablicowych. Czego powinienem użyć zamiast tego?

Wydaje się, że sugerowane są dwie alternatywy: skalar typu zestawu i tabele tymczasowe . Pytanie, z którym się połączyłem, sugeruje to pierwsze. Ale czy dobrą praktyką jest używanie ich zamiast zmiennych tablicowych? Alternatywnie, jeśli pójdę z zestawami, jaki byłby odpowiednik idiomu opartego na zestawie foreach?

Cóż, używałem tymczasowych tabel zamiast zmiennych tablicowych. Nie jest to najlepsze rozwiązanie, ale działa.

Pamiętaj, że nie musisz formalnie definiować ich pól, po prostu utwórz je za pomocą SELECT:

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

(Zobacz także Tworzenie tabeli tymczasowej na podstawie instrukcji select bez użycia opcji Utwórz tabelę ).

Możesz to osiągnąć w MySQL za pomocą WHILEpętli:

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;

EDYCJA: Alternatywnie możesz to zrobić za pomocą 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
 ...
);

Spróbuj użyć funkcji FIND_IN_SET () MySql np

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

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

Uwaga: nie musisz ustawiać zmiennej w StoredProcedure, jeśli przekazujesz parametr z wartościami CSV.

W dzisiejszych czasach użycie tablicy JSON byłoby oczywistą odpowiedzią.

Ponieważ jest to stare, ale wciąż aktualne pytanie, przedstawiłem krótki przykład. Funkcje JSON są dostępne od wersji mySQL 5.7.x / MariaDB 10.2.3

Wolę to rozwiązanie od ELT (), ponieważ bardziej przypomina tablicę, a ta „tablica” może być ponownie wykorzystana w kodzie.

Ale uważaj: to (JSON) jest z pewnością znacznie wolniejsze niż użycie tabeli tymczasowej. Jest po prostu bardziej poręczny. imo.

Oto jak używać tablicy 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

A tutaj mały przykład pokazujący, jak to działa w funkcji / procedurze:

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

Nie wiem o tablicach, ale istnieje sposób na przechowywanie list oddzielonych przecinkami w normalnej kolumnie VARCHAR.

A kiedy potrzebujesz znaleźć coś na tej liście, możesz użyć funkcji FIND_IN_SET () .

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 ;

Wiem, że to trochę późna odpowiedź, ale ostatnio musiałem rozwiązać podobny problem i pomyślałem, że może to być przydatne dla innych.

tło

Rozważ poniższą tabelę zatytułowaną „mytable”:

Problem polegał na zachowaniu tylko ostatnich 3 rekordów i usunięciu wszelkich starszych rekordów, których identyfikator systemowy = 1 (w tabeli może być wiele innych rekordów z innymi wartościami systemowymi)

Byłoby dobrze, gdybyś mógł to zrobić po prostu używając instrukcji

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

Jednak nie jest to jeszcze obsługiwane w MySQL i jeśli spróbujesz tego, pojawi się błąd, taki jak

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

Potrzebne jest więc obejście, w którym tablica wartości jest przekazywana do selektora IN przy użyciu zmiennej. Jednak ponieważ zmienne muszą być pojedynczymi wartościami, musiałbym zasymulować tablicę . Sztuczka polega na utworzeniu tablicy jako listy wartości oddzielonych przecinkami (łańcuchu znaków) i przypisaniu jej do zmiennej w następujący sposób

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

Wynik przechowywany w @myvar to

5,6,7

Następnie selektor FIND_IN_SET służy do wybierania z symulowanej tablicy

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

Łączny wynik końcowy jest następujący:

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

Zdaję sobie sprawę, że to bardzo szczególny przypadek. Można go jednak zmodyfikować, aby pasował do każdego innego przypadku, w którym zmienna musi przechowywać tablicę wartości.

Mam nadzieję, że to pomoże.

Może utwórz tymczasową tabelę pamięci z kolumnami (klucz, wartość), jeśli chcesz tablice asocjacyjne. Posiadanie tablicy pamięci jest najbardziej zbliżone do posiadania tablic w mysql

Oto jak to zrobiłem.

Najpierw utworzyłem funkcję, która sprawdza, czy na liście wartości rozdzielonych przecinkami znajduje się wartość Long / Integer / cokolwiek:

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;

Teraz możesz wyszukać identyfikator na liście identyfikatorów oddzielonych przecinkami, na przykład:

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

Możesz użyć tej funkcji wewnątrz klauzuli WHERE, na przykład:

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

To był jedyny sposób, w jaki udało mi się przekazać parametr „tablica” do PROCEDURY.

Czy nie chodzi o to, aby tablice były wydajne? Jeśli po prostu przeglądasz wartości, myślę, że kursor na tymczasowej (lub stałej) tabeli ma więcej sensu niż szukanie przecinków, nie? Czystsze. Wyszukaj „mysql DECLARE CURSOR”.

W przypadku dostępu swobodnego tabela tymczasowa z indeksowanym numerycznie kluczem podstawowym. Niestety najszybszy dostęp, jaki uzyskasz, to tabela skrótów, a nie prawdziwy losowy dostęp.

Dziwię się, że żadna z odpowiedzi nie wspomina o ELT / FIELD.

ELT / FIELD działa bardzo podobnie do tablicy, zwłaszcza jeśli masz dane statyczne.

FIND_IN_SET również działa podobnie, ale nie ma wbudowanej funkcji uzupełniającej, ale łatwo ją napisać.

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('BB','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)

Działa to dobrze w przypadku listy wartości:

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;

Obie wersje korzystające z zestawów nie działały u mnie (testowane z MySQL 5.5). Funkcja ELT () zwraca cały zestaw. Biorąc pod uwagę, że instrukcja WHILE jest dostępna tylko w kontekście PROCEDURA, dodałem ją do mojego rozwiązania:

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

Szczerze mówiąc, nie sądzę, żeby to była dobra praktyka. Nawet jeśli jest to naprawdę konieczne, jest ledwo czytelne i dość powolne.

Inny sposób, aby zobaczyć ten sam problem. Mam nadzieję, że pomocna

DELIMITER $$
CREATE PROCEDURE ARR(v_value VARCHAR(100))
BEGIN

DECLARE v_tam VARCHAR(100);
DECLARE v_pos VARCHAR(100);

CREATE TEMPORARY TABLE IF NOT EXISTS split (split VARCHAR(50));

SET v_tam = (SELECT (LENGTH(v_value) - LENGTH(REPLACE(v_value,',',''))));
SET v_pos = 1;

WHILE (v_tam >= v_pos)
DO
    INSERT INTO split 
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(v_value,',',v_pos),',', -1);
    SET v_pos = v_pos + 1;
END WHILE;

SELECT * FROM split;

DROP TEMPORARY TABLE split;

END$$


CALL ARR('1006212,1006404,1003404,1006505,444,');

W wersji MYSQL po 5.7.x możesz użyć typu JSON do przechowywania tablicy. Możesz uzyskać wartość tablicy za pomocą klucza za pośrednictwem MYSQL.

Zainspirowany funkcją ELT (numer indeksu, ciąg1, ciąg2, ciąg3,…) myślę, że poniższy przykład działa jako przykład tablicy:

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

Mam nadzieję, że to pomoże.

Oto przykład dla MySQL do zapętlenia ciągu rozdzielanego przecinkami.

DECLARE v_delimited_string_access_index INT;
DECLARE v_delimited_string_access_value VARCHAR(255);
DECLARE v_can_still_find_values_in_delimited_string BOOLEAN;

SET v_can_still_find_values_in_delimited_string = true;
SET v_delimited_string_access_index = 0;
WHILE (v_can_still_find_values_in_delimited_string) DO
  SET v_delimited_string_access_value = get_from_delimiter_split_string(in_array, ',', v_delimited_string_access_index); -- get value from string
  SET v_delimited_string_access_index = v_delimited_string_access_index + 1;
  IF (v_delimited_string_access_value = '') THEN
    SET v_can_still_find_values_in_delimited_string = false; -- no value at this index, stop looping
  ELSE
    -- DO WHAT YOU WANT WITH v_delimited_string_access_value HERE
  END IF;
END WHILE;

wykorzystuje get_from_delimiter_split_stringfunkcję zdefiniowaną tutaj: https://stackoverflow.com/a/59666211/3068233

Czy zmienna tablicowa jest naprawdę potrzebna?

Pytam, ponieważ pierwotnie wylądowałem tutaj, chcąc dodać tablicę jako zmienną tabeli MySQL. Byłem stosunkowo nowy w projektowaniu baz danych i zastanawiałem się, jak to zrobić w typowym języku programowania.

Ale bazy danych są różne. Myślałem, że chcę mieć tablicę jako zmienną, ale okazuje się, że nie jest to powszechna praktyka bazodanowa MySQL.

Standardowa praktyka

Alternatywnym rozwiązaniem dla tablic jest dodanie dodatkowej tabeli, a następnie odniesienie do oryginalnej tabeli za pomocą klucza obcego.

Jako przykład wyobraźmy sobie aplikację, która śledzi wszystkie przedmioty, które każda osoba w gospodarstwie domowym chce kupić w sklepie.

Polecenia do tworzenia tabeli, które pierwotnie wyobrażałem, wyglądałyby mniej więcej tak:

#doesn't work
CREATE TABLE Person(
  name VARCHAR(50) PRIMARY KEY
  buy_list ARRAY
);

Myślę, że wyobraziłem sobie, że buy_list będzie ciągiem pozycji oddzielonych przecinkami lub czymś w tym rodzaju.

Ale MySQL nie ma pola typu tablicy, więc naprawdę potrzebowałem czegoś takiego:

CREATE TABLE Person(
  name VARCHAR(50) PRIMARY KEY
);
CREATE TABLE BuyList(
  person VARCHAR(50),
  item VARCHAR(50),
  PRIMARY KEY (person, item),
  CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES Person(name)
);

Tutaj definiujemy ograniczenie o nazwie fk_person. Mówi, że pole „osoba” w BuyList jest kluczem obcym. Innymi słowy, jest to klucz podstawowy w innej tabeli, a konkretnie w polu „imię” w tabeli Person, co oznacza REFERENCES.

Zdefiniowaliśmy również kombinację osoby i przedmiotu jako klucz podstawowy, ale technicznie nie jest to konieczne.

Wreszcie, jeśli chcesz uzyskać wszystkie pozycje z listy osoby, możesz uruchomić to zapytanie:

SELECT item FROM BuyList WHERE person='John';

To daje wszystkie pozycje na liście Johna. Nie potrzeba żadnych tablic!

Jeśli mamy jeden taki stół

mysql> select * from user_mail;
+------------+-------+
| email      | user | 
+------------+-------+-
| email1@gmail |     1 | 
| email2@gmail |     2 |
+------------+-------+--------+------------+

i tablica tablicowa:

mysql> select * from user_mail_array;
+------------+-------+-------------+
| email      | user | preferences |
+------------+-------+-------------+
| email1@gmail |     1 |           1 |
| email1@gmail |     1 |           2 |
| email1@gmail |     1 |           3 |
| email1@gmail |     1 |           4 |
| email2@gmail |     2 |           5 |
| email2@gmail |     2 |           6 |

Możemy wybrać wiersze drugiej tabeli jako jedną tablicę za pomocą funkcji CONCAT:

mysql> SELECT t1.*, GROUP_CONCAT(t2.preferences) AS preferences
     FROM user_mail t1,user_mail_array t2
       where t1.email=t2.email and t1.user=t2.user
     GROUP BY t1.email,t1.user;

+------------+-------+--------+------------+-------------+
| email      | user | preferences |
+------------+-------+--------+------------+-------------+
|email1@gmail |     1 | 1,3,2,4     |
|email2@gmail |     2 | 5,6         |
+------------+-------+--------+------------+-------------+

Myślę, że mogę poprawić tę odpowiedź. Spróbuj tego:

Parametr „Pranks” to plik CSV. to znaczy. „1, 2, 3, 4… itd.”

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;

Ta metoda sprawia, że ​​szukany ciąg wartości CSV jest stopniowo krótszy z każdą iteracją pętli, co moim zdaniem byłoby lepsze do optymalizacji.

Spróbowałbym czegoś takiego dla wielu kolekcji. Jestem początkującym użytkownikiem MySQL. Przepraszamy za nazwy funkcji, nie mogę się zdecydować, które nazwy będą najlepsze.

delimiter //

drop  procedure init_
//
create procedure init_()
begin
  CREATE TEMPORARY TABLE if not exists 
    val_store(  
    realm  varchar(30) 
    ,  id  varchar(30) 
    ,  val   varchar(255) 
    ,  primary key ( realm , id )
    );
end;
//

drop function if exists get_
//
create function get_( p_realm varchar(30) , p_id varchar(30) )
  returns varchar(255)
  reads sql data
begin 
  declare ret_val varchar(255);
  declare continue handler for 1146 set ret_val = null;
  select val into ret_val from val_store where id = p_id;
  return ret_val;
end;
//

drop procedure if exists set_
//
create procedure set_( p_realm varchar(30) , p_id varchar(30) , p_val varchar(255) )
begin
  call init_(); 
  insert into val_store (realm,id,val) values (p_realm , p_id , p_val) on duplicate key update val = p_val;
end;
//

drop   procedure if exists remove_
//
create procedure remove_( p_realm varchar(30) , p_id varchar(30) )
begin
  call init_();
  delete from val_store where realm = p_realm and id = p_id;
end;
//

drop   procedure if exists erase_
//
create procedure erase_( p_realm varchar(30) ) 
begin
  call init_();
  delete from val_store where realm = p_realm;
end;
//

call set_('my_array_table_name','my_key','my_value');

select get_('my_array_table_name','my_key');

Zamiast zapisywać dane jako tablicę lub tylko w jednym wierszu, powinieneś tworzyć różne wiersze dla każdej otrzymanej wartości. To znacznie ułatwi zrozumienie, zamiast składać wszystko razem.

Czy próbowałeś użyć serialize () PHP? Pozwala to na przechowywanie zawartości tablicy zmiennej w postaci ciągu znaków zrozumiałych dla PHP i jest bezpieczne dla bazy danych (zakładając, że najpierw go uciekłeś).

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

Możesz również zrobić dokładnie to samo bez tablicy numerowanej

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

lub

$array3 = array(
    'somethingNew'
);