Strona używa cookies (ciasteczek). Dowiedz się więcej o celu ich używania i zmianach ustawień. Korzystając ze strony wyrażasz zgodę na używanie cookies, zgodnie z aktualnymi ustawieniami przeglądarki.    X

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? 

internet porady programowanie

Komentarze

0 nowych
R@z0r   5 #1 20.02.2012 15:09

Nie szybciej trzymać w intach i dzielić przez np 100 dla 2 miejsc po przecinku?

Mifczu   11 #2 20.02.2012 15:11

Wszystko zależy co ma reprezentować liczba i na jakiej dokładności nam zależy. Ten typ nie jest tam bez powodu, w większości języków programowania z float jest problem dokładności i zaokrągleń. Ale faktycznie do cen, kursów walut itp lepiej używać DECIMAL który w rezultacie jest dwoma int-ami ;)

slepciu   10 #3 20.02.2012 15:14

@R@z0r
O typach INT i dzieleniu przez 100 dla kwot piszę w ostatnim akapicie części "Jakie mamy alternatywy?"

jkolonko   9 #4 20.02.2012 15:23

> Dlaczego w większości popularnych publikacji na temat programowania w PHP i MySQL'u nie ma na ten temat informacji?

Chyba jednak są, znalazłem pierwsze z brzegu linki w Google:
http://www.php.net/float (duży czerwony warning)
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html (już w tytule rozdziału jest o tym, że to wartość przybliżona)

Poza tym nie jest to cecha PHP i Mysqla. We wszystkich znanych mi językach z liczbami zmiennoprzecinkowymi jest podobnie i istnieje stałoprzecinkowy odpowiednik typu danych na liczby z przecinkiem - również ze swoimi ograniczonymi dotyczącymi precyzji i zakresu, więc warto zgłębić temat dokładnie i poczytać o tych parametrach.

tfl   8 #5 20.02.2012 15:24

@autor

to Ty, Brain?

I chyba chodzi o ieee 754 ?

R@z0r   5 #6 20.02.2012 15:26

Zwracam honor. Musiało mi to umknąć ;)

parasite85   7 #7 20.02.2012 15:37

@autor
Wielkie dzięki slepciu, w tej chwili jestem na etapie tworzenia pracy licencjackiej (projektowa: system do ewidencji zasobów sprzętowo-programowych) a dokładnie projektuje bazę danych w MySQL. Do tej pory nie miałem za dużo do czynienia z tą bazą danych - korzystałem z MS SQL Express i tego typu problemów nie zauważyłem...muszę sprawdzić na szybciutko:)

parasite85   7 #8 20.02.2012 16:14

Sprawadziłem to w MS SQL - system radzi sobie z tym bez problemu i podana wartość jest dokładnie taka jaką wpisujemy.

slepciu   10 #9 20.02.2012 16:23

@jkolonko
Ja nie twierdzę, że tych informacji nie ma. jak wiemy czego szukać to znajdziemy, ale chyba przyznasz, że jak ktoś się uczy PHP czy MySql'a to nie zaczyna od dokumentacji. Ale dzięki za uwagę.

@tfl
to nie ja, znaczy ja to ja ale nie Brain :)
Rzeczywiście chodziło o 754 - już poprawiłem. Dzięki za zwrócenie uwagi.

@parasite85
Dobrze wiedzieć. Wydaje mi się, że w bazach Oracle też ten problem nie występuje, ale pewny nie jestem

StawikPiast   10 #10 20.02.2012 17:06

ten problem wystepuje we wszystkich bazach i jezykach programowana i jest zwiazany z konstrukcja procesorow. oczywiscie mozna stosowac pewne obejscia, ale moga byc one zawodne wiec wlasnie lepiej decimal stosowac. Do tego to powinien byc decimal z 4 cyframi po przecinku, bo tak sie rozlicza platnosci.

Pigmej   5 #11 20.02.2012 19:29

Generalnie zapisywanie ceny / kwoty jako FLOAT to jeden z większych możliwych 'błędów' programistycznych.

