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