DeBlog

Odejmowanie w MySQL’u wydaje się być proste. No bo jaka może być filozofia w odejmowaniu jednej liczby od drugiej, zwłaszcza że obie są typu INT? Jeden przypadek sprawił, że jednak wcale nie jest tak do końca oczywiste.

Załóżmy, że mamy w bazie dwie tabelki X i Y:

Chcielibyśmy w jednym zapytaniu zmniejszać kolumnę “stock” z tabeli Y o wartość “amount” z tabeli X. W sumie co to za filozofia? (zakładamy, że stock może być ujemny):

Zapytanie działa, cieszymy się, ale co w przypadku kiedy “amount” jest większy od “stock”? Okazuje się, że MySQL zamiast zapisać wartość ujemną do pola, które zdefiniowaliśmy jako SIGNED, przekręca licznik i wstawia nam 7-cyfrową wartość dodatnią! Powodem tej sytuacji jest bit znakowy, wykorzystywany w polach typu SIGNED i jego brak w polu typu UNSIGNED. Aby zapytanie działało dokładnie tak jakbyśmy tego chcieli, musimy użyć funkcji CAST i wtedy będziemy operować na dwóch identycznych typach.

Ot taka ciekawostka, której można się nie spodziewać, a jednak jak już się na to trafi, to wszystko wydaje się oczywiste :)

Ostatnio w jednym z naszych projektów potrzebowałem wyciągnąć z bazy produktów po jednym produkcie według item_code. Miałem listę tych kodów do wyciągnięcia, powiedzmy że obejmowały zakres od 1809 do 1820. Banał.

SELECT * FROM items WHERE item_code = 1809;


I niespodzianka - 3 wyniki, ich item_code’y to 1809, 1809CI, 1809S. Oczywiście wywaliło to od razu skrypt, bo zamiast tablicy asocjacyjnej z kolumnami dla jednego wiersza, dostałem tablicę 3-elementową, po jednym elemencie dla każdego wiersza.

Krótkie wytłumaczenie, jeżeli jeszcze się ktoś nie domyślił - akurat ta baza jest na tyle durna, że item_code to pole varchar a nie int. Więc jeżeli w warunku jest item_code = 1809 a nie item_code = ‘1809′, to MySQL rzutuje całą kolumnę na integery, przez co do porównania wywala litery z końca kodu i nagle pasują mu aż 3 wpisy. Podanie szukanego kodu w uszach jako stringu daje spodziewany jeden jedyny rekord z kodem 1809.

Więc tylko ku przestrodze - nie wyszukujcie integerów w kolumnach tekstowych!

Czasem nam się zdarza, że format daty na stronkach ma być bardziej poetycki niż YYYY-mm-dd.

Niby nie ma problemu, słowne formaty też przecież są dostępne. Ale co jeśli serwis jest wielojęzykowy, albo po prostu nie-angielski? Okazuje się że ludziki od MySQLa to przewidzieli i wszystko mamy podane na tacy :)
Wystarczy przestawić locales jednym zapytaniem i już mamy ładne opisowe daty wedle uznania, np dla norweskiego:
SET lc_time_names = ‘no_NO’;

Od tej pory SELECT DATE_FORMAT(NOW(), ‘%d. %M %Y’) AS date daje nam przyjemne dla oka (grafika, nie programisty) ‘23. oktober 2009‘. No, trochę lewy przykład, bo Norwegowie mają prawie identyczne nazwy miesięcy jak Angole, no ale przynajmniej już jest przez ‘k’ ;)

Więcej do poczytania i dostępne wartości lc_time_names są tutaj:
http://dev.mysql.com/doc/refman/5.0/en/locale-support.html

Mam przykładowo tablę, która pełni rolę licznika odwiedzin dla danego rekordu. Aby troszkę to utrudnić to ta tabela dodatkowo przetrzymuje informacje jakiego typu jest ten licznik. Oczywiście został ustawiony unikalny klucz typu id-typ. Taki licznik można inkrementować jednym zapytaniem:

