前面我們介紹過(guò)如何用select…into outfile語(yǔ)句將SQL查詢(xún)結(jié)果導(dǎo)出到文件：
MySQL 將查詢(xún)結(jié)果導(dǎo)出到文件（select … into Statement）

MySQL同時(shí)也提供互補(bǔ)的功能，可以使用load data infile語(yǔ)句將文件中的數(shù)據(jù)加載到數(shù)據(jù)庫(kù)中，這個(gè)文件可以是MySQL導(dǎo)出的文件或其他來(lái)源。本文將介紹load data infile語(yǔ)句的用法及在使用過(guò)程中常見(jiàn)問(wèn)題的解決方式。

一、load data語(yǔ)句簡(jiǎn)介

MySQL的load data infile語(yǔ)句可以從文本文件中讀取數(shù)據(jù)，并且加載到數(shù)據(jù)庫(kù)的表中。和select…into outfile只能導(dǎo)文件到本地?cái)?shù)據(jù)庫(kù)服務(wù)器不同，load data語(yǔ)句即可以從數(shù)據(jù)庫(kù)服務(wù)器本地讀取文件，也可以通過(guò)遠(yuǎn)程客戶(hù)端（使用local關(guān)鍵字）讀取，即可以遠(yuǎn)程將文件加載到數(shù)據(jù)庫(kù)中。

MySQL還提供了一個(gè)mysqlimport命令行工具也可以將數(shù)據(jù)從文件加載到數(shù)據(jù)庫(kù)中，其原理也是通過(guò)load data infile語(yǔ)句完成的。

二、用法示例

默認(rèn)情況下，load data infile語(yǔ)句是從數(shù)據(jù)庫(kù)服務(wù)器加載數(shù)據(jù)的，為了安全起見(jiàn)，一般MySQL都會(huì)配置secure_file_priv參數(shù)，來(lái)指定可以讀寫(xiě)文件的目錄，將要導(dǎo)入的文件放在此參數(shù)指定的目錄下。

show variables like 'secure_file_priv';

我們先通過(guò)導(dǎo)出數(shù)據(jù)的方式創(chuàng)建一個(gè)文件，這里在示例數(shù)據(jù)庫(kù)employees下新建一張測(cè)試表并插入幾條數(shù)據(jù)：

create table person(
id int not null auto_increment primary key,
name varchar(32),
salary decimal(10,2),
remark varchar(128));

insert into person values(null, 'Vincent', 1000, 'AAA');
insert into person values(null, 'Victor', 2000, 'BBB');
insert into person values(null, 'Grace', 3000, 'CCC');

數(shù)據(jù)內(nèi)容如下：

select * from person;

使用select…into outfile將數(shù)據(jù)導(dǎo)出到文件（路徑就是secure_file_priv參數(shù)指定的目錄），這里使用默認(rèn)格式導(dǎo)出：

select * from person into outfile '/opt/mysql8.0.35/mysql-files/person.txt';

導(dǎo)出的person.txt文件內(nèi)容如下（數(shù)據(jù)以tab分隔）：

2.1 基本用法

由于load data infile和select into outfile語(yǔ)句是互補(bǔ)的，所以它們的格式設(shè)定語(yǔ)法是一樣的。select…into outfile采用默認(rèn)格式導(dǎo)出的文件就是load data infile的默認(rèn)導(dǎo)入格式。這種情況下，直接指定文件名及要導(dǎo)入表名即可（這里先清空person表）：

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person;

select * from peron;

2.2 數(shù)據(jù)格式的處理

但也有很多情況數(shù)據(jù)的來(lái)源不是MySQL導(dǎo)出的文件，格式也不同。例如常用的CSV格式文件，我們手動(dòng)將剛才文件改為CSV格式（以逗號(hào)分隔數(shù)據(jù)），且第一行數(shù)據(jù)中remark字段還額外包含了一個(gè)逗號(hào)（紅框處）：

碰到這種和默認(rèn)格式不同的數(shù)據(jù)，MySQL就無(wú)法解析了，如果直接導(dǎo)入就會(huì)報(bào)錯(cuò)：

此時(shí)需要通過(guò)格式子句來(lái)告訴MySQL如何解析數(shù)據(jù)，默認(rèn)的格式子句如下：