Jak już wspomniano wcześniej ani PHP ani MySQL nie jest tu 'odosobnione'. Po prostu NIE WOLNO trzymać kwot jako Float i to warto pamiętać.

Ryan   15 #12 20.02.2012 20:02

To nie wina SQLa ani PHP, że programista nie rozumie istoty typów w danym języku. Sam fakt, że piszesz coś o zapisie binarnym floatów świadczy, że jesteś odrobinę zagubiony. Stosowanie porównania dla floatów (w zasadzie bez względu na implementację, czy zgodna z IEEE 754, czy nie, liczby zmiennoprzecinkowe - zgodnie zdefinicją - mają zmienną precyzję) obarczone jest błędem. Jeśli liczby, na których operujesz, są w wąskim zakresie, to precyzja obliczeń jest wystarczająca, by porównywać z deltą. Jeśli nie kumulujesz błędów z wielu obliczeń, to float będzie ok. Zasadniczo jednak do działań na np. walutach używa się liczb stałoprzecinkowych (jeśli tylko się da). Wie to każdy, kto zadał sobie trud zrozumienia czym jest liczba zmiennoprzecinkowa. Najwyraźniej większość materiałów zakłada, że czytelnik jest na tyle dociekliwy.

slepciu   10 #13 20.02.2012 21:06

@Ryan
Za Wikipedią: "IEEE 754 – standard reprezentacji binarnej i operacji na liczbach zmiennoprzecinkowych". Co ważne nigdzie nie twierdze, że jest to wina PHP czy SQL, wręcz przeciwnie. Do tego wpisu zainspirowała mnie książka o najczęściej popełnianych błędach przy używaniu SQL. Swoją drogą gdybyśmy zakładali że programiści wszystko wiedzą, to po co w ogóle pisać cokolwiek - tylko skąd wtedy mieli by wszystko wiedzieć - i takie koło zamknięte. Napisałem o tym z założeniem, że może jednak jest ktoś, kto o tym nie wie i mu się przyda, w końcu nie sami zawodowi programiści używają SQL,a. Są też tacy, którzy uczą się go z książek, które słabo (lub wcale) wyjaśniają tą kwestię. Podsumowując wydaje mi się niezasadne dyskutowanie nad zasadnością tego wpisu - może mimo wszytko komuś się przyda. Można oczywiście dyskutować nad tym, czy mam rację, czy popełniam jakiś błąd merytoryczny.

tfl   8 #14 20.02.2012 21:31

@slepciu

Brain Kernighan to autor rozdzialu ksiazki, ktora sie zainspirowales. Taki... zart :)

slepciu   10 #15 20.02.2012 21:44

@tfl
Nie mam pamięci do autorów, sprawdziłem ale to nie on - moja napisał Bill Karwin, ale widać nie on jeden pisze o tym. To w sumie dobrze i trochę przeczy mojemu końcowemu pytaniu :) Pozdrawiam

tfl   8 #16 20.02.2012 21:59

@slepciu
Masz racje. Brain tylko stworzyl motto do 10 rozdzialu tej ksiazki :)

slepciu   10 #17 20.02.2012 22:11

@tfl
Racja :)

soanvig   9 #18 20.02.2012 23:27

Co za bzdura, że z liczby 59.95, robi jakąś daleko-zmiennoprzecinkową liczbę (nie sugeruję, że źle napisałeś, bynajmniej. Bzdurą jest taki fakt :D)?! Skąd silnik te pozostałe cyfry bierze? Z kosmosu?

kubut   17 #19 20.02.2012 23:33

Dzięki slepciu za wpis, z SQL mam styczność właściwie na co dzień, choć na płaszczyźnie amatorskiej. Zapewne prędzej czy później przyda się to co napisałeś, bo przyznam że nie wiedziałem o takim problemie ;)

kubut   17 #20 20.02.2012 23:35

@soanvig - chyba nie tyle silnik "bierze te liczby z kosmosu" co matematyka w systemie dwójkowym ;) Przynajmniej ja tak zrozumiałem wpis ;)

tfl   8 #21 21.02.2012 07:37

@kubut

