Różnica między typem danych zmiennoprzecinkowych i dziesiętnych

Jaka to różnica, gdy używam typów danych typu float i decimal w MySQL?

Kiedy należy użyć którego?

Oto, co znalazłem, kiedy miałem te wątpliwości.

mysql> create table numbers (a decimal(10,2), b float);
mysql> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*************************** 1. row ***************************
  @a := (a/3): 33.333333333
  @b := (b/3): 33.333333333333
@a + @a + @a: 99.999999999000000000000000000000
@b + @b + @b: 100

Liczba dziesiętna zrobiła dokładnie to, co powinno zrobić w tych przypadkach, obcięła resztę, tracąc w ten sposób część 1/3.

Tak więc w przypadku sum liczba dziesiętna jest lepsza, ale w przypadku dzielenia liczba zmiennoprzecinkowa jest lepsza, oczywiście do pewnego momentu. Chodzi mi o to, że użycie DECIMAL w żaden sposób nie da ci „arytmetyki odpornej na błędy”.

Mam nadzieję że to pomoże.

W większości środowisk „zmiennoprzecinkowy” jest binarnym typem zmiennoprzecinkowym. Może dokładnie przechowywać wartości o podstawie 2 (do określonego punktu), ale nie może dokładnie przechowywać wielu wartości o podstawie 10 (dziesiętnych). Pływaki są najbardziej odpowiednie do obliczeń naukowych. Nie są one odpowiednie dla większości matematyki zorientowanej na biznes, a niewłaściwe użycie pływaków może Cię ugryźć. Wiele wartości dziesiętnych nie może być dokładnie przedstawionych w systemie dwójkowym. 0.1nie może, na przykład, więc widzisz dziwne wyniki, takie jak 1.0 - 0.1 = 0.8999999.

Ułamki dziesiętne przechowują liczby dziesiętne. Dziesiętny jest dobrym typem dla większości matematyki biznesowej (ale każdy wbudowany typ „pieniądze” jest bardziej odpowiedni do obliczeń finansowych), w którym zakres wartości przekracza zakres zapewniany przez typy całkowite i potrzebne są wartości ułamkowe. Liczby dziesiętne, jak sama nazwa wskazuje, są przeznaczone dla liczb dziesiętnych - mogą dokładnie przechowywać wartości dziesiętne (ponownie do określonego punktu).

MySQL ostatnio zmienił sposób przechowywania typu DECIMAL . W przeszłości zapisywali znaki (lub nybble) dla każdej cyfry zawierającej ASCII (lub nybble) reprezentację liczby całkowitej - vs - uzupełnienia do dwóch lub jej pochodną.

Bieżący format przechowywania dla DECIMAL to seria 1, 2, 3 lub 4-bajtowych liczb całkowitych, których bity są łączone w celu utworzenia liczby uzupełnienia do dwóch z niejawnym punktem dziesiętnym, zdefiniowanym przez Ciebie i przechowywanym w schemacie bazy danych, kiedy deklarujesz kolumnę i określ jej rozmiar DECIMAL i pozycję przecinka.

Na przykład, jeśli weźmiesz 32-bitową liczbę int, możesz zapisać dowolną liczbę z zakresu 0 - 4 294 967 295. To niezawodnie pokryje tylko 999 999 999, więc jeśli wyrzucisz 2 bity i użyjesz (1 << 30 -1), nic nie zrezygnujesz. Pokrycie wszystkich 9-cyfrowych liczb tylko 4 bajtami jest bardziej wydajne niż pokrycie 4 cyfr w 32 bitach przy użyciu 4 znaków ASCII lub 8 cyfr typu nybble. (nybble ma 4 bity, dopuszczając wartości 0-15, więcej niż jest to potrzebne dla 0-9, ale nie można wyeliminować tego marnotrawstwa, przechodząc do 3 bitów, ponieważ obejmuje to tylko wartości 0-7)

