MySQL porównuje ciąg DATE z ciągiem z pola DATETIME

93

Mam pytanie: Czy można dokonać wyboru z bazy danych MySQL, porównując jeden ciąg DATY „2010-04-29” z ciągami przechowywanymi jako DATETIME (2010-04-29 10:00)?

Mam jeden selektor dat, który filtruje dane i chciałbym zapytać tabelę o pole DATETIME w następujący sposób:

SELECT * FROM `calendar` WHERE startTime = '2010-04-29'"

... i chciałbym uzyskać wiersz, który ma wartość DATETIME „2010-04-29 10:00”.

Jakieś sugestie? Dzięki.

mysql datetime comparison

— Manny Calavera
źródło

163

Użyj następujących:

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

Dla odniesienia mam 2 miliony rekordów tabeli, uruchomiłem podobne zapytanie. Odpowiedź Salils zajęła 4,48 sekundy, powyżej 2,25 sekundy.

Więc jeśli stół jest DUŻY, sugerowałbym raczej to.

— David
źródło

8

Pierwsza odpowiedź jest tak powolna, ponieważ przed porównaniem musi sformatować każdą datę i godzinę w łańcuch. Twój jest lepszy, ponieważ bezpośrednio porównuje tylko część daty z pola, ale nadal nie może używać indeksu (testowany na mysql 5.1)

— Marki555

1

Jeśli problemem jest wydajność, warto rozważyć oddzielne przechowywanie części dotyczącej daty i godziny, aby w części daty można było umieścić INDEKS.

— Thijs Riezebeek

1

To zapytanie nie będzie mogło używać indeksu, jeśli startTimejest indeksowane.

— Zamrony P. Juhara

43

Jeśli chcesz zaznaczyć wszystkie wiersze, w których część DATE w kolumnie DATETIME pasuje do określonego literału, nie możesz tego zrobić w ten sposób:

WHERE startTime = '2010-04-29'

ponieważ MySQL nie może bezpośrednio porównywać DATY i DATY. To, co robi MySQL, rozszerza podany literał DATE o czas „00: 00: 00”. Więc twój stan się stanie

WHERE startTime = '2010-04-29 00:00:00'

Na pewno nie to, czego chcesz!

Warunek jest zakresem i dlatego powinien być podany jako zakres. Możliwości jest kilka:

WHERE startTime BETWEEN '2010-04-29 00:00:00' AND '2010-04-29 23:59:59'
WHERE startTime >= '2010-04-29' AND startTime < ('2010-04-29' + INTERVAL 1 DAY)

Istnieje niewielka możliwość, że pierwsza będzie błędna - gdy kolumna DATETIME używa rozdzielczości drugiej sekundy i jest umówione spotkanie o 23:59:59 + epsilon. Generalnie proponuję skorzystać z drugiego wariantu.

Oba warianty mogą używać indeksu na startTime, który stanie się ważny, gdy tabela wzrośnie.

— XL_
źródło

9

Wersja Davida nie jest dobra, nie może używać indeksów. Aby skorzystać z indeksu, trzeba filtr na kolumnie bezpośrednio, a nie na wyniku funkcji ( date(), year(), datediff()lub podobnym)

— Marki555

Szukałem, jak mysql konwertuje datę na datetime, dzięki za wyczyszczenie dodaje 00:00:00. teraz moje wyniki mają sens

— Księgowy م

1

To poprawne szybkie rozwiązanie w porównaniu z innymi odpowiedziami, które nie mogą używać indeksu, jeśli są dostępne

— Zamrony P. Juhara

23

SELECT * FROM `calendar` WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'"

LUB

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

— Salil
źródło

25

To straszne pytanie. Unikaj tego za wszelką cenę. Jeśli na startTime istnieje indeks, nie może być on używany przez to zapytanie, ponieważ stosuje funkcję do kolumny, a nie używa jej bezpośrednio. Oznacza to, że każde takie zapytanie będzie wymagało pełnego skanowania tabeli. W przypadku dużej tabeli będzie to oznaczać wyjątkowo wolne zapytanie.

— steveayre

Zgadzam się, jego odpowiedź jest błędna - poprawną odpowiedź zamieścił poniżej David.

— mindplay.dk

1

Proszę, skorzystaj z odpowiedzi Davida, ponieważ jest o wiele szybsza. Ten jest ważny, ale nie dobry dla szybkości ...

— xarlymg89

1

@ CarlosAlbertoMartínezGadea Ta wersja jest powolna, ponieważ przed porównaniem musi sformatować każdą wartość na ciąg znaków ... Wersja Davida tego nie potrzebuje, ale nadal nie może używać indeksów, więc nie jest optymalna. Zobacz odpowiedź od XL_ dla wersji, która może używać indeksu. Niestety nie ma lepszego sposobu na zrobienie tego poprawnie w mysql

— Marki555

1

Głosowałem negatywnie na tę odpowiedź, ponieważ zapytanie nie może używać indeksu, podczas gdy odpowiedź @ XL_ tak.

— pedromanoel

2

SELECT * FROM sample_table WHERE last_visit = DATE_FORMAT('2014-11-24 10:48:09','%Y-%m-%d %H:%i:%s')

to dla formatu datetime w mysql przy użyciu DATE_FORMAT(date,format).

— RT
źródło

1

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29';

pomaga, możesz przekonwertować wartości tak jak DATEprzed porównaniem.

— Sarath
źródło

0

Możesz przesłać pole DATETIME na DATE jako:

SELECT * FROM `calendar` WHERE CAST(startTime AS DATE) = '2010-04-29'

Jest to bardzo wydajne.

— SGAmpere
źródło

1

Nie, to też nie jest wydajne. nie będzie mógł korzystać z indeksu, jeśli startTimejest indeksowany

— Zamrony P. Juhara,

-6

SELECT * FROM `calendar` WHERE startTime like '2010-04-29%'

Możesz także użyć operatorów porównania w datach MySQL, jeśli chcesz znaleźć coś przed lub po. Dzieje się tak, ponieważ są one zapisane w taki sposób (od największej do najmniejszej z zerami wiodącymi), że proste sortowanie ciągów posortuje je poprawnie.

— Fletcher Moore
źródło