大家好，我是景天，本文主要介紹了MySQL 導(dǎo)出和導(dǎo)入數(shù)據(jù)的幾種實(shí)現(xiàn)方式,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

一、導(dǎo)出數(shù)據(jù)庫(kù)

MySQL導(dǎo)出數(shù)據(jù)的主要作用是將數(shù)據(jù)庫(kù)中的數(shù)據(jù)以某種格式（如SQL、CSV等）導(dǎo)出到一個(gè)文件中，以便在需要時(shí)進(jìn)行導(dǎo)入或備份。

導(dǎo)出數(shù)據(jù)的作用包括但不限于以下幾個(gè)方面：

數(shù)據(jù)備份：導(dǎo)出數(shù)據(jù)可以將數(shù)據(jù)庫(kù)中的數(shù)據(jù)備份到文件中，以防止數(shù)據(jù)丟失或意外刪除。
數(shù)據(jù)遷移：如果需要將數(shù)據(jù)庫(kù)從一個(gè)服務(wù)器遷移到另一個(gè)服務(wù)器，可以將數(shù)據(jù)導(dǎo)出到文件中，然后在目標(biāo)服務(wù)器上導(dǎo)入數(shù)據(jù)。
數(shù)據(jù)共享：如果需要與其他人共享數(shù)據(jù)庫(kù)中的數(shù)據(jù)，可以將數(shù)據(jù)導(dǎo)出到CSV等通用格式的文件中，然后將其發(fā)送給其他人。
數(shù)據(jù)分析：如果需要對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行分析或處理，可以將其導(dǎo)出到文件中，然后使用其他工具（如Excel、Python等）進(jìn)行處理。

（一）使用SELECT … INTO OUTFILE語(yǔ)句將某個(gè)表中的數(shù)據(jù)導(dǎo)出

SELECT … INTO OUTFILE語(yǔ)句是MySQL中用于將查詢結(jié)果導(dǎo)出為CSV文件的方法。它允許您將查詢結(jié)果保存到指定的文件路徑中，以便進(jìn)行后續(xù)處理或備份。
使用案例：

我們有一個(gè)名為"students"的表格，其中包含班級(jí)、男生人數(shù)、女生人數(shù)。要導(dǎo)出這些數(shù)據(jù)到一個(gè)CSV文件中，可以使用以下語(yǔ)句：

select * into outfile '/data/file.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
from students;

這將導(dǎo)出"students"表中的所有數(shù)據(jù)，并將其保存到指定路徑的CSV文件中。文件中的每行代表一個(gè)用戶，每個(gè)字段用逗號(hào)分隔，并用雙引號(hào)包圍。

參數(shù)說(shuō)明：

INTO OUTFILE ‘file_path’: 指定要導(dǎo)出數(shù)據(jù)的文件路徑?？梢允潜镜芈窂交騑eb服務(wù)器上的路徑。 FIELDS
TERMINATED BY ‘delimiter’: 指定字段之間的分隔符。默認(rèn)情況下使用制表符（\t）作為分隔符。 ENCLOSED BY
‘enclosure’: 指定字段內(nèi)容的引號(hào)包圍符號(hào)。默認(rèn)情況下不使用引號(hào)包圍。 LINES TERMINATED BY
‘line_break’: 指定每行數(shù)據(jù)之間的分隔符。默認(rèn)情況下使用換行符（\n）作為行分隔符。 FROM table_name:
指定要導(dǎo)出數(shù)據(jù)的表格名稱。

注意事項(xiàng)：

使用SELECT … INTO OUTFILE語(yǔ)句導(dǎo)出數(shù)據(jù)需要具有相應(yīng)的權(quán)限。確保您具有足夠的權(quán)限來(lái)執(zhí)行此操作。
導(dǎo)出的文件路徑必須是MySQL服務(wù)器可以訪問(wèn)的路徑。如果路徑無(wú)法訪問(wèn)，您將無(wú)法導(dǎo)出數(shù)據(jù)。
如果指定的文件已經(jīng)存在，將會(huì)覆蓋該文件。請(qǐng)確保在導(dǎo)出數(shù)據(jù)之前備份重要的文件。

執(zhí)行這句可能會(huì)報(bào)錯(cuò)如下
mysql> select * into outfile ‘/data/file.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ from students;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

【1】分析原因

其實(shí)原因很簡(jiǎn)單，因?yàn)樵诎惭bMySQL的時(shí)候限制了導(dǎo)入與導(dǎo)出的目錄權(quán)限。只允許在規(guī)定的目錄下才能導(dǎo)入。