Przykład użyty w dokumentach online MySQL używa jako przykładu DECIMAL (18,9). Jest to 9 cyfr przed i 9 cyfr za domniemanym przecinkiem dziesiętnym, co, jak wyjaśniono powyżej, wymaga następującego przechowywania.

Jak 18 8-bitowych znaków: 144 bity

Jako 18 4-bitowych plików nyblowych: 72 bity

Jako 2 32-bitowe liczby całkowite: 64 bity

Obecnie DECIMAL obsługuje maksymalnie 65 cyfr, jako DECIMAL (M, D), gdzie największa dozwolona wartość M to 65, a największa dozwolona wartość D to 30.

Aby nie wymagać jednorazowo fragmentów 9 cyfr, do dodawania cyfr przy użyciu liczb całkowitych 1, 2 i 3 bajtowych są używane liczby całkowite mniejsze niż 32 bity. Z jakiegoś powodu, który jest sprzeczny z logiką, użyto podpisanych zamiast niepodpisanych intów, a robiąc to, 1 bit zostaje wyrzucony, co daje następujące możliwości przechowywania. W przypadku liczb całkowitych 1,2 i 4-bajtowych utracony bit nie ma znaczenia, ale dla int 3-bajtowych jest to katastrofa, ponieważ utrata jednego bitu powoduje utratę całej cyfry.

Z 7-bitowym int: 0 - 99

Z 15-bitowym int: 0 - 9 999

Z 23-bitowym int: 0 - 999 999 (0 - 9 999 999 z 24-bitowym int)

Liczby całkowite 1, 2, 3 i 4-bajtowe są łączone razem w celu utworzenia „puli bitów”, której DECIMAL używa do dokładnego przedstawienia liczby jako liczby całkowitej uzupełnionej do dwóch. Kropka dziesiętna NIE jest przechowywana, to jest domniemane.

Oznacza to, że silnik DB nie wymaga żadnych konwersji ASCII na int, aby przekształcić „liczbę” w coś, co procesor rozpoznaje jako liczbę. Bez zaokrąglania, bez błędów konwersji, to rzeczywista liczba, którą procesor może manipulować.

Obliczenia na tej arbitralnie dużej liczbie całkowitej muszą być wykonywane w oprogramowaniu, ponieważ nie ma sprzętowego wsparcia dla tego rodzaju liczb, ale te biblioteki są bardzo stare i wysoce zoptymalizowane, ponieważ zostały napisane 50 lat temu, aby obsługiwać dane zmiennoprzecinkowe o arbitralnej precyzji IBM 370 Fortran . Nadal są znacznie wolniejsze niż algebra liczb całkowitych o stałym rozmiarze, wykonywane za pomocą sprzętu do obliczeń całkowitych procesora lub obliczenia zmiennoprzecinkowe wykonywane na FPU.

Pod względem wydajności pamięci masowej, ponieważ wykładnik liczby zmiennoprzecinkowej jest dołączony do każdej liczby zmiennoprzecinkowej, określając niejawnie, gdzie znajduje się kropka dziesiętna, jest on ogromnie nadmiarowy, a zatem nieefektywny dla pracy DB. W bazie danych już wiesz, gdzie kropka dziesiętna ma być umieszczona na początku, a każdy wiersz w tabeli, który ma wartość kolumny DECIMAL, musi tylko spojrzeć na 1 i jedyną specyfikację miejsca, w którym ta kropka dziesiętna ma być umieszczona, przechowywana w schemacie jako argumenty do DECIMAL (M, D) jako implikacja wartości M i D.

Wiele uwag na temat tego, który format ma być używany w różnych rodzajach aplikacji, jest poprawnych, więc nie będę się rozpisywał. Poświęciłem trochę czasu, aby napisać to tutaj, ponieważ ktokolwiek utrzymuje połączoną dokumentację online MySQL, nie rozumie żadnego z powyższych i po rundach coraz bardziej frustrujących prób wyjaśnienia im tego poddałem się. Dobrym wskaźnikiem tego, jak słabo zrozumieli to, co napisali, jest bardzo niejasne i prawie nieczytelne przedstawienie tematu.

