MySQL提供了幾種可供選擇的數據導入導出方式。
基本分為兩種形式，一種是sql形式的數據導入導出(mysqldump,mysql,source)，另一種是自定義數據的導入導出(load data infile, into outfile)。
自定義數據格式的導入導出有一個好處就是數據可以是csv格式的，而且用的非常多。比如大型的數據，如果往查詢到數據然后寫到excel里面，效率肯定是一個問題。但是用mysql的自帶的命令，那么就可以讓mysql自己生成csv文件，這樣速度會快不少。導入也是一樣，就是數據文件可以傳到服務器上，然后使用mysql的命令導入到數據庫中，比讀csv格式的文件然后插入到數據庫中效率會提高不少。
下面附上這些命令的具體描述：
mysql中Load Data InFile是用于批量向數據表中導入記錄。
首先創建一個表
Use Test;
Create Table TableTest(
`ID` mediumint(8) default '0',
`Name` varchar(100) default ''
) TYPE=MyISAM;
向數據表導入數據
Load Data InFile 'D:/Data.txt' Into Table `TableTest`
常用如下：
Load Data InFile 'D:/Data.txt' Into Table `TableTest` Lines Terminated By '"r"n';
這個語句，字段默認用制表符隔開，每條記錄用換行符隔開，在Windows下換行符為“"r"n”
D:/Data.txt 文件內容如下面兩行：
1 A
2 B
“1”和“A”之間有一個制表符
這樣就導進兩條記錄了。
自定義語法
Load Data InFile 'D:/Data.txt' Into Table `TableTest` Fields Terminated By ',' Enclosed By '"'
Escaped By '"' Lines Terminated By '"r"n';
Fields Terminated By ',' Enclosed By '"' Escaped By '"'
表示每個字段用逗號分開，內容包含在雙引號內
Lines Terminated By '"r"n';
表示每條數據用換行符分開
導入數據庫
常用source 命令
進入mysql數據庫控制臺，
如mysql -u root -p
mysql>use 數據庫
然后使用source命令，后面參數為腳本文件（如這里用到的.sql）
mysql>source d:"wcnc_db.sql
也可以使用mysql命令（在bin目錄下）
.../bin/mysql db1 > xx.sql;
和 Load Data InFile 相反的是
Select * From `TableTest` Into OutFile 'D:/Data_OutFile.txt';
把表的數據導出
Using command line tools to export data from a MySQL database into a CSV file is quite easy. Here's
how:
mysql -uexampleuser -pletmein exampledb -B -e "select * from "`person"`;" | sed 's/
"t/","/g;s/^/"/;s/$/"/;s/"n//g' > filename.csv
Here is some sample output of the above:
"id","username","group","password"
"1","tux","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"2","tlugian","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"3","saiyuki","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"4","fred","staff","5f4dcc3b5aa765d61d8327deb882cf99"
"5","barney","staff","5f4dcc3b5aa765d61d8327deb882cf99"
"6","wilma","admin","5f4dcc3b5aa765d61d8327deb882cf99"
And now for the explanation:
Starting with the MySQL command. I wont explain the -u and -p options they are straight forward (if
in doubt man mysql). The -B option will delimit the data using tabs and each row will appear on a
new line. The -e option denotes the command to run once you have logged into the database. In this
case we are using a simple SELECT statement.
Onto sed. The command used here contains three seperate sed scripts:
s/"t/","/g;s/^/"/ <--- this will search and replace all occurences of 'tabs' and replace them
with a ",".
;s/$/"/; <--- This will place a " at the start of the line.
s/"n//g <---- This will place a " at the end of the line.
After running the result set through sed we redirect the output to a file with a .csv extension.
mysqldump命令的輸入是在bin目錄下.
1.導出整個數據庫
mysqldump -u 用戶名 -p 數據庫名 > 導出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.導出一個表
mysqldump -u 用戶名 -p 數據庫名 表名> 導出文件的路徑和名稱
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql