CODEWITHSUNDEEP
databasefloating-pointmariadb
bananensplit
bananensplit

Reputation: 57

Limit number of positions after decimal point in MariaDB

I am currently making a small MariaDB database and ran into the following problem: I want to save a floatingpoint number with only 2 poistions after the decimal point but everything before the decimal point should be unaffected.

For example: 1.11; 56789.12; 9999.00; 999999999999.01 etc.

I have done some research and this is what I am using right now:

CREATE TABLE mytable (
    mynumber DOUBLE(10, 2)
)

The problem with this solution is that I also have to limit the number of positions before the decimal point, what I don't want to do.

So is there a possibility to limit the number of positions after the decimal point without affecting the positions before the decimal point or is there a "default number" I can use for the positions before the decimal point?

Upvotes: 2

Views: 961

Answers (2)

Rick James
Rick James

Reputation: 142298

Don't use (m,n) with FLOAT or DOUBLE. It does nothing useful; it does cause an extra round.

DECIMAL(10,2) is possible; that will store numbers precisely (to 2 decimal places).

See also ROUND() and FORMAT() for controlling the rounding for specific values.

You had a mistake -- 999999999999.01 won't fit in DOUBLE(10,2), nor DECIMAL(10,2). It can handle only 8 (=10-2) digits to the left of the decimal point.

Upvotes: 1

Gordan Bobić
Gordan Bobić

Reputation: 1878

You can create a trigger that intercepts INSERT and UPDATE statements and truncates their value to 2 decimal places. Note, however, that due to how floating point numbers work at machine level, the actual number may be different.

Double precision numbers are accurate up to 14 significant figures, not a certain number of decimal points. Realistically, you need to detemine what is the biggest value you might ever want to store. Once you have done that, the DECIMAL type may be more appropriate for what you are trying to do.

See here for more details: https://dev.mysql.com/doc/refman/8.0/en/precision-math-decimal-characteristics.html

Upvotes: 1

Related Questions