Comment puis-je simuler une variable tableau dans MySQL?

Il apparaît que MySQL n'a pas de variables de tableau. Que dois-je utiliser à la place?

Il semble y avoir deux solutions proposées: Une jeu de type scalaire et tables temporaires. La question que je me suis lié d'suggère l'ancien. Mais il est de bonne pratique d'utiliser ces au lieu de variables de tableau? Sinon, si je pars avec des ensembles, ce qui serait la base de jeu de l'idiome équivalent à foreach?

Utiliser les ELT.
Pouvez-vous lier à un niveau plus détaillé de la discussion de l'ELT, peut-être avec un plus exemple?

InformationsquelleAutor einpoklum | 2012-08-29

62

Bien, j'ai été en utilisant les tables temporaires au lieu de variables de type tableau. Pas la plus grande solution, mais il fonctionne.

Notez que vous n'avez pas besoin de définir formellement leurs champs, il suffit de créer entre eux à l'aide d'un SELECT:
```
CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table
SELECT first_name FROM people WHERE last_name = 'Smith';
```
(Voir aussi Créer une table temporaire d'instruction select sans l'aide de Create Table.)
- Ohhh 😮 je ne savais pas SQL a cela!! Les tables ne sont vivants pour la portée de al requêtes en cours d'exécution. NEAT!
- C'est à la condition de ne pas réutiliser la connexion. Car en effet, il durera pour la ensemble session.
- Eh bien, vous pouvez supprimer la table temporaire...
- Et vous ne pouvez pas réutiliser une table temporaire. Il n'est donc pas très utile.
- Comment voulez-vous dire?
- href="https://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html" >dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
- Ouais, eh bien, vous peut le réutiliser, mais pas dans la même requête.
- Non, malheureusement, ça ne fonctionne pas pour moi. J'ai essayé les deux requêtes distinctes, qui a été le point de faire une table temporaire. L'autre partie drôle pour moi, est que l'utilisateur doit disposer d'insérer des droits sur la base de données pour une table temporaire, donc, le tout est d'une grande dispute pour apparemment aucun avantage (pour moi). J'ai fini juste de faire une table normale.
- Dans les 2 requêtes distinctes, il devrait fonctionner...
- Il y a une réponse, ce qui suggère que.
InformationsquelleAutor einpoklum
36

Vous pouvez atteindre cet objectif dans MySQL à l'aide de WHILE boucle:
```
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;
```
EDIT:
Sinon, vous pouvez le faire en utilisant 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
 ...
);
```
- Ne sont pas des boucles possible que dans des procédures stockées?
- oui à droite, il est possible à l'intérieur des procédures stockées, des fonctions et déclencheurs.
- Donc je ne peux pas utiliser le code que vous avez fourni... j'ai besoin de quelque chose qui est applicable de manière plus générale.
- Vous pouvez écrire un exemple de procédure stockée et CALL il.
- Mais c'est seulement pour code fixe à l'aide de la matrice.
- Je ne pense pas que les tableaux sont nécessaires. Vous pouvez facilement le faire en utilisant les tables temporaires ou UNION ALL sans l'aide de la procédure.
- Pouvez-vous s'il vous plaît écrire une autre réponse de démonstration de l'utilisation de l'UNION de TOUS dans ce contexte?
- Ya bien sûr. Voir mise à jour de réponse, il est semblable à tableau à deux dimensions.
- Votre méthode de faire il est très indirecte. Pourquoi ne pas simplement de la boucle ELT(x, @myArrayOfValue); jusqu'à ce que vous obtenez null?
InformationsquelleAutor Omesh
19

Essayez d'utiliser FIND_IN_SET() la fonction de MySql
par exemple,
```
SET @c = 'xxx,yyy,zzz';

