???MySQL--【全網最細】高效導入導出數據，學會下早班???（工作必備 & 建議收藏）

【學習背景】

在日常工作和學習Mysql時，經常涉及到Mysql數據的導入和導出，分享幾種常用又方便的方式：

（1）MySQL命令行source命令

（3）語法into outfile和load data infile

（3）MySQL目錄bin下的mysqldump工具

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

進入正文~

@TOC

測試數據

本文以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;"

導入數據成功！！！

文章完結~~

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

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。