Ale to nie jest problem, tylko cecha, wynikajaca z zalozen. Tak to po prostu ma dzialac. Problem jest wtedy, gdy sie tego uzywa bez posiadania odpowiedniej wiedzy.

Wszystko dlatego, ze dzieki zmiennoprzecinkowosci na 32 bitach mozna zapisac liczbe od -128 do 127 z kilkunastoma (23?) znakami po przecinku. To, ze sa zaokraglenia wynika wlasnie z tego powodu.

command-dos   17 #22 21.02.2012 10:11

@slepcu - dzięki za wpis. Kto wie, może się jeszcze kiedyś przyda. Miałem do czynienia z bazami wcześniej i... chyba popełniałem właśnie ten błąd.

slepciu   10 #23 21.02.2012 14:38

@kubut
@command-dos
Proszę bardzo :) Cieszę się, że się na marne nie pisałem.

NRN   9 #24 24.02.2012 14:58

Patrząc na ten wpis, proponuję wszystkim laboratoria z przedmiotu "Elementy Analizy Numerycznej" (tudzież: "Wstęp do Podstaw Elementów Analizy Numerycznej w języku Delphi Pascal") prowadzonego na 4. semestrze studiów pierwszego stopnia na kierunku Informatyka na Politechnice Poznańskiej. Powyższy problem jest wspaniale opisywany, wraz z rozwiązaniem :) Tylko zdawalnosć jest nie-za-dobra, skąd można wysnuć wniosek, "dlaczego nadal pracujemy na tak niedoskonałym systemie". :P

Ryan   15 #25 26.02.2012 14:27

@slepciu: Tak, we wpisie jest szereg błędów merytorycznych i nieścisłości. Przykładowo piszesz, że w SQLu używanie floata to kiepski pomysł. To pomysł tak samo dobry, jak w każdym innym języku. Ważne po co się go używa. Z tekstu wynika, że zasadność użycia floatów jest pod parasolem "brak". A to nie jest prawda. Piszesz o "zapisie binarnym", co jest kompletnym farmazonem - wszystkie liczby zapisywane są przez komputery w postaci binarnej, w każdym momencie. Kwiatków tego typu jest więcej.

slepciu   10 #26 27.02.2012 11:18

@Ryan
Masz rację, przesadziłem może z napisaniem, że to kiepski pomysł, powinienem użyć raczej określenia że często nie jest to dobrym pomysłem. Rzeczywiście są zastosowania, przy których ten typ będzie nie tylko dobry, ale pewnie i niezbędny. Zgadzam się i składam w tej kwestii krytykę w swoją stronę :) Natomiast nadal nie zgadzam się z tym, że kwestia "zapisu binarnego" jest, jak to określiłeś, "farmazonem". To że wszystkie liczby są tak zapisywane, w żaden sposób nie zmienia tego o czym napisałem.

  #27 09.07.2012 13:41

Super! Dzieki za wyjasnienie! BArdzo przydatne

slepciu   10 #28 09.07.2012 21:39

@Super (niezalogowany) - proszę, cieszę się, że się przydało :)

  #29 10.09.2012 10:09

@slepciu - nie rozumiesz, przeczytaj jeszcze raz co napisał @Ryan, jest różnica między liczbą dwójkowa a liczba (zapis) binarna to nie to samo.
Napisałeś: "Otóż nie wszystkie liczby zapisane w systemie dziesiętnym da się zapisać w formacie binarnym.", na błędach człowiek się uczy, dzięki za artykuł, ale popraw co trzeba i dowiedz się, choćby dla własnej satysfakcji.

  #30 02.10.2012 10:25

DECIMAL i NUMERIC to reprezentacja TEKSTOWA w MySQL. Co się z tym wiąże chyba nie trzeba pisać ale napiszę.
Powolne liczenie (konwersja w obie strony), zajmowana pamięci, np. 8 cyfrowa liczba zajmie 8 bajtów (każdy znak jako char), podczas gdy float zajmuje tylko 4 bajty.
Reprezentacja jako String nie dotyczy już typu DECIMAL z tego co przeczytałem w dokumentacji.
Co do NUMERIC, może też się coś zmieniło... :-)
Co do cen, oczywiście DECIMAL :-)

  #31 27.11.2012 13:08

