Dlaczego w MySQL'u nie stosować typu FLOAT

Kiedy chcemy zapisać liczbę zmiennoprzecinkową (np cenę) w większości języków programowania używamy tylu FLOAT. Okazuje się, że w języku SQL nie jest to dobry pomysł. Dlaczego?.

Nie da się zapisać

Niewiele osób wie, że liczby zmiennoprzecinkowe w SQL są zapisywane w formacie binarnym. Ma to bardzo ważną dla nas konsekwencję. Otóż nie wszystkie liczby zapisane w systemie dziesiętnym da się zapisać w formacie binarnym. Analogicznie do sytuacji w której chcielibyśmy zapisać w systemie dziesiętnym jedną trzecią. Możemy zapisać 0,333333333, ale ponieważ typy danych w językach programowania nie są nieskończone, gdzieś na końcu musimy zaokrąglić. Tak samo sprawa wygląda z FLOAT. SQL zaokrągla zapisane liczby do bardzo zbliżonych. Żeby było zabawniej kiedy odczytamy taką liczbę z bazy widzimy poprawną wartość.

W czym problem?

Można zadać sobie w takim razie pytanie w czym problem skoro przy odczycie otrzymujemy porwaną liczbę. Proponuję przeprowadzić mały eksperyment. Stwórzmy tabelę w bazie danych:

CREATE  TABLE `towar` (
  `id_towar` INT NOT NULL AUTO_INCREMENT ,
  `nazwa` VARCHAR(45) NULL ,
  `cena` FLOAT NULL ,
  PRIMARY KEY (`id_towar`) )
ENGINE = InnoDB

Dodajmy jeden rekord z ceną 59,95:

INSERT INTO `towar` (`id_towar`, `nazwa`, `cena`) VALUES (1, 'towar', 59.95);

Podana cena nie jest przypadkowa. To jest jedna z liczb, która będzie niepoprawnie zapisana, ponieważ wymaga nieskończonej precyzji. Faktycznie zostanie zapisana wartość: 59.950000762939. Zadajmy zapytanie:

SELECT cena FROM towar WHERE id_towar=1

Wynik: 59.95

Więc w czym problem? Baza zwróciła kwotę, którą wpisaliśmy po to by ukryć tą niedokładność. Niestety jeśli podczas odczytywania tej wartości pomnożymy ja razy np. miliard otrzymamy wartość której nie oczekiwaliśmy:

SELECT cena*1000000000 FROM towar WHERE id_towar=1

Otrzymamy wartość: 59950000762. 939

Jeszcze większy problem mamy, kiedy chcielibyśmy wyszukać towary w bazie, których cena wynosi 59.95 zł.

SELECT id_towar FROM towar WHERE cena=59.95

Takie zapytanie zwróci pusty wynik.

Wniosek jest taki, że najlepiej unikać stosowania typu FLOAT. Zresztą typy REAL lub DOUBLE PRECISION maja tą samą wadę.

Jakie mamy alternatywy?

Najczęściej dobrym rozwiązaniem będzie zastosowanie zamiast FLOAT typu NUMERIC lub DECIMAL. Te typy danych umożliwiają reprezentowanie wartości numerycznych z określoną precyzją i skalą w czasie definiowania kolumny. Możemy określić długość liczby i ilość miejsc po przecinku. Zmieniamy zatem naszą kolumnę cena:

ALTER TABLE towar CHANGE cena cena DECIMAL(9,2)

Teraz możemy zapisywać liczby o precyzji 9 i skali 2. Precyzja 9 oznacza, że może być 9 cyfr w liczbie np: 123456789. Skala oznacza ilość cyfr po przecinku. Skale odliczamy od precyzji, w związku z tym możemy zapisać liczby typu 1234567,89.

Teraz zapytanie:

SELECT id_towar FROM towar WHERE cena=59.95

Zwróci wartość 1  (oczywiście w przypadku naszej tabeli). Również mnożenie razy miliard da poprawny wynik.

W przypadku zapisywania kwot możemy tez zastosować typ INT i zapisywać kwoty w groszach, a po stronie aplikacji odpowiednio formatować ich wyświetlanie. Kto co woli :)

Wnioski

Jeśli tylko możemy unikamy używania typu FLOAT. Ten typ nie potrafi przechować wielu liczb więc zwracane wyniki z pól w tym formacje należy zawsze traktować jako niedokładne. Co ważne takie zachowanie się bazy nie jest błędem tylko zamierzone. Więcej na ten temat można poczytać wyszukując informacje na temat formatu  IEEE 754 - jest to specyfikacja, która opisuje standard zapisu formatu binarnego dla liczb zmiennoprzecinkowych.

Moje pytanie

Całkiem retoryczne pytanie: dlaczego w większości popularnych publikacji na temat programowania w PHP i MySQL'u nie ma na ten temat informacji?