Ich habe ein Feld COLORS (varchar(50))
in meiner Tabelle SHIRTS
, das eine durch Kommas getrennte Zeichenfolge wie 1,2,5,12,15,
enthält. Jede Zahl steht für die verfügbaren Farben.
Wenn Sie die Abfrage select * from shirts where colors like '%1%'
ausführen, um alle roten Hemden (Farbe = 1) zu erhalten, erhalte ich auch die Hemden, deren Farbe grau (= 12) und orange (= 15) ist.
Wie soll ich die Abfrage neu schreiben, so dass NUR die Farbe 1 und nicht alle Farben mit der Nummer 1 ausgewählt werden?
Der klassische Weg wäre, links und rechts Kommas hinzuzufügen:
select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
Find_in_set funktioniert aber auch:
select * from shirts where find_in_set('1',colors) <> 0
FIND_IN_SET ist in diesem Fall dein Freund
select * from shirts where FIND_IN_SET(1,colors)
Schauen Sie sich die Funktion FIND_IN_SET für MySQL an.
SELECT *
FROM shirts
WHERE FIND_IN_SET('1',colors) > 0
Das wird sicher funktionieren, und ich habe es tatsächlich ausprobiert:
[email protected] (DB test) :: DROP TABLE IF EXISTS shirts;
Query OK, 0 rows affected (0.08 sec)
[email protected] (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)
[email protected] (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
[email protected] (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)
Versuche es !!!
Wenn der Farbsatz mehr oder weniger festgelegt ist, besteht die effizienteste und auch lesbarste Methode darin, Zeichenfolgekonstanten in Ihrer App zu verwenden und dann den MySQL-Typ SET
mit FIND_IN_SET('red',colors)
in Ihren Abfragen zu verwenden. Bei Verwendung des SET
-Typs mit FIND_IN_SET verwendet MySQL eine ganze Zahl zum Speichern aller Werte und die binäre "and"
-Operation zum Überprüfen auf Vorhandensein von Werten. Dies ist wesentlich effizienter als das Durchsuchen einer durch Kommas getrennten Zeichenfolge.
In SET('red','blue','green')
würde 'red'
intern als 1
gespeichert, 'blue'
würde intern als 2
gespeichert und 'green'
würde intern als 4
gespeichert werden. Der Wert 'red,blue'
wird als 3
(1|2
) und 'red,green'
als 5
(1|4
) gespeichert.
Wenn Sie MySQL verwenden, gibt es eine Methode REGEXP, mit der Sie ...
http://dev.mysql.com/doc/refman/5.1/de/regexp.html#operator_regexp
Dann würdest du verwenden:
SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'
Sie sollten Ihr Datenbankschema tatsächlich so korrigieren, dass Sie drei Tabellen haben:
shirt: shirt_id, shirt_name
color: color_id, color_name
shirtcolor: shirt_id, color_id
Wenn Sie dann alle Hemden finden möchten, die rot sind, führen Sie eine Abfrage wie folgt aus:
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
Funktioniert bei mir
Sie können dies durch folgende Funktion erreichen.
Führen Sie die folgende Abfrage aus, um die Funktion zu erstellen.
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)
);
Und rufen Sie diese Funktion so auf
msyql> select TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB', 'A');
Ich hoffe es würde helfen.