Człowieku, uratowałeś mi życie :-) Dziękuję z całego serca.

  #32 08.06.2013 13:45

używam float w progr. sklepowych i działa bez zarzutu. działa szybko.
przy zaokrągl. do 2 miejsc po przecinku - nie ma żadnych problemów. nic się nie rozsypie a obliczenia będą odpowiednio dokładne.

zapytania do bazy też można nadać inteligentnie - żaden problem. często w mysql trzeba omijać różne dziwne problemy.

artykuł trochę wypacza sens float.
jeżeli float jest tak NIEBEZPIECZNY to od czasu wprowadzenia kalkulatorów cała księgowość i rachunkowość roi się od błędów obliczeniowych, a my potrzebujemy nowego typu maszyn obliczeniowych :)

  #33 11.06.2013 10:24

@Tomasz S. - tak wyedukowane komentarze powinny byc kasowane z miejsca bo ludziom, ktorzy sie dopiero ucza pcha smieci do glowy.
liczba binarna i liczba dwojkowa to dokladnie to samo.
roznica lezy jedynie w tym ze to pierwsze to kalka z jezyka angielskiego a drugie to nasze rodzime slowo.
rownie dobrze mozesz dyskutowac, ze sasuage to nie to samo co kielbasa.

@ale_jazda
masz racje:
ksiegowosc i rachunkowosc roi sie od bledow obliczeniowych, a my potrzebujemy nowego typu maszyn obliczeniowych.
Ciekawy przewal jakies chyba parenascie lat temu machnal francuski (o ile dobrze pamietam) programista wykorzystujac wlasnie bledy zaokraglen. Zhakowal jakis bank i odprowadzal na swoje konto z kazdej tranzakcji te zaokraglane koncowki. Z przykladowej kwoty 59.950000762939 sciagal 0.000000762939. Taka transakcja dla pracownikow banku wygladala jak operacja na 0 frankow (o ile wogole sie pokazywala).

Inna dziedzina gdzie nieprecyzyjnosc obecnych urzadzen jest duzym problemem to np prognozowanie pogody gdzie obecnie nie jest mozliwe wyliczenie na dluzszy okres w przod sytuacji pogodowej.
Kiedys nawet jakis dokument o tym lecial. Pokazywali nawet jak rozne kalkulatory po wykonaniu pewnych dluzszych ciagow operacji dawaly rozbierzne wyniki.

A puenta jest taka, ze float nie jest ani zly ani dobry.
Trzeba po prostu wiedziec jakie ryzyko idzie z jego uzywania.

Na koniec macie linka (ktory pewnie nikomu z was sie w zyciu nie przyda ;]) do biblioteki intela przygotowanej specjalnie dla sektora finansowego.
Cytujac:
"Product Overview

Software implementation of the IEEE 754-2008 Decimal Floating-Point Arithmetic specification, aimed at financial applications, especially in cases where legal requirements make it necessary to use decimal, and not binary floating-point arithmetic (as computation performed with binary floating-point operations may introduce small, but unacceptable errors)."

http://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library

  #34 15.10.2013 13:06

