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

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.

CodePudding user response：

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

CodePudding user response：

To define a column whose data type is DECIMAL you use the following syntax:

column_name  DECIMAL(m,n);

In the syntax above:

m is the precision that represents the number of significant digits. The range of m is 1 to 65.
n is the scale that represents the number of digits after the decimal point. The range of n is 0 and 30. MySQL requires that n is less than or equal to (<=) m.

The DECIMAL(m,n) means that the column can store up to m digits with n decimals. The actual range of the decimal column depends on the precision and scale.

Besides the DECIMAL keyword, you can also use DEC, FIXED, or NUMERIC because they are synonyms for DECIMAL.