Na koniec, jeśli potrzebujesz bardzo precyzyjnych obliczeń zmiennoprzecinkowych, w ciągu ostatnich 20 lat nastąpił ogromny postęp w kodzie zmiennoprzecinkowym, a wsparcie sprzętowe dla 96-bitowych i poczwórnej precyzji float jest tuż za rogiem, ale istnieją dobre biblioteki o dowolnej precyzji, jeśli manipulacja przechowywaną wartością jest ważna.

Nie tylko typowe dla MySQL, różnica między typami zmiennoprzecinkowymi i dziesiętnymi polega na sposobie, w jaki reprezentują one wartości ułamkowe. Typy zmiennoprzecinkowe reprezentują ułamki w formacie binarnym, które mogą reprezentować tylko wartości jako {m*2^n | m, n Integers}. wartości takie jak 1/5 nie mogą być precyzyjnie przedstawione (bez błędu zaokrąglenia). Liczby dziesiętne są podobnie ograniczone, ale reprezentują liczby takie jak {m*10^n | m, n Integers}. Ułamki dziesiętne nadal nie mogą przedstawiać liczb takich jak 1/3, ale w wielu typowych dziedzinach, takich jak finanse, często zdarza się, że oczekuje się, że pewne ułamki dziesiętne można zawsze wyrazić bez utraty wierności. Ponieważ liczba dziesiętna może oznaczać wartość taką jak $0.20(jedna piąta dolara), jest preferowana w takich sytuacjach.

liczba dziesiętna dotyczy stałych ilości, takich jak pieniądze, w przypadku których wymagana jest określona liczba miejsc dziesiętnych. Liczba zmiennoprzecinkowa służy do przechowywania ... liczb zmiennoprzecinkowych o precyzji.

Znalazłem to przydatne:

Ogólnie rzecz biorąc, wartości zmiennoprzecinkowe są dobre do obliczeń naukowych, ale nie powinny być używane do wartości finansowych / pieniężnych. W przypadku matematyki zorientowanej na biznes zawsze używaj liczby dziesiętnej.

Źródło: http://code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/

mysql> CREATE TABLE num(id int ,fl float,dc dec(5,2));
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO num VALUES(1,13.75,13.75);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO num VALUES(2,13.15,13.15);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM num WHERE fl = 13.15;
Empty set (0.00 sec)

mysql> SELECT * FROM num WHERE dc = 13.15;
+------+-------+-------+
| id   | fl    | dc    |
+------+-------+-------+
|    2 | 13.15 | 13.15 |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> SELECT SUM(fl) ,SUM(dc)  FROM num;
+--------------------+---------+
| SUM(fl)            | SUM(dc) |
+--------------------+---------+
| 26.899999618530273 |   26.90 |
+--------------------+---------+
1 row in set (0.00 sec)


mysql> SELECT * FROM num WHERE ABS(fl -  13.15)<0.01;
+------+-------+-------+
| id   | fl    | dc    |
+------+-------+-------+
|    2 | 13.15 | 13.15 |
+------+-------+-------+
1 row in set (0.00 sec)

Jeśli zależy Ci na wydajności, a nie na precyzji, powinieneś zauważyć, że obliczenia z liczbami zmiennoprzecinkowymi są znacznie szybsze niż ułamki dziesiętne

Typy zmiennoprzecinkowe (przybliżona wartość) - FLOAT, DOUBLE

Typy FLOAT i DOUBLE reprezentują przybliżone liczbowe wartości danych. MySQL wykorzystuje cztery bajty na wartości o pojedynczej precyzji i osiem bajtów na wartości o podwójnej precyzji.

