How is DECIMAL(m, n) represented in a 64-bit system?

user_1723615 0
Viewed 70

What is the relationship between a DECIMAL(m,n) column specification and the actual representation of that column in a 64-bit MySQL implementation?

I'm defining tables in a context where I know I need an exact value (hence DECIMAL) and I don't know how sensitive I am to truncation errors in the decimal portion. I'd therefore like to choose a column specification that makes reasonable use of the underlying storage (I know it's a 64-bit system).

I haven't yet found an answer in the MySQL documentation despite a reasonable search.

mysql
1 Answers

It doesn't matter if you're using a 64-bit MySQL build. DECIMAL supports precision greater than 64 bits.

DECIMAL uses 4 bytes for each 9 digits, plus extra bytes for the leftover digits. For example, DECIMAL(32,0) supports up to 9+9+9+5 digits. It will use 4+4+4 bytes for the first 27 digits, then 3 more bytes for the remaining 5 digits. A total of 12+3, or 15 bytes.

The fractional part of the decimal value (after the decimal point) stores digits similarly. So DECIMAL(32,9) would support up to 9+9+5 digits for the integer portion and another 9 digits for the fractional portion. Thus 4+4+3 bytes for the integer and 4 bytes for the fractional part.

There's a more detailed description with examples down to the byte, in the code comments for the decimal2bin() function here: https://github.com/mysql/mysql-server/blob/8.0/strings/decimal.cc#L1282-L1343

Bill_Karwin_208600
Related