第七章導(dǎo)入和導(dǎo)出

第七章導(dǎo)入和導(dǎo)出第一頁，共四十二頁，編輯于2023年，星期四本章重點掌握數(shù)據(jù)導(dǎo)入和導(dǎo)出的方法不同方法之間的區(qū)別第二頁，共四十二頁，編輯于2023年，星期四數(shù)據(jù)導(dǎo)出方法方法一：使用selectintooutfile'filename'語句方法二：使用mysqldump實用程序第三頁，共四十二頁，編輯于2023年，星期四

兩種方法的區(qū)別：使用select方法輸出的文件只有數(shù)據(jù)，沒有表結(jié)構(gòu)，而且一次只能處理一個表，要處理多個表則不是很容易，不過可以將select命令寫入一個sql文件，然后在命令行下執(zhí)行即可在(mysqldump命令的講解中可以看到如何執(zhí)行這個sql文件)；mysqldump可以導(dǎo)出純數(shù)據(jù)文本或只導(dǎo)出含有創(chuàng)建表結(jié)構(gòu)而沒有數(shù)據(jù)的sql文件，同時可以對庫中的多個表進(jìn)行操作，比select的方法靈活，強(qiáng)大的多。

select適合利用程序進(jìn)行處理，而mysqldump則為手工操作，同時提供強(qiáng)大的導(dǎo)出功能，并且可以處理整個庫，或庫中指定的多表。4第四頁，共四十二頁，編輯于2023年，星期四1使用selectintooutfile語句只能導(dǎo)出表的數(shù)據(jù)，不能導(dǎo)出表結(jié)構(gòu)語法SELECT*FROMTABLE_NAMEINTOOUTFILE‘filename’要求：用戶擁有file的權(quán)限

5第五頁，共四十二頁，編輯于2023年，星期四使用樣例：

mysql>usecookbook;

DatabaseChanged

mysql>select*fromstu_infointooutfile‘c:/a.txt’;

QueryOK,12rowsaffected(0.05sec)

這樣就把stu_info表的數(shù)據(jù)導(dǎo)在了‘a(chǎn).txt’文件里面

6第六頁，共四十二頁，編輯于2023年，星期四指定分隔符使用select命令還可以指定導(dǎo)出文件時，字段之間的分隔字符，轉(zhuǎn)義字符，包括字符，及記錄行分隔字符。

FIELDSTERMINATEDBY字段分割字符，默認(rèn)為’\t’

[OPTIONALLY]ENCLOSEDBY字段用什么字符包括，默認(rèn)為無,如果使用了OPTIONALLY，則只有CHAR和VERCHAR被包括；

ESCAPEDBY轉(zhuǎn)義字符，默認(rèn)為’\\’

LINESTERMINATEDBY行分隔符，默認(rèn)無7第七頁，共四十二頁，編輯于2023年，星期四實例mysql>select*fromstu_infointooutfile‘c:/a1.txt’fieldsterminatedby‘,’enclosedby‘“’;

QueryOK,12rowsaffected(0.06sec)

結(jié)果可能如下：

"200801001","楊振軍","1",\N,"男""200801002","白云","1",\N,"女""200801003","吳楠","2",\N,"女"看到每個字段都用‘,’進(jìn)行了分隔，且每個字段都用‘”’包括了起來。注意，行記錄分隔符可以是一個字符串，請大家自行測試。不過，如果輸出文件在指定目錄下如果存在的話就會報錯，先刪除再測試即可。8第八頁，共四十二頁，編輯于2023年，星期四2、使用mysqldump實用程序先舉個簡單的例子

C:\Users\zp>mysqldump-uroot-pcookbook>d:\a.sql

Enterpassword:

可能結(jié)果如下：