W przypadku typu FLOAT standard SQL zezwala na opcjonalne określenie dokładności (ale nie zakresu wykładnika) w bitach następujących po słowie kluczowym FLOAT w nawiasach. MySQL obsługuje również tę opcjonalną specyfikację dokładności, ale wartość precyzji jest używana tylko do określenia rozmiaru pamięci. Precyzja od 0 do 23 daje w wyniku 4-bajtową kolumnę typu FLOAT o pojedynczej precyzji. Precyzja od 24 do 53 daje 8-bajtową kolumnę DOUBLE o podwójnej precyzji.

MySQL dopuszcza niestandardową składnię: FLOAT (M, D) lub REAL (M, D) lub DOUBLE PRECISION (M, D). W tym przypadku „(M, D)” oznacza, że ​​wartości mogą być przechowywane łącznie z maksymalnie M cyframi, z których D cyfr może znajdować się po przecinku. Na przykład kolumna zdefiniowana jako FLOAT (7,4) po wyświetleniu będzie wyglądać jak -999,9999. MySQL wykonuje zaokrąglanie podczas przechowywania wartości, więc jeśli wstawisz 999.00009 do kolumny FLOAT (7,4), przybliżony wynik to 999.0001.

Ponieważ wartości zmiennoprzecinkowe są przybliżone i nie są przechowywane jako dokładne wartości, próby traktowania ich jako dokładnych w porównaniach mogą prowadzić do problemów. Podlegają również zależnościom platformy lub implementacji.

Aby zapewnić maksymalną przenośność, kod wymagający przechowywania przybliżonych wartości danych liczbowych powinien używać funkcji FLOAT lub DOUBLE PRECISION bez określenia precyzji lub liczby cyfr.

https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html

Problemy z wartościami zmiennoprzecinkowymi

Liczby zmiennoprzecinkowe czasami powodują zamieszanie, ponieważ są przybliżone i nie są przechowywane jako dokładne wartości . Wartość zmiennoprzecinkowa zapisana w instrukcji SQL może nie być taka sama, jak wartość reprezentowana wewnętrznie. Próby traktowania wartości zmiennoprzecinkowych jako dokładnych w porównaniach mogą prowadzić do problemów. Podlegają również zależnościom platformy lub implementacji. Typy danych FLOAT i DOUBLE podlegają tym problemom. W przypadku kolumn DECIMAL MySQL wykonuje operacje z dokładnością do 65 cyfr dziesiętnych, co powinno rozwiązać większość typowych problemów z niedokładnością.

Poniższy przykład używa DOUBLE, aby zademonstrować, w jaki sposób obliczenia wykonywane przy użyciu operacji zmiennoprzecinkowych podlegają błędom zmiennoprzecinkowym.

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
    -> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
    -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
    -> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
    -> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
    -> (6, 0.00, 0.00), (6, -51.40, 0.00);

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;

+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    1 |  21.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

Wynik jest poprawny. Chociaż wygląda na to, że pierwsze pięć rekordów nie powinno odpowiadać porównaniu (wartości a i b nie wydają się różne), mogą to zrobić, ponieważ różnica między liczbami pojawia się w okolicach dziesiątego miejsca po przecinku, w zależności od czynników takie jak architektura komputera, wersja kompilatora lub poziom optymalizacji. Na przykład różne procesory mogą inaczej oceniać liczby zmiennoprzecinkowe.

Gdyby kolumny d1 i d2 zostały zdefiniowane jako DECIMAL, a nie DOUBLE, wynik zapytania SELECT zawierałby tylko jeden wiersz - ostatni pokazany powyżej.

