mysql load data infile 的用法(40w数据 用了3-5秒导进mysql)

下面就是MySQL的load data infile的完整攻略：

什么是mysql load data infile

load data infile 是MySQL中一个加载数据的命令，它可以从指定的文本文件中读取数据，并插入到MySQL表中。这个命令通常用于导入大量的数据，它比INSERT语句快得多。在一些需要处理大量数据的应用场景中，load data infile 命令是非常受欢迎的。

mysql load data infile的使用场景

load data infile 命令常用于以下的场景：

• 数据库迁移
• 数据库数据导入
• 大量数据的批量处理

mysql load data infile的语法

load data infile的语法结构如下：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES
        [STARTING BY '']
        [TERMINATED BY '\n']
    ]
    [IGNORE number_lines
        [( field [, field] ... )]
    ]

下面对语法中的每个部分进行讲解：

• LOW_PRIORITY / CONCURRENT：选项用于控制插入操作的优先级。如果使用LOW_PRIORITY，插入操作将会在当前操作后执行。如果使用CONCURRENT，将会使用并行方式插入数据，这个选项仅在MyISAM存储引擎上有效。
• LOCAL：如果使用LOCAL选项，将会从客户端的本地文件中读取数据。如果不指定，将会从MySQL服务器上读取数据。
• file_name：指定输入文件的路径，可以是绝对路径或相对路径。
• REPLACE / IGNORE：在将输入数据插入表时，用REPLACE选项指定，如果表中存在重复数据，将会替换原有数据。如果使用IGNORE选项，将会忽略重复的数据。
• tbl_name：表名。
• FIELDS：用于指定输入文本文件的字段格式。
  • TERMINATED BY：字段之间的分隔符。
  • ENCLOSED BY：字段所使用的包围符号。
  • ESCAPED BY：用于转义输入文件中的特殊字符。
• LINES：用于指定输入文件中的行格式。
  • STARTING BY：指明行的起始符。
  • TERMINATED BY：指定输入文件中行的结束符。
• IGNORE number_lines：用于忽略文件开始的number_lines行。
• field：将输入文件中的某些字段映射到表中的某些字段。

mysql load data infile示例1

下面是一个示例，使用load data infile命令将数据导入到students表中。

首先，我们有一个students.txt文件，文件内容如下：

1   Joe
2   Mary
3   Mike
4   John

我们要将这些数据导入到students表中，students表的结构如下：

CREATE TABLE students (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用load data infile命令导入数据，命令如下：

LOAD DATA INFILE '/path/to/students.txt' 
INTO TABLE students
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(id,name);

上面的命令中，我们指定了输入文件的文件路径，以及需要将数据导入到的数据库表名和表格字段。因为输入文件中的字段之间的分隔符是tab (\t)，所以我们使用FIELDS TERMINATED BY '\t'指定了分隔符。由于每行数据都以换行符 (\n) 结束，所以使用LINES TERMINATED BY '\n'。最后，我们将文件中的第一列(id)映射到表中的id字段，第二列(name)映射到name字段。

mysql load data infile示例2

下面是另一个示例，使用load data infile命令从远程服务器中读取CSV文件，并将数据导入到MySQL表中。

我们运行以下命令将远程文件export.csv下载到本地：

scp user@server:/path/to/export.csv /path/to/local/

数据文件export.csv格式如下：

id,name,age,gender
1,John,25,Male
2,Mary,23,Female
3,Tom,30,Male
4,Jerry,28,Male

使用load data infile命令将CSV文件导入到students表格中，命令如下：

LOAD DATA INFILE '/path/to/local/export.csv' 
INTO TABLE students
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id,name,age,gender);

上面的命令中，我们指定了输入文件的文件路径，需要将数据导入的数据库表名和表格字段。输入文件中的字段之间用逗号分隔，所以我们使用FIELDS TERMINATED BY ','。输入文件中的某些字段被双引号包围，所么我们使用OPTIONALLY ENCLOSED BY '"'进行转义。由于每行数据都以换行符 (\n) 结束，所以使用LINES TERMINATED BY '\n'。IGNORE 1 LINES选项指定忽略文件开始的一行数据，因为这是header行，不属于数据。

以上就是mysql load data infile的完整攻略了。读者可以按照上面的语法和示例进行实践，以达到更熟练地使用mysql load data infile命令的目的。