fields terminated by '\t' encolded by '' escaped by '\\'
lines terminated by '\n' starting by ''

含義解釋?zhuān)?/p>

• fields 表示字段屬性，terminated by ‘\t’ 以制表符分割字段，enclosed by ‘’ 不包裹字段，escaped by ‘\’ 反斜線(xiàn)表示轉(zhuǎn)義符
• lines 表示行屬性，terminated by ‘\n’ \n代表?yè)Q行符，starting by ‘’ 行的起點(diǎn)字符是空。

我們分析一下這里數(shù)據(jù)的格式和默認(rèn)格式的區(qū)別，字段的分隔符是逗號(hào)，因此需要 fields terminated by ‘,’，指定逗號(hào)為分隔符。同時(shí)注意第一行的remak字段是"Hello, Vincent!“，引號(hào)中逗號(hào)又是數(shù)據(jù)內(nèi)容，這個(gè)逗號(hào)不能識(shí)別為分隔符，因此還需要指定 enclosed by '”'，指定雙引號(hào)之內(nèi)的內(nèi)容是一個(gè)字段。增加這個(gè)兩個(gè)子句后，可以看到數(shù)據(jù)格式識(shí)別成功：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"';

三、常見(jiàn)導(dǎo)入問(wèn)題的處理

除了基礎(chǔ)導(dǎo)入場(chǎng)景，我們可能還會(huì)遇到一些其他問(wèn)題或者數(shù)據(jù)加工需求，下面就是導(dǎo)入中常見(jiàn)問(wèn)題的解決方法。

3.1 標(biāo)題行的處理

如果文件的第一行是標(biāo)題而不是數(shù)據(jù)，那么在導(dǎo)入時(shí)我們就需要進(jìn)行忽略處理，你可以手動(dòng)從文本文件中刪除這一行?；蛘?，使用ignore n lines/rows子句來(lái)告訴MySQL導(dǎo)入時(shí)跳過(guò)前n行，我們上面的文本中再增加一行標(biāo)題：

導(dǎo)入時(shí)，通過(guò)ignore 1 lines/rows語(yǔ)句，忽略第一行：

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
ignore 1 lines;    -- 忽略第一行

select * from person;

可以看到第一行標(biāo)題并未導(dǎo)入，而是從第二行數(shù)據(jù)開(kāi)始讀取。

3.2 主鍵/唯一索引沖突的處理

上面的示例中，我們每次導(dǎo)入前都執(zhí)行了truncate table清空表，即每次都是往空表中導(dǎo)入。但如果表中已經(jīng)有數(shù)據(jù)了，導(dǎo)入時(shí)就可能發(fā)生主鍵/唯一索引沖突。向已有數(shù)據(jù)的表中導(dǎo)入數(shù)據(jù)時(shí)如果發(fā)生了主鍵/唯一索引沖突，我們有2個(gè)選擇：忽略或更新

• ignore，遇到鍵值沖突時(shí) 忽略數(shù)據(jù)
• replace，遇到鍵值沖突時(shí) 更新數(shù)據(jù)

手動(dòng)修改一下文件內(nèi)容，將Vincent的salary改為4000：

在into table語(yǔ)句前增加一個(gè)ignore關(guān)鍵字，這樣出現(xiàn)鍵值沖突時(shí)會(huì)忽略而不是報(bào)錯(cuò)：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' ignore into table person
fields terminated by ',' enclosed by '"';

可以看到Vincent的salary并沒(méi)有更新，同時(shí)日志提示忽略了3行。

在into table語(yǔ)句前增加一個(gè)replace關(guān)鍵字，這樣出現(xiàn)鍵值沖突時(shí)會(huì)更新數(shù)據(jù)：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' replace into table person
fields terminated by ',' enclosed by '"';

這里Vincent的salary被更新成了4000，日志中的Deleted 1說(shuō)明實(shí)際操作是將原數(shù)據(jù)刪除再插入數(shù)據(jù)。如果文件每次需要更新導(dǎo)入，那么replace關(guān)鍵字就很適合。

3.3 文件和表的列數(shù)量不同或順序不同

