MySQL – Why NUMERIC_SCALE is NULL for DATA_TYPE=’double’?

information-schemaMySQL

I have created a column whose data_type is DOUBLE.

alter table my_table add column my_column double;

When I queried about this column in information_schema.COLUMNS table
I found that the NUMERIC_SCALE for this column is NULL?

select DATA_TYPE,
NUMERIC_PRECISION,
NUMERIC_SCALE

from information_schema.columns

where table_schema = 'my_schema'
and table_name = 'my_table'
and column_name = 'my_column'

+-----------+-------------------+---------------+
| DATA_TYPE | NUMERIC_PRECISION | NUMERIC_SCALE |
+-----------+-------------------+---------------+
| double    |                22 |          NULL |
+-----------+-------------------+---------------+

Can someone explain this behavior?

Best Answer

Floating point numbers are exactly that -- there's no fixed position for the decimal separator (point), it can be anywhere, and specifying scale (i.e. the number of digits after the decimal separator) does not make sense.

Scale is used for the DECIMAL data type, a type with the fixed decimal separator, for which scale is significant and meaningful.

MySQL also allows a non-standard syntax for floating point numbers, where you can specify precision and scale, e.g. DOUBLE(10, 5). MySQL will round floating point values to that specification when storing them, and format them appropriately when returning query results.

Documentation link.

dbfiddle example.

Related Question