SELECT * from countries 
WHERE FIND_IN_SET(countryname,@c);
```
Remarque: Vous n'avez pas à DÉFINIR la variable dans StoredProcedure si vous êtes de passage de paramètre avec CSV valeurs.
- Méfiez-vous des limites de longueur, qui peut être assez faible: stackoverflow.com/q/2567000/1333493
InformationsquelleAutor Himalaya Garg
15

Ne sais pas sur les tableaux, mais il est un moyen de stocker séparées par des virgules listes normal de la colonne de type VARCHAR.

Et quand vous avez besoin de trouver quelque chose dans cette liste, vous pouvez utiliser le FIND_IN_SET() fonction.
- Si je veux trouver un sous-ensemble d'un ensemble, est-il de toute façon?
- Désolé! Je ne suis pas sûr que c'est possible.
- Vous avez la meilleure solution
InformationsquelleAutor wormhit

De nos jours à l'aide d'un tableau JSON serait une réponse évidente.

Puisque c'est un vieux mais toujours pertinent de se demander, j'ai réalisé un court exemple.
JSON fonctions sont disponibles depuis mySQL 5.7.x /MariaDB 10.2.3

Je préfère cette solution plutôt que l'ELT() parce que c'est vraiment plus comme un tableau et ce "tableau" peut être réutilisé dans le code.

Mais attention: Il (JSON) est certainement beaucoup plus lent que d'utiliser une table temporaire. Son juste plus pratique. l'omi.

Ici est de savoir comment utiliser un tableau 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

Et voici un petit exemple pour montrer comment cela fonctionne dans une fonction/procédure:

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

Vous dites ELT est plus lent ou JSON est plus lent ?
Sugumar: Au moment de la rédaction JSON est certainement plus lent. J'ai édité la réponse à clarifier cela.

InformationsquelleAutor SeparateReality

4
```
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 ;
```
- Veuillez expliquer comment ce code doit être utilisé et comment il répond à la question.
- Comme ici, vous rendre procédure simple qui donne un par un élément de cette chaîne particulière qui travail en tant que tableau dans oracle.
- Oracle? Cette question n'est pas sur Oracle. Aussi, il semble que vous êtes à la définition d'un tableau à l'intérieur de la procédure.
- Veuillez vérifier Syntex c'est seulement pour mysql
- Ne manquez pas la dernière virgule!
InformationsquelleAutor Sagar Gangwal
3

Peut-être créer une mémoire temporaire table avec des colonnes (clé, valeur) si vous voulez des tableaux associatifs. Avoir une table mémoire est la chose la plus proche pour avoir des tableaux dans mysql
- Umm, je ne veux pas de tableaux associatifs, juste des tableaux.
- vous pouvez utiliser une mémoire temporaire de la table avec une seule colonne et ensuite une boucle à travers les valeurs à l'aide des curseurs, qui est la chose la plus proche à l'aide de tableaux et/boucles while dans un non-langage de programmation déclarative
- La langue a en fait cette fonction, c'est à dire, il n'y a pas syntaxique raison pour laquelle vous ne devriez pas être en mesure de sélectionner un vecteur dans une variable comme vous sélectionnez un scalaire en elle.
InformationsquelleAutor Pavle Lekic

Voici comment je l'ai fait.

Tout d'abord, j'ai créé une fonction qui vérifie si une Longue/Integer/quelle que soit la valeur est une liste de valeurs séparées par des virgules:

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;

Alors maintenant, vous pouvez rechercher un ID dans une liste séparée par des virgules des Id, comme ceci:

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

Et vous pouvez utiliser cette fonction à l'intérieur d'une clause where, comme ceci:

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

C'était le seul moyen que j'ai trouvé pour passer un "tableau" de paramètre à une PROCÉDURE.

InformationsquelleAutor chuckedw

Cela fonctionne très bien pour la liste de valeurs:

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;

InformationsquelleAutor user2664904

Les deux versions à l'aide de jeux ne fonctionne pas pour moi (testé avec MySQL 5.5). La fonction ELT() retourne l'ensemble. Considérant l'instruction WHILE est uniquement disponible dans le contexte de la PROCÉDURE je l'ai ajouté à ma solution:

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

Pour être honnête, je ne pense pas que c'est une bonne pratique. Même si ses vraiment nécessaire, c'est à peine lisible et assez lente.

InformationsquelleAutor jmmeier

1

Je sais que c'est un peu une réponse tardive, mais j'ai récemment eu à résoudre un problème semblable et de la pensée que cela pourrait être utile à d'autres.

Fond

Considérer le tableau ci-après appelé 'mytable':

Le problème était de ne garder que les 3 dossiers et de supprimer tous les vieux dossiers, dont idsystème=1 (il pourrait y avoir de nombreux autres enregistrements dans la table avec d'autres idsystème valeurs)

Il serait bon est vous pouvez le faire simplement en utilisant l'instruction
```
DELETE FROM mytable WHERE id IN (SELECT id FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3)
```
Cependant, ce n'est pas encore prise en charge dans MySQL et si vous essayez cela, vous obtiendrez un message d'erreur comme
```
...doesn't yet support 'LIMIT & IN/ALL/SOME subquery'
```
Donc une solution de contournement est nécessaire en vertu de laquelle un tableau de valeurs est passé à la DANS le sélecteur à l'aide de la variable. Cependant, comme les variables doivent être des valeurs à un seul, j'aurais besoin de simuler un tableau. L'astuce consiste à créer le tableau comme une liste séparée par des virgules de valeurs (chaîne de caractères) et d'assigner à la variable comme suit
```
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);
```
Le résultat stocké dans @myvar est

5,6,7

Prochain, le FIND_IN_SET sélecteur est utilisé pour sélectionnez à partir de la simulation de la matrice de
```
SELECT * FROM mytable WHERE FIND_IN_SET(id,@myvar);
```
Le combiné du résultat final se présente comme suit:
```
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);
```
Je suis conscient que c'est un cas très spécifique. Toutefois, il peut être modifié pour convenir à tous les autres cas où une variable pour stocker un tableau de valeurs.

J'espère que cela aide.

InformationsquelleAutor Clinton
1

N'est pas le point de tableaux afin d'être efficace? Si vous êtes juste de l'itération sur les valeurs, je pense qu'un curseur sur un temporaire (ou permanente) de la table de fait plus de sens que de chercher des virgules, non? Aussi plus propre. Recherche de "mysql DECLARE CURSOR".

Pour un accès aléatoire à une table temporaire avec indexé numériquement clé primaire. Malheureusement, la manière la plus rapide d'accès que vous obtenez est une table de hachage, pas vrai d'accès aléatoire.
- C'est un commentaire, pas une réponse. Je n'ai pas indiqué que c'est ce que je veux faire avec le tableau.
InformationsquelleAutor Amaigus
0

En MYSQL version après 5.7.x, vous pouvez utiliser JSON type pour stocker un tableau. Vous pouvez obtenir la valeur d'un tableau par une clé via MYSQL.
- Pouvez-vous donner un exemple de comment je pourrais faire cela?
InformationsquelleAutor nick darn
0

Inspiré par la fonction ELT(numéro d'index, mot1, mot2, string3,...),je pense que l'exemple suivant fonctionne comme un tableau exemple:
```
set @i := 1;
while @i <= 3
do
  insert into table(val) values (ELT(@i ,'val1','val2','val3'...));