--MySQLdump10.13Distrib5.5.9,forWin32(x86)----Host:localhostDatabase:cookbook----------------------------------------------------------Serverversion 5.5.9/*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;/*!40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;/*!40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;/*!40101SETNAMESutf8*/;/*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/;/*!40103SETTIME_ZONE='+00:00'*/;9第九頁，共四十二頁，編輯于2023年，星期四如何解決亂碼showvariableslike'%char%';character-set-databasegb2312在導(dǎo)出語句中在cookbook之前增加—default-character-set=gb2312第十頁，共四十二頁，編輯于2023年，星期四字符校對服務(wù)器字符集和校對MySQL服務(wù)器有一個服務(wù)器字符集和一個服務(wù)器校對規(guī)則，它們均不能設(shè)置為空MySQL按照如下方法確定服務(wù)器字符集和服務(wù)器校對規(guī)則：當(dāng)服務(wù)器啟動時根據(jù)有效的選項設(shè)置根據(jù)運(yùn)行時的設(shè)定值在服務(wù)器級別，確定方法很簡單。當(dāng)啟動mysqld時，根據(jù)使用的初始選項設(shè)置來確定服務(wù)器字符集和校對規(guī)則?？梢允褂?-default-character-set設(shè)置字符集，并且可以在字符集后面為校對規(guī)則添加--default-collation。如果沒有指定一個字符集，那就與--default-character-set=latin1相同。如果你僅指定了一個字符集（例如，latin1），但是沒有指定一個校對規(guī)則，那就與--default-charset=latin1--default-collation=latin1_swedish_ci相同，因為latin1_swedish_ci是latin1的默認(rèn)校對規(guī)則。因此，以下三個命令有相同的效果：shell>mysqldshell>mysqld--default-character-set=latin1shell>mysqld--default-character-set=latin1\--default-collation=latin1_swedish_ci第十一頁，共四十二頁，編輯于2023年，星期四數(shù)據(jù)庫字符集和校對每一個數(shù)據(jù)庫有一個數(shù)據(jù)庫字符集和一個數(shù)據(jù)庫校對規(guī)則，它不能夠為空。CREATEDATABASE和ALTERDATABASE語句有一個可選的子句來指定數(shù)據(jù)庫字符集和校對規(guī)則：第十二頁，共四十二頁，編輯于2023年，星期四表字符集和校對每一個表有一個表字符集和一個校對規(guī)則，它不為空。為指定表字符集和校對規(guī)則，CREATETABLE和ALTERTABLE語句有一個可選的子句：CREATETABLEtbl_name(column_list)[DEFAULTCHARACTERSETcharset_name[COLLATEcollation_name]第十三頁，共四十二頁，編輯于2023年，星期四列字符集和校對每一個“字符”列（即，CHAR、VARCHAR或TEXT類型的列）有一個列字符集和一個列校對規(guī)則，它不能為空。列定義語法有一個可選子句來指定列字符集和校對規(guī)則：col_name{CHAR|VARCHAR|TEXT}(col_length)[CHARACTERSETcharset_name[COLLATEcollation_name]]例如：CREATETABLETable1(column1VARCHAR(5)CHARACTERSETlatin1COLLATElatin1_german1_ci);MySQL按照下面的方式選擇列字符集和校對規(guī)則：如果指定了CHARACTERSETX和COLLATEY，那么采用CHARACTERSETX和COLLATEY。如果指定了CHARACTERSETX而沒有指定COLLATEY，那么采用CHARACTERSETX和CHARACTERSETX的默認(rèn)校對規(guī)則。否則，采用表字符集和服務(wù)器校對規(guī)則。CHARACTERSET和COLLATE子句是標(biāo)準(zhǔn)的SQL。第十四頁，共四十二頁，編輯于2023年，星期四不同的編碼格式會導(dǎo)致同一字符，在不同字符集下的編碼會不同。同樣同一編碼在不同的字符集中代碼的字符也不相同。當(dāng)你的MySQL返回的字符串的編碼格式（字符集）與你的客戶工具程序（mysql,php,querybrowser,...）當(dāng)前使用的字符集不同時，就會造成亂碼。比如一個英國朋友告訴你Long,當(dāng)一位中國小學(xué)生看到后就會告訴你“龍”而不是“長”第十五頁，共四十二頁，編輯于2023年，星期四MySQL中默認(rèn)字符集的設(shè)置有四級:服務(wù)器級，數(shù)據(jù)庫級，表級。最終是字段級的字符集設(shè)置。注意前三種均為默認(rèn)設(shè)置，并不代碼你的字段最終會使用這個字符集設(shè)置。所以我們建議要用showcreatetabletable;或showfullfieldsfromtableName;來檢查當(dāng)前表中字段的字符集設(shè)置。MySQL中關(guān)于連接環(huán)境的字符集設(shè)置有Client端，connection,results通過這些參數(shù)，MySQL就知道你的客戶端工具用的是什么字符集，結(jié)果集應(yīng)該是什么字符集。這樣MySQL就會做必要的翻譯，一旦這些參數(shù)有誤，自然會導(dǎo)致字符串在轉(zhuǎn)輸過程中的轉(zhuǎn)換錯誤。基本上99%的亂碼由些造成。第十六頁，共四十二頁，編輯于2023年，星期四亂碼后需要檢查的信息1數(shù)據(jù)庫表中字段的字符集設(shè)置。showcreatetableTableName或showfullcolumnsfromtableNamemysql>showcreatetablestu_info;CREATETABLE`stu_info`(`id`varchar(20)NOTNULLDEFAULT'',`name`varchar(20)DEFAULTNULL,`class`varchar(10)DEFAULTNULL,`birth_place`int(11)DEFAULTNULL,`sex`char(2)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=gb2312第十七頁，共四十二頁，編輯于2023年，星期四showfullcolumnsfromstu_info;第十八頁，共四十二頁，編輯于2023年，星期四2當(dāng)前聯(lián)接系統(tǒng)參數(shù)showvariableslike'char%'+--------------------------+----------------|Variable_name|Value+--------------------------+----------------|character_set_client|utf8|character_set_connection|utf8|character_set_database|gb2312|character_set_filesystem|binary|character_set_results|utf8|character_set_server|latin1|character_set_system|utf8|character_sets_dir|C:\ProgramFile+--------------------------+----------------第十九頁，共四十二頁，編輯于2023年，星期四中文，請確保表中該字段的字符集為中文兼容：big5|Big5TraditionalChinesegb2312|GB2312SimplifiedChinesegbk|GBKSimplifiedChineseutf8|UTF-8Unicode確保，聯(lián)接參數(shù)與這個字段字符集一致第二十頁，共四十二頁，編輯于2023年，星期四MySQLdump工具很多方面類似相反作用的工具M(jìn)ySQLimport。它們有一些同樣的選項。但MySQLdump能夠做更多的事情。它可以把整個數(shù)據(jù)庫裝載到一個單獨的文本文件中。這個文件包含有所有重建您的數(shù)據(jù)庫所需要的SQL命令。這個命令取得所有的模式（Schema，后面有解釋）并且將其轉(zhuǎn)換成DDL語法（CREATE語句，即數(shù)據(jù)庫定義語句），取得所有的數(shù)據(jù)，并且從這些數(shù)據(jù)中創(chuàng)建INSERT語句。這個工具將您的數(shù)據(jù)庫中所有的設(shè)計倒轉(zhuǎn)。因為所有的東西都被包含到了一個文本文件中。這個文本文件可以用一個簡單的批處理和一個合適SQL語句導(dǎo)回到MySQL中。這個工具令人難以置信地簡單而快速。決不會有半點讓人頭疼地地方。21第二十一頁，共四十二頁，編輯于2023年，星期四導(dǎo)出創(chuàng)建表結(jié)構(gòu)和插入數(shù)據(jù)的SQL語句語法：mysqldump連接信息數(shù)據(jù)庫[表1][表2]…>路徑\文件名.sql如:mysqldump–uroot–pcookbook>c:\cookbook.sql只導(dǎo)出創(chuàng)建表結(jié)構(gòu)的SQL語句語法：mysqldump連接信息–d數(shù)據(jù)庫[表1][表2]…>路徑\文件名.sql如：mysqldump–uroot–p–dcookbookstu_info>f:\backup\stu_info.sql導(dǎo)出表的SQL語句語法：mysqldump連接信息數(shù)據(jù)庫[表1][表2]…>路徑\文件名.sql如：mysqldump–uroot–pcookbookstu_info>f:\backup\stu_info.sql22第二十二頁，共四十二頁，編輯于2023年，星期四這個非常的靈活，您甚至可以使用WHERE從句來選擇您需要的記錄導(dǎo)出到文件中。要達(dá)到這樣的目的，可以使用類似于下面的命令：

MySQLdump–uroot–p–w"ID>‘200801001’"cookbookstu_info>f:\backup\stuinfo001.txt23第二十三頁，共四十二頁，編輯于2023年，星期四只導(dǎo)出插入數(shù)據(jù)的SQL語句語法：mysqldump連接信息–t數(shù)據(jù)庫[表1][表2]…>路徑\文件名.sql如：mysqldump–uroot-p–tcookbookstu_grade>f:\backup\stu_grade.sql單獨生成文本文件和SQL文件語法：mysqldump連接信息-T本地路徑數(shù)據(jù)庫名[表1][表2]…如：mysqldump–uroot–p–Tf:\backup\cookbookstu_infostu_grade第二十四頁，共四十二頁，編輯于2023年，星期四只有指定了-T參數(shù)才可以導(dǎo)出純文本文件，表示導(dǎo)出數(shù)據(jù)的目錄，./表示當(dāng)前目錄，即與執(zhí)行命令時處于同一目錄。如果不指定表，則將導(dǎo)出整個數(shù)據(jù)庫的數(shù)據(jù)。每個表會生成兩個文件，一個為.sql文件，包含建表執(zhí)行。另一個為.txt文件，只包含數(shù)據(jù)，且沒有sql指令。25第二十五頁，共四十二頁，編輯于2023年，星期四補(bǔ)充:

mysqldump支持下列選項：

--add-locks

在每個表導(dǎo)出之前增加LOCKTABLES并且之后UNLOCKTABLE。(為了使得更快地插入到MySQL)。

--add-drop-table

在每個create語句之前增加一個droptable。

--allow-keywords

允許創(chuàng)建是關(guān)鍵詞的列名字。這由在列名前面加表名的方法做到。

-c,--complete-insert

使用完整的insert語句(用列名字)。

-C,--compress

如果客戶和服務(wù)器均支持壓縮，壓縮兩者間所有的信息。

--delayed

用INSERTDELAYED命令插入行。

-e,--extended-insert

使用全新多行INSERT語法。（給出更緊縮并且更快的插入語句）

-#,--debug[=option_string]

跟蹤程序的使用(為了調(diào)試)。

26第二十六頁，共四十二頁，編輯于2023年，星期四--help

顯示一條幫助消息并且退出。

--fields-terminated-by=...

--fields-enclosed-by=...

--fields-optionally-enclosed-by=...

--fields-escaped-by=...

--lines-terminated-by=...

這些選擇與-T選擇一起使用，并且有相應(yīng)的LOADDATAINFILE子句相同的含義。

LOADDATAINFILE語法。

-F,--flush-logs

在開始導(dǎo)出前，洗掉在MySQL服務(wù)器中的日志文件。

-f,--force,

即使我們在一個表導(dǎo)出期間得到一個SQL錯誤，繼續(xù)。

-h,--host=..

從命名的主機(jī)上的MySQL服務(wù)器導(dǎo)出數(shù)據(jù)。缺省主機(jī)是localhost。

-l,--lock-tables.

為開始導(dǎo)出鎖定所有表。

-t,--no-create-info

不寫入表創(chuàng)建信息(CREATETABLE語句）

第二十七頁，共四十二頁，編輯于2023年，星期四-d,--no-data

不寫入表的任何行信息。如果你只想得到一個表的結(jié)構(gòu)的導(dǎo)出，這是很有用的！

--opt

同--quick--add-drop-table--add-locks--extended-insert--lock-tables。

應(yīng)該給你為讀入一個MySQL服務(wù)器的盡可能最快的導(dǎo)出。

-pyour_pass,--password[=your_pass]

與服務(wù)器連接時使用的口令。如果你不指定“=your_pass”部分，mysqldump需要來自終端的口令。

-Pport_num,--port=port_num

與一臺主機(jī)連接時使用的TCP/IP端口號。（這用于連接到localhost以外的主機(jī)，因為它使用Unix套接字。）

-q,--quick

不緩沖查詢，直接導(dǎo)出至stdout；使用mysql_use_result()做它。

-S/path/to/socket,--socket=/path/to/socket

與localhost連接時（它是缺省主機(jī))使用的套接字文件。

第二十八頁，共四十二頁，編輯于2023年，星期四-T,--tab=path-to-some-directory

對于每個給定的表，創(chuàng)建一個table_name.sql文件，它包含SQLCREATE命令，和一個table_name.txt文件，它包含數(shù)據(jù)。注意：這只有在mysqldump運(yùn)行在mysqld守護(hù)進(jìn)程運(yùn)行的同一臺機(jī)器上的時候才工作。.txt文件的格式根據(jù)--fields-xxx和--lines--xxx選項來定。

-uuser_name,--user=user_name

與服務(wù)器連接時，MySQL使用的用戶名。缺省值是你的登錄名。

-Ovar=option,--set-variablevar=option

設(shè)置一個變量的值?？赡艿淖兞勘涣性谙旅?。

-v,--verbose

冗長模式。打印出程序所做的更多的信息。

-V,--version

打印版本信息并且退出。

-w,--where='where-condition'

只導(dǎo)出被選擇了的記錄；注意引號是強(qiáng)制的！

"--where=user='jimf'""-wuserid>1""-wuserid<1"

第二十九頁，共四十二頁，編輯于2023年，星期四二數(shù)據(jù)導(dǎo)入使用LOADDATAINFILE'filename'命令使用mysqlimport實用程序使用mysql命令30第三十頁，共四十二頁，編輯于2023年，星期四1、使用load命令loaddatainfile‘c:/a1.txt'intotablestu_infofieldsterminatedby','enclosedby'"';可以帶的參數(shù)如下：FIELDSTERMINATEDBY''[OPTIONALLY]ENCLOSEDBY''ESCAPEDBY''LINESTERMINATEDBY''FIELDSTERMINATED表示字段分隔；[OPTIONALLY]ENCLOSED表示字段用什么字符包括起來，如果使用了OPTIONALLY，則只有CHAR和VERCHAR被包括；ESCAPED表示當(dāng)需要轉(zhuǎn)義時用什么作為轉(zhuǎn)義字符；LINESTERMINATED表示每行記錄之間用什么分隔。上面列的是缺省值，而且這些項都是可選的，不選則使用缺省值?？梢愿鶕?jù)需要進(jìn)行修改。31第三十一頁，共四十二頁，編輯于2023年，星期四您也可以設(shè)置插入語句的優(yōu)先級，如果您要把它標(biāo)記為低優(yōu)先級（LOW_PRIORITY），那么MySQL將會等到?jīng)]有其他人讀這個表的時候，才把插入數(shù)據(jù)。可以使用如下的命令：

LOADDATALOW_PRIORITYINFILE“c:/1.txt"INTOTABLEstu_grade;您也可以指定是否在插入數(shù)據(jù)的時候，取代或者忽略文件與數(shù)據(jù)表中重復(fù)的鍵值。替代重復(fù)的鍵值的語法：LOADDATALOW_PRIORITYINFILE“c:/1.txt"REPLACEINTOTABLEstu_info;上面的句子看起來有點笨拙，但卻把關(guān)鍵字放在了讓您的剖析器可以理解的地方。32第三十二頁，共四十二頁，編輯于2023年，星期四TERMINATEDBY描述字段的分隔符，默認(rèn)情況下是tab字符（\t）

ENCLOSEDBY描述的是字段的括起字符。比方以引號括起每一個字段。

ESCAPEDBY描述的轉(zhuǎn)義字符。默認(rèn)的是反些杠（backslash：\）.33第三十三頁，共四十二頁，編輯于2023年，星期四使用前面的MySQLimport命令的例子，用LOADDATAINFILE語句把同樣的文件導(dǎo)入到數(shù)據(jù)庫中:select*fromstu_gradeintooutfile'c:/2.txt'fieldsterminatedby','enclosedby'"';LOADDATAINFILE“c:/2.txt"REPLACEINTOTABLEstu_infoFIELDSTERMINATEDBY','ENCLOSEDBY'"';LOADDATAINFILE語句中有一個MySQLimport工具中沒有特點：LOADDATAINFILE可以按指定的列把文件導(dǎo)入到數(shù)據(jù)庫中。34第三十四頁，共四十二頁，編輯于2023年，星期四當(dāng)我們要把數(shù)據(jù)的一部分內(nèi)容導(dǎo)入的時候，這個特點就很重要。比方說，我們要從Access數(shù)據(jù)庫升級到MySQL數(shù)據(jù)庫的時候，需要加入一些欄目（列/字段/field）到MySQL數(shù)據(jù)庫中，以適應(yīng)一些額外的需要。這個時候，我們的Access數(shù)據(jù)庫中的數(shù)據(jù)仍然是可用的，但是因為這些數(shù)據(jù)的欄目(field)與MySQL中的不再匹配，因此而無法再使用MySQLimport工具。盡管如此，我們?nèi)匀豢梢允褂肔OADDATAINFILE，下面的例子顯示了如何向指定的欄目(field)中導(dǎo)入數(shù)據(jù)：Createtablestud_gradeasselect*fromstu_gradewhere1=2;Altertablestud_gradeaddcolumnmemovarchar(45);LOADDATAINFILE“c:/1.txt"INTOTABLEstud_grade(id,subject_id,score,semester);35第三十五頁，共四十二頁，編輯于2023年，星期四2、使用mysqlimport實用程序MySQLimport位于MySQL/bin目錄中，是MySQL的一個載入（或者說導(dǎo)入）數(shù)據(jù)的一個非常有效的工具。這是一個命令行工具。有兩個參數(shù)以及大量的選項可供選擇。這個工具把一個文本文件（textfile）導(dǎo)入到你指定的數(shù)據(jù)庫和表中。比方說我們要從文件stu_info.txt中把數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫cookbook中的表stu_info中,表名即為文件名：MySQLimport–uroot–pcookbookc:\stu_info.txt表名36第三十六頁，共四十二頁，編輯于2023年，星期四.MySQLimport的常用選項介紹:-dor--delete新數(shù)據(jù)導(dǎo)入數(shù)據(jù)表中之前刪除數(shù)據(jù)數(shù)據(jù)表中的所有信息

-for--force不管是否遇到錯誤，MySQLimport將強(qiáng)制繼續(xù)插入數(shù)據(jù)

-ior--ignoreMySQLimport跳過或者忽