可以通過(guò)以下命令查看secure-file-priv當(dāng)前的值是什么

SHOW VARIABLES LIKE "secure_file_priv";

mysql8.0默認(rèn)目錄是/var/lib/mysql-files/

可以看到，本地value的值為/var/lib/mysql-files/。經(jīng)查資料，value的值有可能有如下幾種：
（1）NULL，表示禁止。

（2）如果value值有文件夾目錄，則表示只允許該目錄下文件（PS：測(cè)試子目錄也不行）。

（3）如果為空，則表示不限制目錄。

【2】解決方案

去掉導(dǎo)入的目錄限制。可修改mysql配置文件（Windows下為my.ini, Linux下的/etc/my.conf），在[mysqld]下面，查看是否有：

secure_file_priv =

如上這樣一行內(nèi)容，如果沒(méi)有，則手動(dòng)添加。如果存在如下行：

secure_file_priv = /home 

這樣一行內(nèi)容，表示限制為/home文件夾。而如下行：

secure_file_priv =

這樣一行內(nèi)容，表示不限制目錄，等號(hào)一定要有，否則mysql無(wú)法啟動(dòng)。
修改完配置文件后，重啟mysql生效。

重啟數(shù)據(jù)庫(kù)

systemctl restart mysqld.service

默認(rèn)只能指定 /tmp目錄 。否則回報(bào) “ ERROR 1 (HY000) at line 1: Can’t create/write to file ‘/xxxx/xxxx/xxx.csv’ (Errcode: 13)” 錯(cuò)誤 ！導(dǎo)致原因是因?yàn)闄?quán)限問(wèn)題，修改起來(lái)比較麻煩 ！可以放到/tmp/目錄下，然后cp到其它你想存放的目錄?！就扑]使用這種方法】
導(dǎo)出到/data/目錄報(bào)權(quán)限不足

導(dǎo)出到tmp/目錄成功

下載到本地打開(kāi)

（二）使用mysqldump工具導(dǎo)出數(shù)據(jù)

mysqldump是MySQL數(shù)據(jù)庫(kù)的一個(gè)命令行工具，用于備份和導(dǎo)出數(shù)據(jù)庫(kù)、表格和數(shù)據(jù)。以下是使用mysqldump工具導(dǎo)出數(shù)據(jù)的基本語(yǔ)法：

mysqldump -u username -p dbname table_name > /path/to/output/file.sql

參數(shù)說(shuō)明

-u: 指定MySQL的用戶名。
-p: 表示需要輸入密碼。 dbname: 指定要導(dǎo)出數(shù)據(jù)的數(shù)據(jù)庫(kù)名稱。 table_name: 指定要導(dǎo)出數(shù)據(jù)的表格名稱。如果要導(dǎo)出整個(gè)數(shù)據(jù)庫(kù)，可以省略該參數(shù)。 /path/to/output/file.sql:
指定導(dǎo)出數(shù)據(jù)的輸出文件路徑。

導(dǎo)出整個(gè)數(shù)據(jù)庫(kù)可以使用如下命令：
mysqldump –u用戶名 p密碼 -d 數(shù)據(jù)庫(kù)名 表名 > 腳本名

導(dǎo)出單個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)和數(shù)據(jù)：

mysqldump -uroot -pJxxxxxx357@  --databases  db005 >dump2.sql

導(dǎo)出單個(gè)數(shù)據(jù)庫(kù)中單個(gè)表結(jié)構(gòu)和數(shù)據(jù)： 數(shù)據(jù)庫(kù)名 表名

mysqldump -uroot -pJixxxxxxx7@   db005  t1 >dump3.sql

導(dǎo)出整個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)（不包含數(shù)據(jù)）：

mysqldump -h localhost -uroot -p123456 -d database > dump.sql

只包含數(shù)據(jù)庫(kù)和表結(jié)構(gòu)，不包含數(shù)據(jù)

mysqldump -uroot -pJinghao31357@ -d db001 >dump.sql

導(dǎo)出單個(gè)數(shù)據(jù)表結(jié)構(gòu)（不包含數(shù)據(jù)）：

mysqldump -h localhost -uroot -p123456 -d database table > dump.sql

導(dǎo)出全部數(shù)據(jù)庫(kù)數(shù)據(jù)：

mysqldump -uroot -p --all-databases > alldb.sql

（三）導(dǎo)出步驟

1.原服務(wù)器操作

1.指定到處對(duì)應(yīng)的數(shù)據(jù)庫(kù)文件

mysqldump -uroot -pJinghao31357@ --all-databases --single-transaction > `date +%Y%m%d%H`-mysal-all.sql

