• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL表导入导出

MySQL 彭东稳 9年前 (2016-04-03) 25022次浏览 已收录 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 工具不仅可以做备份数据的工具可以将数据导出为包含 CREATEINSERT sql 文件,也可以导出为文本文件。

[options]

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

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

mysql 客户端是一个功能丰富的命令行工具,使用 mysql 可以在命令行模式下执行 SQL 语句,将查询结果导入到文本文件中。相比 mysqldump 来说,mysql 工具导出的结果可读性更强,更灵活。语法如下:

默认重定向到文件中的结果集已经去掉了无用的字符,所谓无用字符就是格式排版字符。当然,你也可以使用-N去掉列名称;-s去掉无用字符。一般组合使用即可,另外,-ss 或-NB效果等同于-s -N

上面导出文件虽然后缀为 csv,但本质还是一个文本文件,如果需要 csv 文件呢?最简单可以使用 Linux 下的 sed 命令处理一下。

简单说下,我们得到的数据,每个字段之间是用制表符 \t 分隔的。在 Linux 中,cat -A file可以把文件中的所有可见的和不可见的字符都显示出来,在 vim 中,如何将不可见字符也显示出来呢?当然,如果只是想在 vim 中查看的话,可以这样:%!cat -A在Vim中调用 cat 转换显示。这样的做法不便于编辑,其实 vim 本身是可以设置显示不可见字符的。

只需要:set invlist即可以将不可见的字符显示出来,例如,会以^I表示一个 tab 符(\t),$表示一个回车符等。最后,:set nolist可以回到正常的模式。

简单了解了这些,使用下面的 sed 命令对这些特殊字符处理一下就好了。转换为 csv 格式的排版模式即可。

sed 命令用到了正则表达式,所以第一个表达式 s/\t/","/g 把所有的制表符替换为 ",",然后第二个表达式,将每行的开头加上双引号,第三个表达式将每行的结尾添加一个双引号和一个回车符\r(原始数据每行结尾只有一个换行符\n,sed 命令是处理去掉回车符后的字符串)。

二、导入文本文件

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 命令导入文本文件

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


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

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