From my understanding of the manual for DECIMAL in the mysql docs, it states that each multiple of nine digits requires 4 bytes and the following for left over digits;
number of digits leftover | bytes
0 | 0
1 | 1
2 | 1
3 | 2
4 | 2
5 | 3
6 | 3
7 | 4
8 | 4
So a decimal(12,6) would be equal to 6 bytes in total storage?
I know that the fractional part will always equal 6 digits as zeros are padded, but the integer part is not so for a DECIMAL(12,6) column does the storage get valued per row or at column definition?
Example
DECIMAL(12,6)
1: 178.999999 // 3 digits for integer part so total would be 5 bytes?
2: 0.880000 // 1 digits for integer part so total would be 4 bytes?
3 123456.123456 // 6 digits would equal 6 bytes?
Or will it always be 6 bytes if declared (12,6)?
EDIT
Also a decimal declared as (5,5) would take 6 bytes to store and a decimal declared as (12,6) would also take 6 bytes to store. Seeing as the storage size is not an issue would the difference in size affect how mysql retrieves or indexes the column?
解决方案
For MySQL 5.1 (and higher), it splits up the storage for the integer part and the fractional part, and makes room for storage for the largest possibly number. So for a DECIMAL(12,6), you need 3 bytes for the integer part and 3 bytes for the fractional part. It doesn't look like it decreases storage based on the value; it sets the memory aside no matter what the value is.
You can check out the documentation here: