MySQL数据导出与导入方法-优快云博客

1.使用SELECT ...INTO OUTFILE导出文本文件

SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]

-- OPTIONS
   FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value'

FIELDS TERMINATED BY 'value':设置字段之间的分隔符,可以为单个或多个字符,默认情况下为制表符'\t'
FIELDS [OPTIONALLY] ENCLOSED BY 'value':设置字段的包围字符,只能为单个字符,如果使用了OPTIONALLY则只有CHAR和VERCHAR等字符数据字段被包括
FIELDS ESCAPED BY 'value':设置如何写入或读取特殊字符,只能为单个字符,即设置转义字符,默认为''
LINES STARTING BY 'value':设置每行数据开头的字符,可以为单个或多个字符,默认情况下不使用任何字符
LINES TERMINATED BY 'value':设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n'

该种方法文件被创建到服务器主机上,因此必须拥有文件写入权限(FILE权限),才能使用此语法
如果想在服务器主机之外的机器上创建结果文件,应该使用比如"MySQL -e "SELECT ...">file_name"的命令
FIELDS和LINES两个子句都是自选的,如果两个都被指定了,FIELDS必须位于LINES的前面
SELECT INTO OUTFILE是LOAD DATA INFILE的补语

例子:

SELECT * FROM  test.person INTO OUTFILE "C:\person0.txt";

结果为:

SELECT * FROM test.person INTO OUTFILE "C:\person1.txt"
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';

2.使用MySQLdump命令导出文本文件

mysqldump -T path -u root -p dbname [tables] [OPTIONS]

--OPTIONS 选项
--fields -terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value

只有指定了-T参数才可以导出纯文本文件
--fields-terminated-by=value:设置字段之间的分隔符,可以为单个或多个字符,默认情况下为制表符'\t'
--fields-enclosed-by=value:设置字段的包围字符
--fields-optionally-enclosed-by=value:设置字段的包围字符,只能为单个字符,只能包括CHAR和VERVHAR等字符数据字段
--fields-escaped-by=value:控制如何写入或读取特殊字符,只能为单位字符,即设置转义字符,默认值为反斜线''
--lines-terminated-by=value:设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n'

与SELECT...INTO OUTFILE语句种的OPTIONS各个参数设置不同,这里OPTIONS各个选项等号后面的value值不要用引号括起来

例子:

mysqldump -T C:\test person -u root -p

语句执行成功,系统C盘目录下会有两个文件,分别为person.sql和person.txt

person.sql包含创建person表的CREATE语句
person.txt包含数据包的数据

3.使用MySQL命令导出文本文件

mysql -u root -p --execute="SELECT 语句" dbname>filename.txt

该命令使用--execute选项,表示执行该选项后面的语句并退出

例子:

mysql -u root --execute="SELECT * FROM person;" test>C:\person3.txt

使用MySQL命令还可以指定查询结果的显示格式,如果记录字段很多,可能一行不能完全显示,可以使用--vartical参数,将每条记录分为多行显示(类似于\G)

mysql -u root -p --vertival --execute="SELECT * FORM person;" test > C:\person4.txt

使用--html将结果集导入到html文件中

mysql -u root -p --html --execute="SELECT * FROM person;" test>C:\person5.html

使用--xml将结果集导入到html文件中

mysql -u root -p --xml --execute="SELECT * FROM person;" test>C:\person6.html

4.使用LOAD DATA INFILE方式导入文本文件

LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

-- OPTIONS
   FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value'

FIELDS TERMINATED BY 'value':设置字段之间的分隔符,可以为单个或多个字符,默认情况下为制表符'\t'
FIELDS [OPTIONALLY] ENCLOSED BY 'value':设置字段的包围字符,只能为单个字符,如果使用了OPTIONALLY则只有CHAR和VERCHAR等字符数据字段被包括
FIELDS ESCAPED BY 'value':设置如何写入或读取特殊字符,只能为单个字符,即设置转义字符,默认为''
LINES STARTING BY 'value':设置每行数据开头的字符,可以为单个或多个字符,默认情况下不使用任何字符
LINES TERMINATED BY 'value':设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n'

IGNORR number LINS选项表示忽略文件开始处的行数,number代表忽略的行数
执行LOAD DATA语句需要FILE权限

例子:

LOAD DATA INFILE 'C:\person0.txt' INTO TABLE test.person;

5.使用MySQLimport命令导入文本文件

mysqlimport -u root -p dbname filname.txt [OPTIONS]

--OPTIONS 选项
--fields -terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value

--fields-terminated-by=value:设置字段之间的分隔符,可以为单个或多个字符,默认情况下为制表符'\t'
--fields-enclosed-by=value:设置字段的包围字符
--fields-optionally-enclosed-by=value:设置字段的包围字符,只能为单个字符,只能包括CHAR和VERVHAR等字符数据字段
--fields-escaped-by=value:控制如何写入或读取特殊字符,只能为单位字符,即设置转义字符,默认值为反斜线''
--lines-terminated-by=value:设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n'
--ingore-line=n:狐狸数据文件的前n行

与SELECT...INTO OUTFILE语句种的OPTIONS各个参数设置不同,这里OPTIONS各个选项等号后面的value值不要用引号括起来

MySQLdump备份的文件只能在MySQL中使用码?

MySQLdump备份的文本文件实际是数据库的一个副本,使用该文件不经可以在MySQL中恢复数据库,而且通过对该文件的简单修改,而可以使用该文件在SQL Server或者Sybase等其他数据库中恢复数据库,这在某种程度上实现了数据库之前的迁移

如何选择备份工具?

直接赋值数据文件是最为直接,快速的备份方法,但是缺点是基本上不能实现增量备份;备份时需要确保没有使用这些表;如果在赋值一个表的同时服务器正在修改它,则赋值无效;备份文件时,最好官博服务器,然后冲洗启动服务器;为了保证数据的一致性,需要在备份文件前,执行以下SQL语句:

FLUSH TABLE WITH READ LOCK;

以上语句表示将内存中的数据都刷新到磁盘中,同时锁定数据表,以保障赋值过程中,不会有新的数据写入;这种方法备份出来的数据恢复也很简单,直接复制会原来的数据库目录下即可

MySQLhotcopy是一个PRRL程序,他hi用LOCK TABLES,FLUSH TABLES和cp或scp来快速备份数据库;它是备份数据库或单个表的最快的路径,但它只能运行在数据库文件所在的机器上,并且MySQLhotcopy只能用于备份M有ISAM表;MySQLhotcopy适合于小型数据库的备份,数据量不大,可以使用,MySQLhotcopy程序明天运行一次完全备份
MySQLdump将数据表导成SQL脚本文件,在不同的MySQL版本之间升级时相对比较合适,这也是最常用的备份方法;MySQLdump比直接复制要慢些