Post
by alexa » Mon 30 Sep 2013 16:33
Hello Micha,
Please note that DOUBLE supports 15 decimal places. However, internally, MySQL stores 17 signs following the comma. That's why you are seeing such result.
If you need all the 17 signs to be displayed, you can use CAST. For example:
SELECT CAST(my_column AS DECIMAL(20, 18)) FROM my_table
In this situation we recommend to replace the DOUBLE type with DECIMAL one.
For better clarity of the described above, let's consider the following script:
USE test;
DROP TABLE IF EXISTS double_test;
CREATE TABLE double_test (
double_column double DEFAULT NULL,
decimal_column decimal(20, 18) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
INSERT INTO double_test VALUES(31.6500000000002, 31.6500000000002); -- 15
INSERT INTO double_test VALUES(31.65000000000002, 31.65000000000002); -- 16
INSERT INTO double_test VALUES(31.650000000000002, 31.650000000000002); -- 17
INSERT INTO double_test VALUES(31.6500000000000002, 31.6500000000000002); -- 18
SELECT double_column, CAST(double_column AS decimal(20, 18)), decimal_column FROM double_test
