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ł. Zachęcam do lektury krótkiego rozdziału, dokumentującego konkretny antywzorzec języka SQL.

Poniższe informacje w formie zmodyfikowanej lub nie zaczerpnięte są z książki Billa Karwina „Antywzorce języka SQL”. Polecam, jako niezbędnik programisty.

Niektóre wartości liczbowe, które możemy sobie bez trudu wyobrazić, w ogóle nie mogą być reprezentowane z zastosowaniem metody skończonej precyzji. Część programistów uważa, że takie rozwiązanie jest usprawiedliwione – skoro wartości złożonych z nieskończonej liczby cyfr i tak nie da się zapisać, każda zapisywana przez nas liczba z natury ma skończoną precyzję i tak też powinna być przechowywana w formie binarnej, prawda? Niestety nie.

Standard

Zgodnie ze standardem IEEE 754 liczby zmiennoprzecinkowe są reprezentowane w systemie liczbowym o podstawie 2. Oznacza to, że wartości wymagające nieskończonej precyzji w systemie binarnym nie pokrywają się ze zbiorem wartości, które wymagają takiej reprezentacji w systemie dziesiętnym. Niektóre wartości, które wymagają skończonej precyzji w systemie dziesiętnym, na przykład 59,95, wymagają nieskończonej precyzji, jeśli miałyby być dokładnie reprezentowane w systemie binarnym. Typ danych FLOAT nie oferuje takich możliwości, zatem stosuje najbliższą obsługiwaną wartość w systemie liczbowym o podstawie 2, czyli wartość odpowiadającą liczbie 59.950000762939 w systemie dziesiętnym.

Niektóre wartości przypadkowo wymagają skończonej precyzji w obu formatach. Jeśli zrozumiemy szczegóły przechowywania liczb w formacie IEEE 754, teoretycznie będziemy potrafili przewidywać, jak poszczególne wartości dziesiętne będą reprezentowane w formacie binarnym. W praktyce jednak większość programistów nie wykonuje podobnych obliczeń dla każdej stosowanej przez siebie liczby zmiennoprzecinkowej. Nie możemy zagwarantować, że kolumna FLOAT w bazie danych będzie dostatecznie precyzyjna, zatem nasza aplikacja powinna zakładać, że każda wartość w tej kolumnie mogła zostać zaokrąglona. Niektóre bazy danych obsługują pokrewne typy danych nazwane DOUBLE PRECISION i REAL. Precyzja oferowana przez te typy danych i sam typ FLOAT zależy co prawda od implementacji bazy danych, ale wszystkie te typy reprezentują wartości zmiennoprzecinkowe ze skończoną liczbą cyfr binarnych, zatem sposób zaokrąglania liczb we wszystkich przypadkach jest podobny.

Uzasadnione użycie

FLOAT jest dobrym typem danych w sytuacji, gdy potrzebujemy liczb rzeczywistych z przedziału większego niż ten obsługiwany przez typy INTEGER i NUMERIC. Naukowe aplikacje często wskazuje się jako przykład uzasadnionego stosowania typu FLOAT. W systemie Oracle typu danych FLOAT używa się do wyrażania dokładnie skalowanych wartości liczbowych, zaś do reprezentowania niedokładnych wartości numerycznych stosuje się typ danych BINARY_FLOAT (zgodny ze standardem kodowania IEEE 754).

Rozwiązanie

Zamiast typu FLOAT i typów pokrewnych możemy stosować typy danych NUMERIC lub DECIMAL języka SQL dla liczb ułamkowych stałej precyzji.

ALTER TABLE Bledy ADD COLUMN godziny NUMERIC(9,2);
ALTER TABLE Konta ADD COLUMN stawka_godzinowa NUMERIC(9,2);

Wymienione typy danych umożliwiają dokładne reprezentowanie wartości numerycznych z maksymalną precyzją określoną podczas definiowania odpowiednich kolumn. Precyzję należy określić w formie argumentu typu danych — obowiązująca składnia przypomina trochę sposób określania długości typu danych VARCHAR. Precyzja to łączna liczba cyfr dziesiętnych, których możemy używać dla wartości w tak zdefiniowanej kolumnie. Precyzja równa 9 oznacza, że możemy przechowywać takie wartości jak 123456789, ale najprawdopodobniej nie będziemy mogli obsłużyć wartości równej 1234567890 [1].

Istnieje też możliwość określenia skali za pośrednictwem drugiego argumentu tego typu danych. Skala decyduje o liczbie cyfr na prawo od przecinka oddzielającego część całkowitą od ułamkowej. Cyfry skali są odliczane od liczby cyfr precyzji, zatem precyzja 9 ze skalą 2 oznaczają, że możemy przechowywać takie wartości, jak 1234567.89, ale już nie wartości 12345678.91 czy 123456.789.

Określane przez nas precyzja i skala są stosowane dla danej kolumny we wszystkich wierszach tabeli. Innymi słowy, nie możemy przechowywać wartości ze skalą 2 w części wierszy i wartości ze skalą 4 w pozostałych. W języku SQL to naturalne, że typ danych kolumny jest konsekwentnie stosowany dla wszystkich wierszy (tak jak w przypadku kolumny typu VARCHAR(20), gdzie każdy wiersz może zawierać łańcuch określonej długości).

Zaletą typów NUMERIC i DECIMAL jest możliwość przechowywania liczb wymiernych bez ryzyka ich zaokrąglania (jak w przypadku typu FLOAT). Po zapisaniu wartości 59.95 możemy być pewni, że w bazie danych jest przechowywana dokładnie ta liczba. Jeśli porównamy ją ze stałą wartością 59.95, okaże się, że obie wartości są sobie równe.

Jeśli tylko  możemy tego uniknąć, nie powinniśmy używać typu FLOAT.

[1] W niektórych systemach baz danych rozmiar tej kolumny jest zaokrąglany w górę do najbliższego bajta, słowa lub podwójnego słowa, zatem w pewnych przypadkach maksymalna wartość w kolumnie typu NUMERIC może składać się z większej liczby cyfr, niż to wynika ze wskazanej precyzji.