główna strona
wróć
[MySQL] Jak zaokrąglić liczby w bazie MySQL?
Ostatnia zmiana: 22.11.2008, Autor artykułu: Waldemar Miotk
artykul-0015.jpg

Bardzo często podczas programowania stajemy przed dylematem zaokrąglania liczb. Potrzeby są najróżniejsze. Czasami zaokrąglamy w górę czasami w dół, czasami standardowo a czasami po prostu odcinamy część ułamkową. Może też wystąpić konieczność zaokrąglenia według zadanej dokładności. Czasami też będziemy musieli poradzić sobie z zadaniem zaokrąglenia ceny do np. 5 groszy.  W MySQL możemy wszystkie te rzeczy zrobić w prosty sposób korzystając z gotowych funkcji lub wykonując odpowiednie obliczenia.

W MySQL mamy kilka funkcji zdefiniowanych do zaokrąglania liczb:

  1. ceil() - zaokrągla liczby ułamkowe w górę;
  2. floor() - zaokrągla liczby ułamkowe w dół;
  3. round() - zaokrągla liczby ułamkowe zgodnie z przyjętymi standardami;
  4. truncate() - odcina część ułamkową liczby lub zmienia do pełnych dziesiątek, setek itp.

Funkcja ceil() zaokrągla liczbę ułamkową w górę:

  • SELECT CEIL(-5.1) = -5
  • SELECT CEIL(5.1) = 6
  • SELECT CEIL(0.1) = 1
  • SELECT CEIL(-0.1) = 0
  • SELECT CEIL(-5.94032) = -5
  • SELECT CEIL(5.94032) = 6
  • SELECT CEIL(5.5) = 6
  • SELECT CEIL(liczba) FROM tabela; # wynik : zaokrąglone w górę do liczby całkowitej pole liczba

Funkcja floor() zaokrągla liczby ułamkowe w dół (i wbrew pozorom nie jest to proste odcięcie części ułamkowej - patrz liczby ujemne):

  • SELECT FLOOR(-5.1) = -6
  • SELECT FLOOR(5.1) = 5
  • SELECT FLOOR(0.1) = 0
  • SELECT FLOOR(-0.1) = -1
  • SELECT FLOOR(-5.94032) = -6
  • SELECT FLOOR(5.94032) = 5
  • SELECT FLOOR(5.5) = 5
  • SELECT FLOOR(-5.5) = -6
  • SELECT FLOOR(liczba) FROM tabela; # wynik : zaokrąglone w dół do liczby całkowitej pole liczba

Funkcja round() zaokrągla liczby ułamkowe według standardowych reguł, czyli najmniej znaczącą 5 zaokrągla w górę (przy liczbach ujemnych w dół):

  • SELECT ROUND(-5.1) = -5
  • SELECT ROUND(5.1) = 5
  • SELECT ROUND(0.1) = 0
  • SELECT ROUND(-0.1) = 0
  • SELECT ROUND(-5.94032) = -6
  • SELECT ROUND(5.94032) = 6
  • SELECT ROUND(5.5) = 6
  • SELECT ROUND(-5.5) = -6
  • SELECT ROUND(liczba) FROM tabela; # wynik : zaokrąglone do liczby całkowitej pole liczba

Ponadto funkcja round() posiada dodatkową opcję oznaczającą dokładność obliczeń. Dokładność oznacza ilość miejsc po przecinku, które będą uwzględniane podczas zaokrąglania, np.:

  • SELECT ROUND(5.94032, 2) = 5.94
  • SELECT ROUND(5.96549, 2) = 5.97
  • SELECT ROUND(5.99549, 2) = 6.00
  • SELECT ROUND(-5.99549, 2) = -6.00
  • SELECT ROUND(liczba, 2) FROM tabela; # wynik : zaokrąglone do dwóch miejsc po przecinku pole liczba

Domyślną wartością jest 0, czyli zaokrąglanie do liczb całkowitych. Opcja dokładność ma jeszcze jedno ciekawe zastosowanie. Umożliwia zaokrąglanie liczb do pełnych dziesiątek, setek, tysięcy itd. Wystarczy wpisać jako dokładność liczbą ujemną np.:

  • SELECT ROUND(593049, -1) = 593050
  • SELECT ROUND(593049, -2) = 593000
  • SELECT ROUND(593049, -3) = 593000
  • SELECT ROUND(593049, -4) = 590000
  • SELECT ROUND(593049, -5) = 600000
  • SELECT ROUND(liczba, -2) FROM tabela; # wynik : zaokrąglone do setek pole liczba

