Zapytanie MySQL wyszukuje wartości w ciągu oddzielonym przecinkami


93

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?


6
Przypuszczam, że można to zrobić za pomocą wyrażenia regularnego, ale znacznie lepszym rozwiązaniem byłoby podzielenie kolorów koszul na osobną tabelę (kolory) i użycie tabeli łączenia (kolory_koszuli), używając identyfikatorów koloru / koszuli, aby je połączyć.
ceejayoz

Nie mogę uwierzyć z 6 odpowiedziami, żaden z nich nie wspomniał o typie danych MySQL SET ..
ColinM,

Odpowiedzi:


190

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

Próbowałem find_in_set, ale zwraca ten sam wynik niezależnie od wprowadzonej wartości koloru ... Jakieś sugestie?
bikey77

@ bikey77: Może to jest problem, dokumentacja mówi: Ta funkcja nie działa poprawnie, jeśli pierwszy argument zawiera znak przecinka („,”).
Andomar

Mój błąd, to był logiczny błąd z powodu tych samych wartości fikcyjnych. To działa dobrze. Dzięki!
bikey77

@Andomar Zanim znalazłem twoją odpowiedź, zmagałem się z IN, ale twoja działa jak urok ... Dziękuję bardzo ..
PHP Mentor

3
Ma to wpływ na wydajność, ponieważ Find_in_set nie używa indeksu
Kamran Shahid



11

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 !!!


Hej @rolandomysqldba, testuję twoje zapytanie i działa dobrze, ale muszę wprowadzić w nim kilka zmian. Powiedzmy, że chcę uzyskać wszystkie koszule, w których wartość koloru wynosi 1,2 w kolumnie.
Ahsan Saeed

7

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



3

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

8
@Blindy: Jest to prawdą tylko wtedy, gdy założysz, że OP ma prawa edycji schematu bazy danych; ma czas na przeprojektowanie bazy danych, migrację danych i refaktoryzację wszystkich klientów; oraz że redukcja złożoności tego zapytania przeważa nad wzrostem złożoności w pozostałej części aplikacji.
Andomar,

1
@Andomar, z drugiej strony, gdy napotka ograniczenia rozmiaru przy pobieraniu wierszy i jego „rekordy” zostaną obcięte, wtedy zacznie się prawdziwa zabawa!
Blindy,

3
@Blindy: Brakuje Ci sensu; Nie twierdzę, że ma najlepsze rozwiązanie, tylko że nie każdy ma swobodę przeprojektowania swojego środowiska zgodnie z
własnymi

Zgadzam się z @Andomar
Adam B


0

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

0

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

-7

Nie wszystkie odpowiedzi są poprawne, spróbuj tego:

select * from shirts where 1 IN (colors);
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.