LOL, racja ze specifykacją FLOAT, ale czy autor próbował poprawnie utworzyć tabelkę?
CREATE TABLE `towar` (
`id_towar` int(11) NOT NULL AUTO_INCREMENT,
`nazwa` varchar(45) DEFAULT NULL,
`cena` float(10,2) DEFAULT NULL,
PRIMARY KEY (`id_towar`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf

W tym przypadku nie ma problemu. Float zajmuje tylko 4 bajty, podczas gdy DECIMAL 8 bajtów jeśli używamy liczb z przecinkiem http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html

mar3k2004   1 #35 23.01.2014 12:11

wg specyfikacji Międzynarodowej Federacji Księgowych, dane dotyczące kwot pieniężnych powinny być zapisywane jako DECIMAL ( 10 , 4 )

Pozdrawiam

  #36 02.07.2014 16:35

Dziękuję za artykuł, naprawdę bardzo mi się przydał. Pozdrowienia serdeczne! Walker z przetrwanie.net

slepciu   10 #37 02.07.2014 21:52

@janlis1995 - miło to słyszeć :)

  #38 27.11.2014 02:36

najlepiej zapisywać ceny w groszach lub odpowiednio np. centach itd.
zamiast 55.95 = 5595. a w programie odpowiednio przedstawiamy ten wynik

  #39 12.12.2014 09:22

Dziwne miejsce, dziwny artykuł, dziwne komentarze...

Wygląda to trochę jak dyskusja przedszkolaków na temat mechaniki kwantowej.

W całym zagadnieniu nie ma niczego nieoczekiwanego, czy trudnego. Całe zagadnienie działa dokładnie tak samo pod dowolną platformą, architekturą czy też językiem programowania, a różne cechy wynikają z konstrukcji liczb zmiennoprzecinkowych.

Autor zdaje się nie rozróżniać matematyki, w której istnieje pojęcia liczby rzeczywistej od realizacji obliczeń zmiennoprzecinkowych w procesorach.

W artykule pojawia się szereg błędnych sugestii (np. 59.95 x 1000000000 jest średnio związane z 59950000000 - z definicji, albo jeśli X=59.95, to następnie X==59.95 może zwrócić fałsz - jest to dość trywialne). Uważam, że bardzo dobrze, że autor zwraca uwagę na problem, w który mogą łatwo wdepnąć ludzie bez wiedzy, ale autor nie powinien silić się na tłumaczenie kwestii, o których nie ma pojęcia...

  #40 12.12.2014 09:29

Dodam jeszcze, że aby mieć jakiekolwiek pojęcie na temat tego o czym się mówi można zacząć np. od lektury tego: http://www.mimuw.edu.pl/~przemek/przemek1_files/mobl_slajdy.pdf

I tak np. kwestia "SELECT cena*1000000000 FROM towar WHERE id_towar=1" jest omówiona od slajdu 96.

  #41 04.04.2015 13:07

Co się dzieje z polską edukacją, że mało kto wie jaka jest prawidłowa deklinacja zaimków osobowych? Nie chcę wyjść na takiego co się czepia. Jednak razi mnie:
"...wpisaliśmy po to by ukryć tą niedokładność..."
"...maja tą samą wadę..."
Poprawnie:
"...wpisaliśmy po to by ukryć tę niedokładność..."
"...maja tę samą wadę..."
Formy użyte przez autora w języku mówionym-potocznym są dopuszczalne - w PISANYM NIE!
Cóż jednak zrobić gdy magistery, profesory i doktory "tą" pisownię uznają za poprawną.
Więcej: http://pl.wiktionary.org/wiki/Aneks:Język_polski_-_zaimki#ten.2C_ta.2C_to.2C_ci....

Poza tym treść bardzo przydatna.

  #42 07.04.2015 11:09

@StawikPiast:
Nie tyle związane jest to z konstrukcją procesorów, co z formatem tych liczb i faktu, że są one przeznaczone do obliczeń przybliżonych.
http://kaczus.ppa.pl/art/liczbyzmiennoprzecinkowe,19.html - trochę więcej przykładów jak nie stosować liczb zmiennoprzecinkowych.

  #43 07.04.2015 11:11

@StawikPiast:
Nie tyle związane jest to z konstrukcją procesorów, co z formatem tych liczb i faktu, że są one przeznaczone do obliczeń przybliżonych.
http://kaczus.ppa.pl/art/liczbyzmiennoprzecinkowe,19.html - trochę więcej przykładów jak nie stosować liczb zmiennoprzecinkowych.

  #44 22.09.2015 14:42

Nie rozumiem dlaczego tak bardzo radzisz stosowanie np. "DECIMAL(8,2)", skoro ten szeroko opisany problem rozwiązuje "FLOAT(8,2)". Zachowany jest typ float, a jednocześnie znika problem precyzji.

  #45 12.01.2016 02:05

Dzięki! Takie wpisy są jak oazy na pustyni, czasem ratują z poważnych tarapatów. Liczba która mnie dobijała to FLOAT 9,4.