Podobnie jak funkcja round() działa funkcja truncate(), z tym że nie zaokrągla ona wartości a jedynie obcina końcówkę:

  • SELECT TRUNCATE(54.324, 0); # wynik : 54
  • SELECT TRUNCATE(-54.324, 0); # wynik : -54

Drugi parametr tej funkcji to parametr precyzji wskazujący jaka część ułamka ma pozostać:

  • SELECT TRUNCATE(54.324, 2); # wynik : 54.32
  • SELECT TRUNCATE(54.324, 1); # wynik : 54.3
  • SELECT TRUNCATE(-54.324, 2); # wynik : -54.32
  • SELECT TRUNCATE(-54.324, 1); # wynik : -54.3

Podając parametr w postaci liczby ujemnej możemy zerować najmniej znaczące cyfry znajdujące się po lewej stronie separatora dziesiętnego (.):

  • SELECT TRUNCATE(5432.4, -2); # wynik : 5400
  • SELECT TRUNCATE(5432.4, -1); # wynik : 5430
  • SELECT TRUNCATE(-5432.4, -2); # wynik : -5400
  • SELECT TRUNCATE(-5432.4, -1); # wynik : -5430
  • SELECT TRUNCATE(5432.4, -4); # wynik : 0

Istnieją też inne metody różnego rodzaju zaokrąglania liczb według potrzeb:

1. Aby odciąć część ułamkową najprościej zastosować funkcję truncate() lub zastosować odejmowanie dzielenia modulo:

  • SELECT -34.549 - (-34.549%1); #wynik : -34.000
  • SELECT 34.549 - (34.549%1); # wynik : 34.000
  • SELECT cena - (cena%1) FROM tabela; #wynik : cena z odciętymi groszami
  • SELECT TRUNCATE(cena, 0) FROM tabela; #wynik : cena z odciętymi groszami

2. Aby odciąć tylko część części ułamkowej stosujemy funkcję truncate() z parametrem precyzji lub odejmowanie dzielenia modulo z mnożeniem i dzieleniem:

  • SELECT 34.549 - (((34.549*100)%1)/100); # wynik : 34.54
  • SELECT 34.549 - (((34.549*10)%1)/10); # wynik : 34.5
  • SELECT -34.549 - (((-34.549*100)%1)/100); # wynik : -34.54
  • SELECT -34.549 - (((-34.549*10)%1)/10); # wynik : -34.5
  • SELECT TRUNCATE(cena , 2) FROM tabela; # wynik : pole cena z odciętymi wartościami mniejszymi niż 1 grosz
  • SELECT cena - (((cena*100)%1)/100) FROM tabela; # wynik : pole cena z odciętymi wartościami mniejszymi niż 1 grosz

3. Aby zaokrąglić w dół do wartości dziesiątych, setnych itd. stosujemy metodę dzielenia modulo, przy liczbach ujemnych odejmując dokładność:

  • SELECT 593049 - (593049%10); # wynik : 593040
  • SELECT 593049 - (593049%100); # wynik : 593000
  • SELECT -593049 - (-593049%10) - 10; # wynik : -593050
  • SELECT -593049 - (-593049%100) - 100; # wynik : -593100
  • SELECT cena - (cena%100) FROM tabela; # wynik : pole cena zaokrąglone do pełnych setek w dol

4. Aby zaokrąglić w górę do wartości dziesiątych, setnych itd. również stosujemy metodę dzielenia modulo tym razem dodając dokładność do liczb dodatnich:

  • SELECT 593049 - (593049%10) + 10; # wynik : 593050
  • SELECT 593049 - (593049%100) + 100; # wynik : 593100
  • SELECT -593049 - (-593049%10); # wynik : -593040
  • SELECT -593049 - (-593049%100); # wynik : -593000
  • SELECT cena - (cena%100) + 100 FROM tabela;  # wynik : pole cena zaokroglone do pelnych setek w gore

5. Aby zaokąglić liczbę do np. 5 groszy (czyli 12,43 do 12,40 a 12,47 do 12,45) w dół wykonujemy:

  • SELECT ROUND((12.43*100 - 12.43*100%5)/100, 2); # wynik : 12.40
  • SELECT ROUND((12.47*100 - 12.47*100%5)/100, 2); # wynik : 12.45
  • SELECT ROUND((cena*100 - cena*100%5)/100, 2); #wynik : pole cena zaokrąglone do 5 groszy w dół

