Why are some numbers converted after load data local infile?

Question

I have the following setup:

> show create table nums;
+-------+--------------------------------------------------------------------+
| Table | Create Table                                                       |
|-------+--------------------------------------------------------------------|
| nums  | CREATE TABLE `nums` (                                              |
|       |   `num` decimal(15,8) DEFAULT NULL                                 |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------+

$ cat num_file
8.04
$ echo "load data local infile 'num_file' into table nums (@num) 
set num=truncate(@num,8);" | mysql

I would expect that the table would contain 8.04, but instead:

> select * from nums;
+------------+
| num        |
|------------|
| 8.03999999 |
+------------+

Why is this happening, and how can I fix it?

Answer 1

Here are the issues:

• mysql's load data documentation indicates "LOAD DATA regards all input as strings", so "8.04" is being read as a string, not a numeral
• truncate() documentation indicates "When the first argument is of any floating-point type or of any non-numeric type, the return type is always DOUBLE"
  • Since a string is a non-numeric type, truncate is returning a DOUBLE, which is an approximated value; as the documentation indicates, "floating-point values are approximate and not stored as exact values".

To solve this, you can cast the input to decimal so truncate() will return a decimal; as the truncate() documentation states, "When the first argument is a DECIMAL value, the return type is also DECIMAL":

set num=truncate(cast(@num as decimal(15,8)),8)