前面我們介紹過如何用select…into outfile語句將SQL查詢結(jié)果導出到文件：
MySQL 將查詢結(jié)果導出到文件（select … into Statement）

MySQL同時也提供互補的功能，可以使用load data infile語句將文件中的數(shù)據(jù)加載到數(shù)據(jù)庫中，這個文件可以是MySQL導出的文件或其他來源。本文將介紹load data infile語句的用法及在使用過程中常見問題的解決方式。

一、load data語句簡介

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

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

二、用法示例

默認情況下，load data infile語句是從數(shù)據(jù)庫服務器加載數(shù)據(jù)的，為了安全起見，一般MySQL都會配置secure_file_priv參數(shù)，來指定可以讀寫文件的目錄，將要導入的文件放在此參數(shù)指定的目錄下。

show variables like 'secure_file_priv';

我們先通過導出數(shù)據(jù)的方式創(chuàng)建一個文件，這里在示例數(shù)據(jù)庫employees下新建一張測試表并插入幾條數(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ù)導出到文件（路徑就是secure_file_priv參數(shù)指定的目錄），這里使用默認格式導出：

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

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

2.1 基本用法

由于load data infile和select into outfile語句是互補的，所以它們的格式設(shè)定語法是一樣的。select…into outfile采用默認格式導出的文件就是load data infile的默認導入格式。這種情況下，直接指定文件名及要導入表名即可（這里先清空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ù)的來源不是MySQL導出的文件，格式也不同。例如常用的CSV格式文件，我們手動將剛才文件改為CSV格式（以逗號分隔數(shù)據(jù)），且第一行數(shù)據(jù)中remark字段還額外包含了一個逗號（紅框處）：

碰到這種和默認格式不同的數(shù)據(jù)，MySQL就無法解析了，如果直接導入就會報錯：

此時需要通過格式子句來告訴MySQL如何解析數(shù)據(jù)，默認的格式子句如下：

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

含義解釋：

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

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

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

三、常見導入問題的處理

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

3.1 標題行的處理

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

導入時，通過ignore 1 lines/rows語句，忽略第一行：

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;

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

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

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

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

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

在into table語句前增加一個ignore關(guān)鍵字，這樣出現(xiàn)鍵值沖突時會忽略而不是報錯：

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

可以看到Vincent的salary并沒有更新，同時日志提示忽略了3行。

在into table語句前增加一個replace關(guān)鍵字，這樣出現(xiàn)鍵值沖突時會更新數(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說明實際操作是將原數(shù)據(jù)刪除再插入數(shù)據(jù)。如果文件每次需要更新導入，那么replace關(guān)鍵字就很適合。

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

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

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

alter table person add extra_column int after name;

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

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);    -- 指定導入的列名順序

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

3.4 導入部分列

如果只想將文件中的部分列導入數(shù)據(jù)庫，即丟棄部分列的數(shù)據(jù)。我們也可以通過指定列名的方式來實現(xiàn)，通過僅指定需要導入數(shù)據(jù)的列名，而想丟棄的數(shù)據(jù)用一個變量名來占位，這樣對應的列數(shù)據(jù)就不會被導入到數(shù)據(jù)庫中。

例如導入數(shù)據(jù)時，僅想導入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來占位，而不是指定remark列名，因此remark列沒有數(shù)據(jù)導入，相當于僅導入了部分列。

3.5 導入過程中處理數(shù)據(jù)

除了將數(shù)據(jù)原封不動導入之外，load data infile語句還支持一個set子句讓你在導入過程中對數(shù)據(jù)進行加工處理。

例如記錄導入時間，我們再增加一個列import_time，用來記錄數(shù)據(jù)導入時間：

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;

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

對于想要加工的列，我們可以先將列賦給變量，然后對變量加工后，再通過set子句寫入表的列，達到先加工后導入的效果。例如對于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;

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

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

最新評論