6. Aby zaokąglić liczbę do np. 5 groszy (czyli 12,43 do 12,45 a 12,47 do 12,50) w górę wykonujemy:

  • SELECT ROUND((12.43*100 - 12.43*100%5)/100 + 0.05, 2); # wynik : 12.45
  • SELECT ROUND((12.47*100 - 12.47*100%5)/100 + 0.05, 2); # wynik : 12.50
  • SELECT ROUND((cena*100 - cena*100%5)/100 + 0.05, 2); #wynik : pole cena zaokrąglone do 5 groszy w górę
     
Komentarze(12)

Podpis:
W celu potwierdzenia, zaznacz pole pod znakiem: D
Capcha
jerzy - IP: xxx.xxx.146.130, Data: 18.03.2024 08:39:18
dzieki poszlo
Starszy pan - IP: xx.xx.12.79, Data: 28.10.2022 23:42:19
Fajny poradnik, zwięzły. Chwila czytania i znalazłem to czego potrzebowałem.
promil - IP: xx.xxx.170.214, Data: 06.10.2022 14:11:19
git to wyszło
tonder - IP: xx.x.176.173, Data: 13.01.2022 09:05:23
technik informatyk pozdrawia wszystkich co szukają na bazach danych xD btw fajny poradnik :)))
Domin - IP: xx.xx.32.51, Data: 03.02.2021 10:26:49
Wielkie dzięki !
Mario - IP: xx.x.66.51, Data: 19.09.2019 10:29:43
@Waldek - thx ;)
Waldek - IP: x.xx.15.70, Data: 19.09.2019 09:27:14
@Mario:

Problem dotyczy nie tylko MySQLa ale większości języków programowania i reprezentacji liczb zmiennoprzecinkowych w pamięci komputera/interpretera/kompilatora. Liczby te nie są zapisywane tak jak nam się wydaje, czyli tak jak je widzimy (liczba 2.1 zapisywana może być jako 2.099999999999998 a nie jako 2.1). Wszystko zależy też od architektury komputera na której program jest uruchamiany. Dlatego w praktyce programowania bardzo często możemy spotkać się z błędem podliczania takich liczb i niestety trzeba się do tego przyzwyczaić czyli stosować różne zabiegi dodatkowe-zaokrąglające przy podliczaniu. Jednym z nich jest używanie ROUND, gdyż liczba 2.09999999998 zostanie zaokrąglona do 2.1. A czasami działa zwykłe przemnożenie liczby zmiennoprzecinkowej przez 1. :)

Właśnie z tego powodu w swoich programach przechowuję liczby zmiennoprzecinkowe w polach tekstowych. Nie mam problemu z obliczeniami niestety powstaje problem z wydajnością tego rozwiązania. Jednak na szczęście moc współczesnych komputerów jest już na tyle duża, że nie powoduje to żadnych problemów w standardowych rozwiązaniach.

I mały, szybki przykład na koniec:
116,85000001 x 2,0999998 = 245,384976651 = 245,38 :)
116,85 x 2,1 = 245,385 = 245,39

Mario - IP: xx.x.66.51, Data: 19.09.2019 08:52:49
Masz zagadkę :)

zapisane w bazie w polu double albo float albo decimal wartości :
cena brutto = 116,85
ilosc = 2,1

update pole
wartosc_brutto = cena_brutto * ilosc
daje wynik 116,85 * 2,1 = 245.38


kalkulator / excel
116,85 * 2,1 = 245,39

select SQL
SELECT round(SUM(116.85*2.1),2) = 245,39
prawidłowe


Skąd w takim razie update daje tak dziwną wartość ?
Przemo - IP: xx.xxx.150.154, Data: 07.06.2019 15:43:02
6. Zaokrąglenie do 5gr nie powinno zaokrąglać 12zł do 12zł 5gr :)
Kamil Steinbach - IP: xxx.xxx.63.50, Data: 08.11.2018 10:07:25
Bardzo dobry poradnik. Pozdrawiam Kamil Steinbach.
Waldek - IP: xxx.xx.150.117, Data: 01.12.2016 21:56:43
Zaokrąglenie liczb w SQL rozwaliło serwer? Ciekawe... Czy mogę prosić o jakieś szczegóły, gdyż nie spotkałem się jeszcze z takim przypadkiem.
Zly - IP: xx.xxx.117.141, Data: 01.12.2016 20:09:30
Ten poradnik rozwalil mi serwer (arianeamt2.pl)
(c)2007-2016 Waldemar Miotk - ostatnia aktualizacja silnika 06.02.2016 - Twój IP: 3.233.242.216. Ta strona, aby lepiej działać, używa plików cookie przechowywanych na komputerach użytkowników. Wszystkie prawa do tekstów zamieszczonych na stronie są zastrzeżone, chyba że przy konkretnym tekście znajduje się inna informacja.
go up