Recherche MySQL de valeurs dans une chaîne de caractères séparée par des virgules
j'ai un champ COLORS (varchar(50))
dans ma table SHIRTS
qui contient une chaîne délimitée par une virgule telle que 1,2,5,12,15,
. Chaque nombre représentant les couleurs disponibles.
lors de l'exécution de la requête select * from shirts where colors like '%1%'
pour obtenir tous les T-shirts rouges (couleur=1), je reçois également les T-shirts qui est de couleur grise (=12) et orange (=15).
Comment dois-je réécrire la requête pour qu'elle ne sélectionne que la couleur 1 et pas toutes les couleurs contenant le numéro 1?
10 réponses
la manière classique serait d'ajouter des virgules à gauche et à droite:
select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
mais find_in_set fonctionne aussi:
select * from shirts where find_in_set('1',colors) <> 0
FIND_IN_SET est ton ami dans ce cas
select * from shirts where FIND_IN_SET(1,colors)
regardez la fonction FIND_IN_SET pour MySQL.
SELECT *
FROM shirts
WHERE FIND_IN_SET('1',colors) > 0
cela va fonctionner à coup sûr, et je l'ai effectivement essayé:
lwdba@localhost (DB test) :: DROP TABLE IF EXISTS shirts;
Query OK, 0 rows affected (0.08 sec)
lwdba@localhost (DB test) :: CREATE TABLE shirts
-> (<BR>
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> ticketnumber INT,
-> colors VARCHAR(30)
-> );<BR>
Query OK, 0 rows affected (0.19 sec)
lwdba@localhost (DB test) :: INSERT INTO shirts (ticketnumber,colors) VALUES
-> (32423,'1,2,5,12,15'),
-> (32424,'1,5,12,15,30'),
-> (32425,'2,5,11,15,28'),
-> (32426,'1,2,7,12,15'),
-> (32427,'2,4,8,12,15');
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@localhost (DB test) :: SELECT * FROM shirts WHERE LOCATE(CONCAT(',', 1 ,','),CONCAT(',',colors,',')) > 0;
+----+--------------+--------------+
| id | ticketnumber | colors |
+----+--------------+--------------+
| 1 | 32423 | 1,2,5,12,15 |
| 2 | 32424 | 1,5,12,15,30 |
| 4 | 32426 | 1,2,7,12,15 |
+----+--------------+--------------+
3 rows in set (0.00 sec)
essayez !!!
si l'ensemble des couleurs est plus ou moins fixe, le moyen le plus efficace et aussi le plus lisible serait d'utiliser les constantes de chaîne dans votre application et puis d'utiliser le SET
de MySQL avec FIND_IN_SET('red',colors)
dans vos requêtes. En utilisant le type SET
avec FIND_IN_SET , MySQL utilise un entier pour stocker toutes les valeurs et utilise l'opération binaire "and"
pour vérifier la présence de valeurs qui est beaucoup plus efficace que la numérisation d'une chaîne séparée par des virgules.
Dans SET('red','blue','green')
, 'red'
serait stocké en interne comme 1
, 'blue'
serait stocké en interne comme 2
et 'green'
serait stocké en interne comme 4
. La valeur 'red,blue'
serait stockée comme 3
( 1|2
) et 'red,green'
comme 5
( 1|4
).
si vous utilisez MySQL, il y a une méthode REGEXP que vous pouvez utiliser...
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
alors vous utiliserez:
SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'
vous devriez en fait corriger votre schéma de base de données de sorte que vous avez trois tables:
shirt: shirt_id, shirt_name
color: color_id, color_name
shirtcolor: shirt_id, color_id
alors si vous voulez trouver toutes les chemises qui sont rouges, vous feriez une requête comme:
SELECT *
FROM shirt, color
WHERE color.color_name = 'red'
AND shirt.shirt_id = shirtcolor.shirt_id
AND color.color_id = shirtcolor.color_id
select * from shirts where find_in_set('1',colors) <> 0
Fonctionne pour moi
vous pouvez atteindre cet objectif en suivant la fonction.
Exécuter la requête suivante pour créer la fonction.
DELIMITER ||
CREATE FUNCTION `TOTAL_OCCURANCE`(`commastring` TEXT, `findme` VARCHAR(255)) RETURNS int(11)
NO SQL
-- SANI: First param is for comma separated string and 2nd for string to find.
return ROUND (
(
LENGTH(commastring)
- LENGTH( REPLACE ( commastring, findme, "") )
) / LENGTH(findme)
);
et appelez cette fonction comme ceci
msyql> select TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB', 'A');
J'espère que ça aidera.
toutes les réponses ne sont pas vraiment correctes, essayez ceci:
select * from shirts where 1 IN (colors);