注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL表导入导出

MySQL 彭东稳 6569次浏览 已收录 0个评论

有时经常会需要将MySQL数据库中的数据导出到外部存储文件中,MySQL数据库中的数据库可以导出成sql文本文件、xml文件或者html文件以及txt文本文件,同样这些导出文件也可以导入到MySQL数据库中。

一、导出文本文件

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

MySQL数据库导出数据时,允许使用包含导出定义的SELECT语句进行数据的导出操作。该文件被创建到服务器主机上,因此必须拥有文件的写入权限并且此文件不能提前存在,才能使用此语法。语法如下:

FIELDS

TERMINATED BY ‘string’

设置输出字段之间的分割字符,可以为单个或多个字符,默认为制表符’\t’,等同于TERMINATED BY ‘/t';

[OPTIONALLY] ENCLOSED BY ‘char’

设置输出字段的包围字符,只能够为单个字符,如果使用[OPTIONALLY]则只有CHAR和VARCHAR等字符数据字段被包括。默认为ENCLOSED BY ”;

ESCAPED BY ‘char’

设置如何写入或读取特殊字符,只能够为单个字符,即设置转义符,默认值为’\';

LINES

STARTING BY ‘string’

设置每行数据开头的字符,可以为单个或多个字符,默认情况下不适用任何字符;

TERMINATED BY ‘string’

设置每行数据结尾的字符,可以为单个或多个字符,默认值为’\n’;FIELDS和LINES两个子句都是自选的,但是如果两个都被指定了,FIELDS必须位于LINES的前面。

实例

语法FIELDS和LINES子句是相同的,两个子句都是可选的,但是如果同时指定了两个子句,则FIELDS必须在LINES前。如果用户没有指定一个FIELDS子句,则使用默认FIELDS TERMINATED BY ‘/t’ ENCLOSED BY ” ESCAPED BY ‘//’。

PS:可以把导入的文本文件格式化,命令“unix2dos 1.txt“是把Linux格式的文本格式化成Windows格式的,命令”dos2unix“相反。如果要把文本文件导入到excel中,可以创建新表格—–打开文件(所有文件)—–选择txt文件—–选择分割方式(tab/space/,)

2. 用mysqldump命令导出文本文件

mysqldump工具不仅可以做备份数据的工具可以将数据导出为包含CREATEINSERTsql文件,也可以导出为文本文件。

[options]

只有指定了-T参数才可以导出纯文本文件,path表示导出数据的目录,tables为指定要导出的表名称,如果不指定,将导出数据库db_name中所有的表。[options]为可选参数选项,这些选项需要结合-T选项使用才有效。

PS:所有操作中-uroot是指定登陆用户,-predht是指定登陆使用的密码。

3. 用mysql命令导出文本文件

MySQL是一个功能丰富的工具命令,使用mysql还可以在命令行模式下执行SQL指令,将查询结果导入到文本文件中。相比mysqldumpmysql工具导出的结果可读性更强。语法如下:

二、导入文本文件

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

MySQL允许将数据导出到外部文件,也可以从外部文件导入数据。MySQL提供了一些导入数据的工具,这些工具有LOAD DATA语句、source命令和mysql命令。LOAD DATA INFILE语句将文本文件中的行以非常高的速度读入表中。 LOAD DATA INFILE是补充 SELECT … INTO OUTFILE。载入的文件名称必须为文字字符串。下面介绍LOAD DATA语句的语法。

这两个语句的语法FIELDS和LINES子句是相同的,两个子句都是可选的,但是如果同时指定了两个子句,则FIELDS必须在LINES前。

FIELDS

TERMINATED BY ‘string’

设置输入字段之间的分割字符,可以为单个或多个字符,默认为制表符’\t’,等同于TERMINATED BY ‘/t';

[OPTIONALLY] ENCLOSED BY ‘char’

设置输入字段的包围字符,只能够为单个字符,如果使用[OPTIONALLY]则只有CHAR和VARCHAR等字符数据字段被包括。默认为ENCLOSED BY ”;

ESCAPED BY ‘char’

设置如何写入或读取特殊字符,只能够为单个字符,即设置转义符,默认值为’\';

LINES

STARTING BY ‘string’

设置每行数据开头的字符,可以为单个或多个字符,默认情况下不适用任何字符;

TERMINATED BY ‘string’

设置每行数据结尾的字符,可以为单个或多个字符,默认值为’\n’;FIELDS和LINES两个子句都是自选的,但是如果两个都被指定了,FIELDS必须位于LINES的前面。

[IGNORE number LINES]

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

实例:将/tmp/2.txt文件以':’为分隔符将多个字段数据导入到user.aa表中,aa表必须提前存在,且表字段需设定好。

在导入的过程中可能会报如下错误:ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement.

权限问题,可以查看一下这个变量,如下:

意思是允许这个目录下的文件进行LOAD操作,所以把文件拷贝到这个目录下执行就好了。

2. 用mysqlimport命令导入文本文件

MysqlimportMySQL内置的一个工具,使用mysqlimport可以导入文本文件,并且不需要登陆MySQL客户端。Mysqlimport命令提供许多与LOAD DATA INFILE语句相同的功能。大多数选项直接对应LOAD DATA INFILE子句,语法如下:


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (1)or分享 (0)
关于作者:

您必须 登录 才能发表评论!