【學習背景】

在日常作業和學習MySQL時，經常涉及到MySQL資料的匯入和匯出，分享幾種常用又方便的方式：
（1）MySQL命令列source命令
（3）語法into outfile和load data infile
（3）MySQL目錄bin下的mysqldump工具

本文將會介紹以及測驗這幾種MySQL匯入匯出資料的方式及使用注意事項，引數可能會比較多，大家可以學習最常用的就好，這里分享出來，希望能幫助到有需要的小伙伴~

進入正文~

測驗資料

本文以Windows下操作為例，Linux也是一樣的方法，區別在于路徑語法不同而已~
創建一個MySQL資料庫test和資料表demo_info，方便進行測驗~

create database if not exists test default character set utf8 collate utf8_general_ci;
use test;
-- 創建測驗表
create table test.demo_info(
	id int(7) primary key not null auto_increment,
	name varchar(255) not null,
	sex char(1) not null,
	age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

alter table test.demo_info comment '測驗表';
alter table test.demo_info modify column id int(7) not null auto_increment comment 'ID';
alter table test.demo_info modify column name varchar(255) not null comment '姓名';
alter table test.demo_info modify column sex char(1) not null comment '性別:1-男,0-女';
alter table test.demo_info modify column age int(3) comment '年齡';

一、命令source實作

2.1 匯入資料

（1）準備insert.sql內容如下：

use test;
insert into test.demo_info(name,sex,age) values('張一','1',21);
insert into test.demo_info(name,sex,age) values('張二','0',22);
insert into test.demo_info(name,sex,age) values('張三','1',23);

存放路徑：C:/Users/Administrator/Desktop/insert.sql

（2）先登錄到MySQL命令列
打開cmd命令視窗，登錄到MySQL命令列：
$ cd C:\Program Files\MySQL\MySQL Server 5.7\bin
$ mysql -hlocalhost -uroot -p --default-character-set=utf8
輸入密碼：
mysql >

（3）執行source命令匯入資料：
mysql> use test;
mysql> show tables;
mysql> select * from demo_info;
mysql> source C:/Users/Administrator/Desktop/insert.sql;

注意如果你資料庫沒有設定字符集為utf8，并且在連接時也沒有指定--default-character-set=utf8連接，那么會導致插入中文資料時亂碼，提示如下：

亂碼原因是，默認客戶端連接編碼為GBK
mysql> use test;
mysql> show variables like '%character%';

中文亂碼情況的解決方案，如果不想在連接時指定字符集為utf8，可以修改mysql的配置my.ini（my.cnf）指定字符集為utf8，重啟mysql服務生效~

[client]
default-character-set=utf8
[mysql]
character-set-server=utf8
[mysqld]
default-character-set=utf8

2.2 匯出資料

命令source匯出資料主要是通過執行匯出資料的SQL陳述句，本質還是使用into outfile語法來實作，這里先簡單直接使用下~

（1）準備select.sql內容如下：

use test;
select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt';

select.sql存放路徑：C:/Users/Administrator/Desktop/select.sql

（2）執行source命令匯出資料：
mysql> source C:/Users/Administrator/Desktop/select.sql;

不過，別高興太早，一般都會報錯的，提示如下：
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

原因是--secure-file-priv安全路徑問題，具體往下進入到into outfile章節了解~

二、 into oufile和load data infile實作

2.1 into outfile

2.1.1 簡單匯出資料

匯出資料通過into outfile語法實作，匯入資料通過load data infile語法實作~

（1）前提條件說明
授權用戶file權限：
mysql > select * from mysql.user where user='root' \G;
mysql > update mysql.user set File_priv='Y' where user='root';
mysql > select * from mysql.user where user='root' \G;
mysql > flush privileges;

如果沒有授予用戶的File_priv權限為Y，into outfile匯出檔案時會報錯：
ERROR 1 (HY000): Can’t create/write to file ‘C:\Users\Administrator\Desktop\demo_info.txt’ (Errcode: 13 - Permission denied)

配置安全路徑：

MySQL使用into outfile語法匯出資料時，只能匯出資料檔案到secure-file-priv指定的安全路徑下~
查看安全路徑命令mysql> show variables like '%secure%';

可以看到引數secure_file_priv對應的路徑即為MySQL安全路徑：
但是Windows下路徑問題，有一個小坑，容易誤匯入，就是這里show顯示的路徑是單反斜杠\，但實際用的時候要么變成雙反斜杠\\，要么改成單斜杠/，才能使用into outfile語法正常匯出，否則會報錯~

如果指定匯出檔案路徑不是安全路徑下的，則會報錯：
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

簡單匯出測驗下（非安全路徑，如桌面）：

select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt';

報錯提示如下：

簡單匯出測驗下（安全路徑）

select * from test.demo_info into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/demo_info.txt';

正常匯出demo_info.txt資料檔案（注意Windows下路徑不要用單反斜杠\）

（2）配置安全路徑
如果不想用默認安全路徑，可以修改引數--secure-file-priv為自定義路徑，修改MySQL組態檔，一般默認的組態檔路徑為：
Windows：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
Linux：/etc/my.cnf

安全路徑在[mysqld]組下找到引數secure_file_priv進行配置即可~

這里我修改為空字串""：

secure-file-priv=""

空字串""表示不限制匯出路徑，不過需要是mysql用戶有讀寫權限的目錄，例如Linux下，你不能直接匯出到/root/目錄下，肯定是沒權限創建資料檔案的~~

（3）匯出資料
簡單匯出測驗：

select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt';

發現匯出到桌面居然不成功，其他MySQL安裝目錄和D盤都可以，C盤下都不行~

解決方案是按快捷鍵：Win 快速搜索：服務關鍵字，找到mysql服務，右鍵查看屬性~

切換賬戶為本地系統賬戶并勾選允許服務與桌面互動~

應用并重啟mysql服務生效~

重新簡單匯出測驗，匯出到桌面成功：

select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.txt';

2.1.2 帶格式匯出資料

通過前面簡單匯出資料得到資料檔案demo_info.txt，可以看到匯出的資料占用的空間比較大

7	張一	1	21
8	張二	0	22
9	張三	1	23

如果欄位的資料比較長，資料量比較大，會很浪費空間，因此需要對into outfile匯出的資料檔案進行格式化：
（1）MySQL命令列>

select * from demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.del' character set utf8 fields terminated by 0x0f;

匯出的資料空間完全緊密，不浪費任何空間，實際使用這種方式的非常多：
（2）終端命令列：

mysql -hlocalhost -uroot -p test -e "select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info.del' character set utf8 fields terminated by 0x0f"

into outfile引數說明：

使用enclosed by引數示例：

select * from demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info2.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"';

使用escaped by引數示例：
例如，把張三的名字后面加個特殊符號換行符\n

update test.demo_info set name='張一\n' where id=7;

再執行匯出命令：

select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info3.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"';

使用lines引數示例：

update test.demo_info set name='張一' where id=7;

select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info4.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';

觀察每條記錄的首尾資料格式：

2.1.3 匯出注意事項

（1）存在問題：
Linux環境下，由于使用MySQL語法into outfile匯出的資料檔案時，資料檔案只能保存在MySQL資料庫服務端，那么會導致在集群模式下，當應用和資料庫分別部署在兩臺不同的服務器時，會存在應用無法讀取到資料檔案的問題~

MySQL服務器M：/batchfile/mysql/data/test/demo_info.del;
應用服務器A： 批量程式，可能會通過shell腳本想要加載demo_info.del資料檔案~
應用服務器B： 批量程式，可能會通過shell腳本想要加載demo_info.del資料檔案~

（2）解決方案：
可以通過mount掛在指定目錄/batchfile/為共享盤目錄，實作服務器A、B、M都能擁有該目錄下的資料檔案的讀寫訪問權限~

具體mount命令的使用方式，可以查詢百度學習下~

2.2 load data infile

2.2.1 簡單匯入資料

（1）資料檔案
前面通過into outfile簡單匯出得到demo_info.txt：

7	張一	1	21
8	張二	0	22
9	張三	1	23

（2）匯入資料

load data infile 'C:/Users/Administrator/Desktop/demo_info.txt' into table demo_info character set utf8;

2.2.2 帶格式匯入資料

匯入del資料檔案（加載服務端檔案）：
命令列mysql>

load data infile 'C:/Users/Administrator/Desktop/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f;

load data infile引數說明：

其實只需要跟into outfile匯出引數一樣，匯出時有的引數，load data infile匯入時該有的引數也要有，才能保證匯入資料時對得上~

比如into outfile匯出最復雜的情況如下（分隔符為0x0f、非數值雙引號"擴起、特殊轉義符使用雙引號"轉義、每條記錄開頭是start及結尾是end\n）得到資料檔案demo_info_complex_data.del

update test.demo_info set name='張一\n' where id=7;

select * from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo_info_complex_data.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';

可以看到demo_info_complex_data.del內容如下：

那么要匯入demo_info_complex_data.del對應的load data infile語法完整SQL陳述句為：

load data infile 'C:/Users/Administrator/Desktop/demo_info_complex_data.del' into table demo_info character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';

其實很簡單，把into outfile匯出資料時character后面的引數直接copy過來就行~

Linux終端命令：

mkdir -p /batchfile/mysql/data/test/
mysql -hlocalhost -uroot -p test -e "load data infile '/batchfile/mysql/data/test/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f"

匯入del資料檔案（加載客戶端本地LOCAL檔案）：
命令列mysql>

load data LOCAL infile 'C:/Users/Administrator/Desktop/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f;

Linux終端命令：

mysql -hlocalhost -uroot -p test -e "load data LOCAL infile '/batchfile/mysql/data/test/demo_info.del' into table demo_info character set utf8 fields terminated by 0x0f"

注意：如果MySQL服務端在Linux，load data infile默認是加載服務端路徑的資料檔案，指定LOCAL表示加載的是客戶端的本地資料檔案~

三、工具mysqldump實作

MySQL 自帶mysqldump 工具，工具檔案在bin目錄下，不僅可以匯出和匯入表資料，還可以選擇性的匯出庫表（整庫、多庫、單庫、多表、單表）結構，是資料庫備份的方途徑之一~
同樣本文以Windows下為例，Linux區別在于路徑不同~
操作本地：mysqldump -u資料庫用戶 -p xxx
操作遠程：mysqldump -hIP地址 -P埠號 -p xxx

3.1 匯出

3.1.1 資料庫

打開cmd命令窗，進入到bin目錄下：

cd C:\Program Files\MySQL\MySQL Server 5.7\bin

（1）匯出所有資料庫（結構+資料）

mysqldump -uroot -p --all-databases > C:/Users/Administrator/Desktop/all_databases.sql

（2）匯出指定資料庫（結構+資料）

mysqldump -uroot -p --databases test > test.sql

也可以指定多個資料庫（結構+資料）

mysqldump -u root -p --databases test test2 > test_test2.sql

3.1.2 資料表

（1）匯出指定資料表（結構+資料）

mysqldump -u root -p --set-gtid-purged=OFF test demo_info > demo_info.sql

注意：這里設定--set-gtid-purged引數設定為OFF表示mysqldump備份時會記錄MySQL的binlog日志，如果不加，則不會記錄binlog日志，binlog日志這里不再做具體介紹，簡單說明就是MySQL資料庫備份、主從復制的核心日志檔案~
那要不要記錄binlog日志，取決于你的MySQL設定主從復制的時候用到了gtid：
mysql> show variables like '%gtid%';
引數gtid_mode為ON表明用到gtid了，當然我這里是單庫沒有主從因此為OFF~

所以如果是MySQL主從資料庫，并且在主庫使用mysqldump備份時，需要加--set-gtid-purged=OFF，以便主庫記錄binlog日志，否則主庫沒有了binlog日志，當你想在主庫恢復備份的資料時，資料并不會被同步到從庫~

（2）匯出指定資料表（僅結構）

mysqldump -u root -p --set-gtid-purged=OFF -d test demo_info > demo_info.sql

–引數說明

（2）匯出指定資料表（僅資料）

mysqldump -u root -p --set-gtid-purged=OFF -t test demo_info > demo_info.sql

等價于：

mysqldump -u root -p --set-gtid-purged=OFF --no-create-info test demo_info > demo_info.sql

–引數說明

（3）匯出指定資料表（僅資料 + where條件）

mysqldump -u root -p --set-gtid-purged=OFF --no-create-info test demo_info --where "name = '張三'" > demo_info.sql

3.2 匯入資料

使用工具mysqldump本質是得到SQL陳述句檔案，本文主要目的是介紹匯入和匯出表資料~

（1）匯出表資料
通過前面匯出的介紹，可以使用以下命令僅匯出demo_info表的資料即可~
終端命令$ ：

mysqldump -hlocalhost -P3306 -uroot -p --set-gtid-purged=OFF --no-create-info test demo_info > C:/Users/Administrator/Desktop/demo_info.sql

得到demo_info資料表的SQL資料檔案demo_info.sql~

（2）匯入表資料
先清空表，再匯入資料~
終端命令$ ：

mysqldump -hlocalhost -P3306 -uroot -p < C:/Users/Administrator/Desktop/demo_info.sql;

也可以通過最開始介紹的source命令列來執行SQL陳述句檔案實作匯入~
命令列mysql> source C:/Users/Administrator/Desktop/demo_info.sql;
或
通過終端命令$：

mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"select * from test.demo_info;"
mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"delete from test.demo_info;"

mysql -hlocalhost -P3306 -uroot -pabc@123456 test -e"source C:/Users/Administrator/Desktop/demo_info.sql;"

匯入資料成功！！！

文章完結~~

原創不易，覺得有用的小伙伴來個一鍵三連（點贊+收藏+評論 ）+關注支持一下，非常感謝~