set @i = @i + 1;
end while;
```
Espère que ça aide.

InformationsquelleAutor edward

Je suis surpris aucune des réponses mentionnent ELT/CHAMP.

ELT/CHAMP fonctionne de manière très similaire à un tableau en particulier si vous avez des données statiques.

FIND_IN_SET travaille également similaire mais qui n'ont pas construit dans l'complémentaires
fonction, mais il est assez facile d'en écrire un.

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)

InformationsquelleAutor Jongab

-1

Je Pense que je peux améliorer sur cette réponse. Essayez ceci:

Le paramètre "Farces" est un fichier CSV. c'est à dire. '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;

Cette méthode rend la chaîne recherchée de CSV valeurs progressivement plus court à chaque itération de la boucle, ce qui je pense serait mieux pour l'optimisation.

Qu'est-ce que 'cette réponse" vous avez parlé? Aussi, vous n'avez pas à avoir un fichier CSV.

InformationsquelleAutor user2288580

-2

Je voudrais essayer quelque chose comme cela pour de multiples collections. Je suis un MySQL débutant. Désolé pour les noms de fonction, ne pouvait pas décider de ce que les noms seraient meilleures.

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

Je crois comprendre ce que vous êtes suggérant, mais cela est assez compliqué et probablement aussi incroyablement lent...
Je ne peux pas l'approuver ou de le rejeter sans stress tests. Il s'agit d'une clé primaire de recherche et d'insertion sur une table temporaire (ou d'une table normale). Je vais l'utiliser jusqu'à ce que je rencontre des problèmes ou de trouver une meilleure façon; mais je fais des choses étranges, comme écrire des compilateurs et des jeux complètement dans Oracle PL/SQL.

InformationsquelleAutor Dave

-4

Avez-vous essayé d'utiliser serialize () de PHP?
Qui vous permet de stocker le contenu d'une variable de tableau dans une chaîne de caractères en PHP comprend et est sans danger pour la base de données (en supposant que vous avez échappé à ce premier).
```
$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());
```
Vous pouvez également faire exactement la même sans un numéro de tableau
```
$array2 = array(
'something' => 'something else'
);
```
ou
```
$array3 = array(
'somethingNew'
);
```
- Je ne travaille pas avec PHP, ce qui n'est pas vraiment pertinent pour moi.
- L'utilisation de JSON au lieu de sérialiser. Il est plus générique et indépendant de la langue.
InformationsquelleAutor Magictallguy

Vous devez vous connecter pour publier un commentaire.