有时经常会需要将MySQL数据库中的数据导出到外部存储文件中,MySQL数据库中的数据库可以导出成sql文本文件、xml文件或者html文件以及txt文本文件,同样这些导出文件也可以导入到MySQL数据库中。
导出文本文件
1)用SELECT * INTO OUTFILE导出文本文件
MySQL数据库导出数据时,允许使用包含导出定义的SELECT语句进行数据的导出操作。该文件被创建到服务器主机上,因此必须拥有文件的写入权限并且此文件不能提前存在,才能使用此语法。语法如下:
1
|
SELECT
col1
.
.
FROM
table_name
WHERE
condition
INTO
OUTFILE
"file_name"
[
options
]
;
|
[options]
1
2
3
4
5
6
|
FIELDS
TERMINATED
BY
'value'
#设置字段之间的分割字符,可以为单个或多个字符,默认为制表符'\t';
FIELDS
[
OPTIONALLY
]
ENCLOSED
BY
'value'
#设置字段的包围字符,只能够为单个字符,如果使用OPTIONALLY则只有CHAR和VARCHAR等字符数据字段被包括;
FIELDS
ESCAPED
BY
'value'
#设置如何写入或读取特殊字符,只能够为单个字符,即设置转衣服,默认值为'\';
LINES
STARTING
BY
'value'
#设置每行数据开头的字符,可以为单个或多个字符,默认情况下不适用任何字符;
LINES
TERMINATED
BY
'value'
#设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n';
#FIELDS和LINES两个子句都是自选的,但是如果两个都被指定了,FIELDS必须位于LINES的前面。
|
实例
1
2
3
4
5
|
# 以默认制表符“\t”格式导出数据到/tmp/1.txt文件;
Mysql
>
select
*
from
TABLE_NAME
into
outfile
"/tmp/1.txt"
;
# 以分号格式导出数据到/tmp/1.txt文件;
Mysql
>
select
*
from
TABLE_NAME
into
outfile
"/tmp/1.txt"
FIELDS
TERMINATED
BY
':'
;
|
PS:可以把导入的文本文件格式化,命令“unix2dos 1.txt“是把Linux格式的文本格式化成Windows格式的,命令”dos2unix“相反。如果要把文本文件导入到excel中,可以创建新表格—–打开文件(所有文件)—–选择txt文件—–选择分割方式(tab/space/,)。
2)用mysqldump命令导出文本文件
Mysqldump工具不仅可以做备份数据的工具可以将数据导出为包含CREATE、INSERT的sql文件,也可以导出为文本文件。
1
|
mysqldump
-
T
path
-
uroot
-
predhat
db
_name
[
tables
]
[
options
]
|
[options]
1
2
3
4
5
|
--
fields
-
terminated
-
by
=
value
#设置字段之间的分割字符,可以为单个或多个字符,默认为制表符'\t';
--
field
-
enclosed
-
by
=
value
#设置字段的包围字符;
--
fields
-
optionally
-
enclosed
-
by
=
valu
#设置字段的包围字符,只能够为单个字符,如果使用OPTIONALLY则只有CHAR和VARCHAR等字符数据字段被包括;
--
fields
-
escaped
-
by
=
value
#设置如何写入或读取特殊字符,只能够为单个字符,即设置转衣服,默认值为'\';
--
lines
-
terminated
-
by
=
value
#设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n';
|
只有指定了-T参数才可以导出纯文本文件,path表示导出数据的目录,tables为指定要导出的表名称,如果不指定,将导出数据库db_name中所有的表。[options]为可选参数选项,这些选项需要结合-T选项使用才有效。
1
2
|
# 此语句将会在/tmp目录下生成两个文件,一个是1.sql和1.txt;
$
mysqldump
-
T
/
tmp
/
1.txt
db_name
.table_name
-
uroot
-
predhat
|
1
|
$
mysqldump
-
T
/
tmp
/
1.txt
db_name
.table_name
-
uroot
-
predhat
--
fields
-
terminated
-
by
=
:
--
fields
-
optionally
-
enclosed
-
by
=
\"
--
fields
-
escaped
-
by
=
?
--
lines
-
terminated
-
by
=
\
r
\
n
|
PS:所有操作中-uroot是指定登陆用户,-predht是指定登陆使用的密码。
3)用mysql命令导出文本文件
MySQL是一个功能丰富的工具命令,使用mysql还可以在命令行模式下执行SQL指令,将查询结果导入到文本文件中。相比mysqldump,mysql工具导出的结果可读性更强。语法如下:
1
2
3
4
5
|
# 把查询结果导出到文本文件;
$
mysql
-
uroot
-
predhat
-
e
"SELECT * FROM table_name;"
db_name
>
filename
.txt
# 把查询结果导出成html格式;
$
mysql
-
uroot
--
predhat
--
html
-
e
"SELECT * FROM table_name;"
db_name
>
filename
.html
|
导入文本文件
1)用LOAD DATA INFILE方式导入文本文件
MySQL允许将数据导出到外部文件,也可以从外部文件导入数据。MySQL提供了一些导入数据的工具,这些工具有LOAD DATA语句、source命令和mysql命令。LOAD DATA INFILE语句用于高速地从一个文本文件中读取行,并装入一个表中。文件名称必须为文字字符串。下面介绍LOAD DATA语句的语法。
1
|
LOAD
DATA
INFILE
‘
filename
.txt’
INTO
TABLE
table
_name
[
options
]
[
IGNORE
number
LINES
]
|
[options]
1
2
3
4
5
6
|
FIELDS
TERMINATED
BY
'value'
#设置字段之间的分割字符,可以为单个或多个字符,默认为制表符'\t';
FIELDS
[
OPTIONALLY
]
ENCLOSED
BY
'value'
#设置字段的包围字符,只能够为单个字符,如果使用OPTIONALLY则只有CHAR和VARCHAR等字符数据字段被包括;
FIELDS
ESCAPED
BY
'value'
#设置如何写入或读取特殊字符,只能够为单个字符,即设置转衣服,默认值为'\';
LINES
STARTING
BY
'value'
#设置每行数据开头的字符,可以为单个或多个字符,默认情况下不适用任何字符;
LINES
TERMINATED
BY
'value'
#设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n';
[
IGNORE
number
LINES
]
#选项表示忽略文件开始处的行数,number表示忽略的行数。执行LOAD DATA语句需要FILE权限;
|
实例:将/tmp/2.txt文件以':’为分隔符将多个字段数据导入到user.aa表中,aa表必须提前存在,且表字段需设定好。
1
|
Mysql
>
load
data
infile
'/tmp/2.txt'
into
table
user
.aa
fields
terminated
by
':'
;
|
2)用mysqlimport命令导入文本文件
Mysqlimport是MySQL内置的一个工具,使用mysqlimport可以导入文本文件,并且不需要登陆MySQL客户端。Mysqlimport命令提供许多与LOAD DATA INFILE语句相同的功能。大多数选项直接对应LOAD DATA INFILE子句,语法如下:
1
2
3
4
5
6
7
8
|
mysqlimport
-
uroot
-
predhat
db_name
filename
.txt
[
options
]
[
options
]
--
fields
-
terminated
-
by
=
value
--
field
-
enclosed
-
by
=
value
--
fields
-
optionally
-
enclosed
-
by
=
value
--
fields
-
escaped
-
by
=
value
--
lines
-
terminated
-
by
=
value
--
ignore
-
lines
=
n
|