Prawidłowym sposobem porównania liczb zmiennoprzecinkowych jest najpierw podjęcie decyzji o akceptowalnej tolerancji dla różnic między liczbami, a następnie porównanie z wartością tolerancji. Na przykład, jeśli zgodzimy się, że liczby zmiennoprzecinkowe należy traktować tak samo, jeśli są takie same z dokładnością do jednego na dziesięć tysięcy (0,0001), należy zapisać porównanie, aby znaleźć różnice większe niż wartość tolerancji:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    6 | -51.4 |    0 |
+------+-------+------+
1 row in set (0.00 sec)

Z drugiej strony, aby uzyskać wiersze, w których liczby są takie same, test powinien znaleźć różnice w wartości tolerancji:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) <= 0.0001;
+------+------+------+
| i    | a    | b    |
+------+------+------+
|    1 | 21.4 | 21.4 |
|    2 | 76.8 | 76.8 |
|    3 |  7.4 |  7.4 |
|    4 | 15.4 | 15.4 |
|    5 |  7.2 |  7.2 |
+------+------+------+
5 rows in set (0.03 sec)

Wartości zmiennoprzecinkowe podlegają zależnościom platformy lub implementacji. Załóżmy, że wykonujesz następujące instrukcje:

CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;

Na niektórych platformach instrukcja SELECT zwraca inf i -inf. Na innych zwraca 0 i -0.

Konsekwencją powyższych problemów jest to, że jeśli spróbujesz utworzyć replikację slave przez zrzucenie zawartości tabeli z mysqldump na master i ponowne załadowanie pliku zrzutu do slave, tabele zawierające kolumny zmiennoprzecinkowe mogą się różnić między dwoma hostami.

https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

Reguła twarda i szybka

Jeśli wszystko, co musisz zrobić, to dodać, odjąć lub pomnożyć liczby, które przechowujesz, najlepszym rozwiązaniem jest DECIMAL.

Jeśli chcesz podzielić lub wykonać jakąkolwiek inną formę arytmetyki lub algebry na danych, prawie na pewno będziesz szczęśliwszy z float. Biblioteki zmiennoprzecinkowe, a na procesorach Intela sam procesor zmiennoprzecinkowy mają TONY operacji korygujących, naprawiających, wykrywających i obsługujących lawinę wyjątków, które występują podczas wykonywania typowych funkcji matematycznych - zwłaszcza funkcji transcendentalnych.

Jeśli chodzi o dokładność, napisałem kiedyś system budżetowy, który obliczał procentowy wkład każdego z ponad 3000 kont, dla 3600 jednostek budżetowych, według miesiąca do węzła konsolidacji tej jednostki, a następnie w oparciu o tę macierz procentową (3000 + x 12 x 3600) Pomnożyłem kwoty budżetowane przez najwyższe węzły organizacyjne do kolejnych 3 poziomów węzłów organizacyjnych, a następnie obliczyłem z tego wszystkie (3000 + 12) wartości dla wszystkich 3200 jednostek szczegółowych. Miliony, miliony i miliony obliczeń zmiennoprzecinkowych podwójnej precyzji, z których każde spowodowałoby zrzucenie wszystkich tych prognoz w konsolidacji od dołu do najwyższego poziomu w organizacji.

Całkowity błąd zmiennoprzecinkowy po tych wszystkich obliczeniach wyniósł ZERO . To było w 1986 roku, a dzisiejsze biblioteki zmiennoprzecinkowe są dużo, dużo lepsze niż wtedy. Intel wykonuje wszystkie swoje pośrednie obliczenia podwojeń z 80-bitową precyzją, co praktycznie eliminuje błąd zaokrąglania. Kiedy ktoś mówi ci „to błąd zmiennoprzecinkowy”, prawie na pewno NIE jest prawdą.

float(i double) reprezentuje ułamki binarne

decimal reprezentuje ułamki dziesiętne

declare @float as float(10)
declare @Decimal as decimal(10)
declare @Inetger as int

set @float =10.7
set @Decimal =10.7
set @Inetger=@Decimal

print @Inetger

in float, gdy ustawiono wartość na integer print 10, ale w postaci dziesiętnej 11