MySQL提供了幾種可供選擇的數(shù)據(jù)導(dǎo)入導(dǎo)出方式。
基本分為兩種形式，一種是sql形式的數(shù)據(jù)導(dǎo)入導(dǎo)出(mysqldump,mysql,source)，另一種是自定義數(shù)據(jù)的導(dǎo)入導(dǎo)出(load data infile, into outfile)。
自定義數(shù)據(jù)格式的導(dǎo)入導(dǎo)出有一個(gè)好處就是數(shù)據(jù)可以是csv格式的，而且用的非常多。比如大型的數(shù)據(jù)，如果往查詢到數(shù)據(jù)然后寫到excel里面，效率肯定是一個(gè)問題。但是用mysql的自帶的命令，那么就可以讓mysql自己生成csv文件，這樣速度會(huì)快不少。導(dǎo)入也是一樣，就是數(shù)據(jù)文件可以傳到服務(wù)器上，然后使用mysql的命令導(dǎo)入到數(shù)據(jù)庫中，比讀csv格式的文件然后插入到數(shù)據(jù)庫中效率會(huì)提高不少。
下面附上這些命令的具體描述：
mysql中Load Data InFile是用于批量向數(shù)據(jù)表中導(dǎo)入記錄。
首先創(chuàng)建一個(gè)表
Use Test;
Create Table TableTest(
`ID` mediumint(8) default '0',
`Name` varchar(100) default ''
) TYPE=MyISAM;
向數(shù)據(jù)表導(dǎo)入數(shù)據(jù)
Load Data InFile 'D:/Data.txt' Into Table `TableTest`
常用如下：
Load Data InFile 'D:/Data.txt' Into Table `TableTest` Lines Terminated By '"r"n';
這個(gè)語句，字段默認(rèn)用制表符隔開，每條記錄用換行符隔開，在Windows下?lián)Q行符為“"r"n”
D:/Data.txt 文件內(nèi)容如下面兩行：
1 A
2 B
“1”和“A”之間有一個(gè)制表符
這樣就導(dǎo)進(jìn)兩條記錄了。
自定義語法
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 '"'
表示每個(gè)字段用逗號(hào)分開，內(nèi)容包含在雙引號(hào)內(nèi)
Lines Terminated By '"r"n';
表示每條數(shù)據(jù)用換行符分開
導(dǎo)入數(shù)據(jù)庫
常用source 命令
進(jìn)入mysql數(shù)據(jù)庫控制臺(tái)，
如mysql -u root -p
mysql>use 數(shù)據(jù)庫
然后使用source命令，后面參數(shù)為腳本文件（如這里用到的.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';
把表的數(shù)據(jù)導(dǎo)出
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.導(dǎo)出整個(gè)數(shù)據(jù)庫
mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 > 導(dǎo)出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.導(dǎo)出一個(gè)表
mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 表名> 導(dǎo)出文件的路徑和名稱
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql