Mam pole COLORS (varchar(50))w mojej tabeli, SHIRTSktóre zawiera ciąg rozdzielany przecinkami, taki jak 1,2,5,12,15,. Każda liczba reprezentująca dostępne kolory.
Uruchamiając zapytanie select * from shirts where colors like '%1%'o wszystkie czerwone koszule (kolor = 1), otrzymuję również koszule w kolorze szarym (= 12) i pomarańczowym (= 15).
Jak mam przepisać zapytanie, aby wybierało TYLKO kolor 1, a nie wszystkie kolory zawierające liczbę 1?
Odpowiedzi:
Klasycznym sposobem byłoby dodanie przecinków po lewej i prawej stronie:
select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
Ale find_in_set działa również:
select * from shirts where find_in_set('1',colors) <> 0
W tym przypadku FIND_IN_SET jest Twoim przyjacielem
select * from shirts where FIND_IN_SET(1,colors)
Przyjrzyj się funkcji FIND_IN_SET dla MySQL.
SELECT *
FROM shirts
WHERE FIND_IN_SET('1',colors) > 0
To na pewno zadziała i faktycznie to wypróbowałem:
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)
Spróbuj !!!
Jeśli zestaw kolorów jest mniej więcej stała, najbardziej efektywne, a także najbardziej czytelny sposób byłoby użyć stałe ciągów znaków w aplikacji, a następnie użyć MySQL SETtypu ze FIND_IN_SET('red',colors)w zapytaniach. Używając SETtypu z FIND_IN_SET , MySQL używa jednej liczby całkowitej do przechowywania wszystkich wartości i używa "and"operacji binarnej do sprawdzenia obecności wartości, co jest o wiele bardziej wydajne niż skanowanie ciągu oddzielonego przecinkami.
W SET('red','blue','green'), 'red'byłby przechowywany wewnętrznie jako 1, 'blue'byłby przechowywany wewnętrznie jako 2i 'green'byłby przechowywany wewnętrznie jako 4. Wartość 'red,blue'zostanie zapisana jako 3( 1|2) i 'red,green'jako 5( 1|4).
Jeśli używasz MySQL, istnieje metoda REGEXP, której możesz użyć ...
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
Więc użyłbyś:
SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'
Właściwie powinieneś naprawić schemat bazy danych, aby mieć trzy tabele:
shirt: shirt_id, shirt_name
color: color_id, color_name
shirtcolor: shirt_id, color_id
Następnie, jeśli chcesz znaleźć wszystkie czerwone koszule, zrób zapytanie takie jak:
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
Pracuje dla mnie
1. W przypadku MySQL:
SELECT FIND_IN_SET(5, columnname) AS result
FROM table
2. dla Postgres SQL:
SELECT *
FROM TABLENAME f
WHERE 'searchvalue' = ANY (string_to_array(COLUMNNAME, ','))
Przykład
select *
from customer f
where '11' = ANY (string_to_array(customerids, ','))
Możesz to osiągnąć, wykonując następujące funkcje.
Uruchom następujące zapytanie, aby utworzyć funkcję.
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)
);
I nazwij tę funkcję w ten sposób
msyql> select TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB', 'A');