前面每次導(dǎo)入時(shí)我們都只提供了表名，這就要求文本中數(shù)據(jù)的列和數(shù)據(jù)庫(kù)中表的列數(shù)量要相同，并且順序是對(duì)應(yīng)的。如果文件中字段順序和表不同，或者字段數(shù)量不同，那么就需要手動(dòng)指定導(dǎo)入順序。

我們修改一下表結(jié)構(gòu)，在name和salary之間增加一個(gè)extra_column，此時(shí)表的字段就比文件中字段數(shù)多了，且順序也不對(duì)應(yīng)：

alter table person add extra_column int after name;

此時(shí)導(dǎo)入數(shù)據(jù)就需要根據(jù)文件中字段的順序來(lái)指定表的列名：

truncate table person;
load data infile '/opt/mysql8.0.35/mysql-files/person.txt' replace into table person
fields terminated by ',' enclosed by '"'
(id,name,salary,remark);    -- 指定導(dǎo)入的列名順序

注意列名是單獨(dú)放在語(yǔ)句最后（如果有set子句則在set語(yǔ)句之前），而不是緊跟在表名后。

3.4 導(dǎo)入部分列

如果只想將文件中的部分列導(dǎo)入數(shù)據(jù)庫(kù)，即丟棄部分列的數(shù)據(jù)。我們也可以通過(guò)指定列名的方式來(lái)實(shí)現(xiàn)，通過(guò)僅指定需要導(dǎo)入數(shù)據(jù)的列名，而想丟棄的數(shù)據(jù)用一個(gè)變量名來(lái)占位，這樣對(duì)應(yīng)的列數(shù)據(jù)就不會(huì)被導(dǎo)入到數(shù)據(jù)庫(kù)中。

例如導(dǎo)入數(shù)據(jù)時(shí)，僅想導(dǎo)入id,name,salary這3列，忽略remark列：

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
(id,name,salary,@var);

這里用@var來(lái)占位，而不是指定remark列名，因此remark列沒(méi)有數(shù)據(jù)導(dǎo)入，相當(dāng)于僅導(dǎo)入了部分列。

3.5 導(dǎo)入過(guò)程中處理數(shù)據(jù)

除了將數(shù)據(jù)原封不動(dòng)導(dǎo)入之外，load data infile語(yǔ)句還支持一個(gè)set子句讓你在導(dǎo)入過(guò)程中對(duì)數(shù)據(jù)進(jìn)行加工處理。

例如記錄導(dǎo)入時(shí)間，我們?cè)僭黾右粋€(gè)列import_time，用來(lái)記錄數(shù)據(jù)導(dǎo)入時(shí)間：

alter table person add import_time timestamp;

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
(id,name,salary,remark)
set import_time=current_timestamp;

select * from person;

在語(yǔ)句的最后，增加了一個(gè)set import_time=current_timestamp子句，它會(huì)導(dǎo)入時(shí)設(shè)置import_time列為當(dāng)前時(shí)間戳（雖然數(shù)據(jù)都不在文件中）。

對(duì)于想要加工的列，我們可以先將列賦給變量，然后對(duì)變量加工后，再通過(guò)set子句寫(xiě)入表的列，達(dá)到先加工后導(dǎo)入的效果。例如對(duì)于salary列，如果值小于3000，那么就加999：

truncate table person;

load data infile '/opt/mysql8.0.35/mysql-files/person.txt' into table person
fields terminated by ',' enclosed by '"'
(id,name,@sal,remark)
set salary=if(@sal<3000, @sal+999, @sal);

select * from person;

導(dǎo)入時(shí)，先將值賦給變量@sal，經(jīng)過(guò)if函數(shù)的加工后，再通過(guò)set子句將加工過(guò)后的值寫(xiě)入salary列，可以看到Victor的salary變成了2999。如果沒(méi)有set子句，那么salary列的值就丟棄了，就是上一節(jié)導(dǎo)入部分列的操作。

以上就是MySQL中l(wèi)oad data infile語(yǔ)句的用法及常見(jiàn)問(wèn)題的處理，熟練掌握后可以幫助你快速將數(shù)據(jù)從文件導(dǎo)入數(shù)據(jù)庫(kù)（一個(gè)常用的場(chǎng)景就是將Excel文件保存為CSV格式導(dǎo)入數(shù)據(jù)庫(kù)）。更多相關(guān)MySQL 文件導(dǎo)入數(shù)據(jù)庫(kù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論