MySQL decimal memory usage (signed v unsigned)

Question

I understand the memory storage requirements for the MySQL decimal number (eg. DECIMAL(5,2) requires 2 bytes for the 3 integers and 1 for the decimals. But I notice that decimal can also be signed or unsigned.

With numbers such as TINYINT, the range varies depending on whether it is signed (-128 to 127) or unsigned (0-255). My question then is does the memory usage for decimals change if they are signed or unsigned? Would -999.99 to 999.99 use the same memory as 000.00 to 999.99?

Answer 1

Would -999.99 to 999.99 use the same memory as 000.00 to 999.99?

Short answer: Yes.

Longer answer:

• The digits to the left of the decimal point and the digits to right are each considered to be whole numbers.
• Those on the left have a sign, but it does not take up any extra space.
• Each group (on left or right) of 9 digits fits in 4 bytes; less than 9 takes up ceil(N/2) bytes.

A simple rule of thumb: "DECIMAL(m,n) takes m/2 bytes." It's not always exact, but it is very close.

I think the last major change in DECIMAL was in 5.0.5.