Zapytanie jest raczej proste (jeśli nie wiesz co oznacza “ON DUPLICATE KEY”
odsyłam do manuala MySQL). Całość trochę się komplikuje kiedy jednym SQLem chcę wrzucić kilkanaście rekordów. W tej sytuacji nasze zapytanie zmienia formę na coś takiego:

Wpis ku pamięci.

Swego czasu miałem spory problem z rozwiązaniem problemu wyciągnięcia danych grupując i sortując jednocześnie.

Cały problem polegał na tym, że chciałem wyciągnąć najnowszego newsa z konkretnej grupy. Robiłem to mniej więcej tak:

Niestety wynik bywał opłakany, bo wyciągane były zazwyczaj newsy pierwsze z brzegu. Dlaczego? Otóż przed ORDERem następuje grupowanie. MySQL w tej sytuacji nie patrzy na to czy ma jakoś posortować dane, tylko najpierw grupuje, a potem coś tam próbuje posortować :)

Żeby rozwiązać ten palący problem spędziłem trochę czasu na poszukiwaniach w Sieci. Widziałem jakieś INNER JOINy z JOINami i innymi cudami. Rozwiązanie, które pokażę (a znalazłem przypadkiem :)) wykorzystuje podzapytanie. Całość wydaje się zgrabna i czytelna, chociaż przyznam, że nie sprawdzałem pod kątem wydajności.

Dzisiaj walczyłem z dość prostym, aczkolwiek powolnym zapytaniem SQL. Problem pewnie polegał na tym, że dane były pobierane z tabeli o ~50k rekordach, sortowane, grupowane itd.

Owe zapytanie wyglądało mniej więcej tak:

Co ciekawe to zapytanie było strasznie powolne - czas wykonania 1,7s.  Dodatkowo zapytanie EXPLAIN dobiło faktem, że zapytanie typu SIMPLE musi wykorzystywać tabele tymczasową.

Aby całość przyspieszyć założono indeks na pola firstname i lastname

Oraz odpowiednio zmodyfikowaliśmy zapytanie:

Całość działała poprawnie i, co najważniejsze, szybko. Okazało się, że zapytanie ma jedną wadę. Sortowanie ma być najpierw po polu lastname, a potem po polu firstname. Okazuje się, że prosta zamiana kolejnością pól w wyrażeniu ORDER BY skutkuje spowolnieniem czasu wykonywania.

Fakt, czy będziemy grupować najpierw po polu firstname, a później po polu lastname nie miał znaczenia, dlatego postanowiono na zamianę kolejności tych pól, oraz poprawienie indeksu.

Dodatkowo drobna zmiana zapytania na:

I całość śmiga, aż miło :)

Okazuje się, że dla serwera MySQL kolejność pól w zakładanym indeksie ma wpływ na późniejszy czas wykonywania zapytań.

Niedawno odkryłem dość irytujący problem w MySQL. Otóż okazuje się, że nie można sumować wartości NULL z dowolną cyfrą. Wynik takiego zapytania:

Zwróci nam wartość NULL. Jak na mój chłopski rozum to powinna być wartość 5. Ten mały problem, może czasami całkiem sprawnie pokrzyżować jakieś zliczania/etc przez co wyniki, które otrzymamy mogą mijać się z rzeczywistością.

Na szczęście udało mi się znaleźć całkiem zgrabne rozwiązanie. Jest nim funkcja COALESCE, która zwraca pierwszą nie-pustą wartość podaną podczas wywołania. Drobna modyfikacja zapytania i mamy poprawny wynik:

O rety.. Przeszedłem długą drogę, JOIN.. GROUP BY.. FROM (SELECT …) i oczywiście i tak nie chciało działać.. A potrzebowałem pewnej prostej rzeczy, którą opiszę na prostym przykładzie: w bazie trzymam n-zamówień, zaś każde zamówienie może być podzielone na x-części, przy czym każda ta część ma swój status (np.: zgłoszone, zapłacone, wysłane…). Problem: w jaki sposób wyciągnąć jednym zapytaniem wszystkie zamówienia, przy czym chce również wiedzieć czy poszczególne zamówienia posiadają części o różnych statusach. Nic nie działało i tak o to trafiłem w tajemnicze COUNT(DISTINCT), i okazało się, że właśnie ta funkcja robi wszystkie skomplikowane rzeczy, które ja próbowałem tworzyć w ciągu pół godziny :)

Przykład zastosowania:

Niedawno natrafiłem na dosyć osobliwy problem podczas definiowania kolumn w jednej z tabeli. Okazuje się, że wszystkim dobrze znany (mam taką nadzieję:)) typ TIMESTAMP rządzi się swoimi własnymi prawami i potrafi w pewnych sytuacjach namieszać dość konkretnie. O co chodzi? Wyobraźmy sobie, że chcemy stworzyć w tabeli ‘czasy’ 2 pola:

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Niby wszystko jest w porządku, jednak pojawia się komunikat:

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

Niestety twórcy MySQL, chociaż opisali taką sytuację, nie raczyli napisać dlaczego tak się dzieje. Próba zastąpienia CURRENT_TIMESTAMP przez NOW() tudzież LOCALTIME() też nic nie daje, jako że są to wartości równoznaczne tej pierwszej. Warto przy tym wspomnieć, że typ TIMESTAMP daje dość duże możliwości, jeżeli chcemy dać polu wartość domyślną i/lub automatycznie uaktualniać przy użyciu nieszczęsnego CURRENT_TIMESTAMP. Możemy użyć go np. do czegoś takiego:
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
lub
updated_at TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP.

Wspaniale, tylko że dotyczy to pierwszej kolumny. Próba użycia CURRENT_TIMESTAMP w kolejnej kolumnie zakończy się powyższym błędem.
Co więc zrobić, gdy chcemy koniecznie mieć możliwość zdefiniowania defaultowej wartości dla obu takich pól czasowych (o aktualizacji nie wspominając)? Możemy zdefiniować pola następująco:

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
edited_at TIMESTAMP NOT NULL DEFAULT '2008-01-01 00:00:00'


Zamiast jakiejś daty w polu DEFAULT możemy umieścić 0 lub nawet NULL, ale w tym ostatnim przypadku trzeba również pamiętać o zadeklarowaniu pola jako NULL. Takie pośrednie rozwiązania zapewne jednak nas nie satysfakcjonują - data w drugim polu stanie się szybko przestarzała a wartość zerowa do niczego się raczej nie przyda. Niestety tylko takie wyjście proponuje
manual.
Najlepiej jest zatem zapewnić samemu wstawianie odpowiednich wartości. I tu można się bardzo zdziwić - nie trzeba bowiem pisać
INSERT INTO czasy VALUES (NOW(), CURRENT_TIMESTAMP);

Jak to? Definiujemy tabelę ‘czasy’ w ten sposób:
create table czasy (
created_at timestamp NOT NULL default '0000-00-00 00:00:00',
updated_at timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);

Następnie wykonujemy zapytanie
INSERT INTO czasy(created_at,updated_at) VALUES (NULL,NULL);

Jaki będzie efekt? Obie kolumny będą zawierać tą samą wartość - CURRENT_TIMESTAMP :) Dzieje się tak dlatego, że przypisanie kolumnie zdefiniowanej jako NOT NULL wartości NULL zamieni ją na aktualny czas.
A powiadają, że szczęśliwi czasu nie liczą…

W naszych serwisach bardzo często pojawia się zarządzanie cenami różnych produktów (wyliczanie cen, stawek VAT oraz innych skomplikowanych obliczeń). W większości przypadków podstawą jest cena netto, natomiast problem pojawia się, kiedy klient zażyczy aby podstawą była cena brutto!
Continue reading “Wykorzystanie ceny brutto w rachunkach aplikacji”