–single-transaction ：讓整個(gè)數(shù)據(jù)在dump過(guò)程中保證數(shù)據(jù)的一致性，這個(gè)選項(xiàng)對(duì)InnoDB的數(shù)據(jù)表很有用，且不會(huì)鎖表。
但是這個(gè)不能保證MyISAM表和MEMORY表的數(shù)據(jù)一致性。

備份指定數(shù)據(jù)庫(kù)指定表(多個(gè)表以空格間隔)

mysqldump -uroot -p  mysql db event > /backup/mysqldump/2table.sql

導(dǎo)出特定數(shù)據(jù)庫(kù)的所有表格的表結(jié)構(gòu)及其數(shù)據(jù)，添加“–databases ”命令參數(shù)

mysqldump  -u  b_user -h 101.3.20.33 -p'H_password' -P3306 --databases test  > all_database.sql

-A = --all-databases 全庫(kù)備份-B =  --databases 部分庫(kù)備份

2.將備份的sql語(yǔ)句scp到指定服務(wù)器

scp 2020081222-maysl-all.sql root@10.0.0.51:/root/

2.新服務(wù)器導(dǎo)入原來(lái)的數(shù)據(jù)庫(kù)

這種方式不用登錄數(shù)據(jù)庫(kù)
利用備份的sql語(yǔ)句導(dǎo)入，復(fù)原舊服務(wù)器數(shù)據(jù)

mysql -uroot -pJinghao31357@ < 2020081222-maysl-all.sql

二、導(dǎo)入數(shù)據(jù)

（一）load data參數(shù)詳解

在MySQL中，您可以使用LOAD DATA語(yǔ)句將外部數(shù)據(jù)文件導(dǎo)入到數(shù)據(jù)庫(kù)中。LOAD DATA語(yǔ)句允許您以高效的方式將大量數(shù)據(jù)快速加載到數(shù)據(jù)庫(kù)表中。

以下是使用LOAD DATA導(dǎo)入數(shù)據(jù)的基本語(yǔ)法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE table_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(column_name_or_user_var,...)]
    [SET column_name = expr,...]

逐個(gè)解釋這些選項(xiàng)的含義：

LOW_PRIORITY：該選項(xiàng)可用于降低LOAD
DATA語(yǔ)句的優(yōu)先級(jí)，以便它不會(huì)與其他查詢競(jìng)爭(zhēng)資源。這在需要同時(shí)運(yùn)行其他重要查詢的系統(tǒng)上是有用的。
CONCURRENT：該選項(xiàng)允許在導(dǎo)入數(shù)據(jù)的同時(shí)進(jìn)行其他操作，而不會(huì)相互干擾。
LOCAL：該選項(xiàng)指示從客戶端系統(tǒng)上的文件加載數(shù)據(jù)，而不是從服務(wù)器上的文件加載。 INFILE
‘file_name’：指定要導(dǎo)入的數(shù)據(jù)文件的路徑和名稱。 REPLACE：如果存在重復(fù)的記錄，該選項(xiàng)將刪除現(xiàn)有記錄并用新記錄替換它們。
IGNORE：如果存在重復(fù)的記錄，該選項(xiàng)將忽略新記錄，保留現(xiàn)有記錄。 INTO TABLE
table_name：指定要將數(shù)據(jù)導(dǎo)入的目標(biāo)表的名稱。 CHARACTER SET charset_name：指定用于解析文件的字符集。
FIELDS TERMINATED BY ‘string’：指定字段之間的分隔符。 ENCLOSED BY
‘char’：指定字段內(nèi)容的引號(hào)包圍符號(hào)。 ESCAPED BY ‘char’：指定用于轉(zhuǎn)義特殊字符的轉(zhuǎn)義符號(hào)。 LINES
TERMINATED BY ‘string’：指定每行數(shù)據(jù)之間的分隔符。 IGNORE number {LINES |
ROWS}：指定要忽略的行數(shù)或行數(shù)范圍。 (column_name_or_user_var,…)：指定要導(dǎo)入的列名或用戶變量。 SET
column_name = expr,…：設(shè)置要導(dǎo)入的列的值。

導(dǎo)入案例：
我們把剛才導(dǎo)出的file.csv改一下

（二）執(zhí)行導(dǎo)入

load data infile '/tmp/file.csv' into table students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

查看數(shù)據(jù)庫(kù)，導(dǎo)入成功

到此這篇關(guān)于mysql導(dǎo)入導(dǎo)出數(shù)據(jù)的示例詳解的文章就介紹到這了,更多相關(guān)mysql導(dǎo)入導(dǎo)出數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論