[FIXED] Why can't load the data into mysql:

Issue

Mysql table price structure:

CREATE TABLE `price` (
  `code` varchar(12) ,
  `date` date ,
  `open` decimal(8,2) ,
  `high` decimal(8,2) ,
  `low` decimal(8,2) ,
  `close` decimal(8,2) ,
  `amount` decimal(20,2) ,
  `volume` decimal(16,2) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the data.csv file which i want to load into tablbe price.

000046.XSHE,19940912,20.0,20.0,16.0,16.92,121262592.0,7043300
000046.XSHE,19940913,17.0,17.32,16.0,16.46,47195860.0,2810800
000046.XSHE,19940914,16.3,16.4,15.49,15.95,24762992.0,1558300

The max value for volume is 47195860.0 which is in the range of decimal(20,2).

Load it with mysql load command.

LOAD DATA local INFILE 'data.csv'
    INTO TABLE finance.price
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    lines terminated by '\r\n';

An error info occurs:

Query OK, 1 row affected, 1 warning (0.09 sec)       
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

Show it to get the reason.

show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'volume' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

Number 121262592.0 is so lower than decimal(20,2),why Data truncated for column 'volume' at row 1,only one row loaded.

select * from price;
+-------------+------------+-------+-------+-------+-------+--------------+------------+
| code        | date       | open  | high  | low   | close | amount       | volume     |
+-------------+------------+-------+-------+-------+-------+--------------+------------+
| 000046.XSHE | 1994-09-12 | 20.00 | 20.00 | 16.00 | 16.92 | 121262592.00 | 7043300.00 |
+-------------+------------+-------+-------+-------+-------+--------------+------------+
1 row in set (0.00 sec)

Solution

LOAD DATA local INFILE 'data.csv'
    INTO TABLE finance.price
    FIELDS TERMINATED BY ','
    OPTIONAL  ENCLOSED BY '"'
    lines terminated by '\r\n';

Answered By - user7988893

Answer Checked By - Timothy Miller (PHPFixing Admin)