MySQL - 使用 'load data infile"导入巨大的 csv

Question

我需要一些帮助。我创建的表结构如下:

CREATE TABLE `my_data` (
`Date` VARCHAR(45) NOT NULL, 
`test1` double,
`check1` int,
`test2` double,
`check2` int,
`No` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(No)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我的 csv 数据是巨大的 5GB 及以上文件。它每秒捕获数据。每秒的数据可能相同，但信息是有效的。如何导入所有重复项？当我尝试使用以下命令时，系统不断消除重复项。

LOAD DATA LOCAL INFILE 'D:/mydatatable.csv' INTO TABLE my_data FIELDS TERMINATED BY ',' enclosed by '"' lines terminated by '\n' IGNORE 1 LINES

这是 csv 的示例记录

<style>
	.demo {
		border:1px solid #C0C0C0;
		border-collapse:collapse;
		padding:5px;
	}
	.demo th {
		border:1px solid #C0C0C0;
		padding:5px;
		background:#F0F0F0;
	}
	.demo td {
		border:1px solid #C0C0C0;
		padding:5px;
	}
</style>
<table class="demo">
	<caption>Table 1</caption>
	<thead>
	<tr>
		<th>date/time</th>
		<th>A</th>
		<th>B</th>
		<th>C</th>
	</tr>
	</thead>
	<tbody>
	<tr>
		<td>2/23/2015 0:42</td>
		<td>3</td>
		<td>4</td>
		<td>2</td>
	</tr>
	<tr>
		<td>2/23/2015 0:42</td>
		<td>3</td>
		<td>4</td>
		<td>2</td>
	</tr>
	<tr>
		<td>2/23/2015 0:42</td>
		<td>3</td>
		<td>4</td>
		<td>2</td>
	</tr>
	<tr>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
	</tr>
	</tbody>
</table>​

CSV 数据:

2/23/2015 0:42,3,4,2
2/23/2015 0:42,3,4,2
2/23/2015 0:42,3,4,2

Answer 1

就我个人而言，我无法重现该问题，您确定您的行是由 \n 而不是 \r\n 终止的吗？

首先，我会尝试将 auto_increment 列移动为第一列。

ALTER TABLE `my_data`
    CHANGE COLUMN `NO` `NO` INT(11) NOT NULL AUTO_INCREMENT FIRST;

然后，我显式定义列，以便它们根据导入的数据正确表示，而不是隐含的。

LOAD DATA LOCAL INFILE 'D:/mydatatable.csv' 
    IGNORE INTO TABLE `my_data` 
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    IGNORE 1 LINES (`DATE`, `test1`, `check1`, `test2`, `check2`);

这将确保 CSV 中是否存在任何额外的列数据，该数据将被忽略，以确保 auto_increment 列不会被 '' 污染或0等。

最终结果

mysql> LOAD DATA LOCAL INFILE 'D:/mydatatable.csv'
    -> IGNORE INTO TABLE `test`.`my_data`
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES (`DATE`, `test1`, `check1`, `test2`, `check2`);
Query OK, 3 rows affected, 3 warnings (0.04 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1261 | Row 1 doesn't contain data for all columns |
| Warning | 1261 | Row 2 doesn't contain data for all columns |
| Warning | 1261 | Row 3 doesn't contain data for all columns |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM my_data;
+----+----------------+-------+--------+-------+--------+
| NO | DATE           | test1 | check1 | test2 | check2 |
+----+----------------+-------+--------+-------+--------+
|  1 | 2/23/2015 0:42 |     3 |      4 |     2 |   NULL |
|  2 | 2/23/2015 0:42 |     3 |      4 |     2 |   NULL |
|  3 | 2/23/2015 0:42 |     3 |      4 |     2 |   NULL |
+----+----------------+-------+--------+-------+--------+
3 rows in set (0.00 sec)