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)
LOAD DATA local INFILE 'data.csv'
INTO TABLE finance.price
FIELDS TERMINATED BY ','
OPTIONAL ENCLOSED BY '"'
lines terminated by '\r\n';