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

声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。