MySQL——表的导出和导入

最新推荐文章于 2025-03-10 20:21:54 发布

时光·漫步zth

最新推荐文章于 2025-03-10 20:21:54 发布

阅读量7.2w

点赞数 19

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_41573234/article/details/80589048

版权

MySQL 专栏收录该内容

43 篇文章

订阅专栏

有时会需要将MySQL数据4中的数据导出到外部存储文件中，MySQL数据库中的数据可以导出成qI文本文件、xml文件或者html文件，同样这些导出的文件也可以导入MySQL数据库中，

一. 表的导出

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

在 MySQL 数据库中导出数据时，允许使用包含导出定义的 SELECT 语句进行数据的导出操作。该文件在服务器主机上创建，因此必须拥有文件写入权限 (FILE权限)，才能使用此语法。“SELECT...INTO OUTFILE 'filename' " 形式的 SELECT语句可以把被选择的行写入一个文件中，filename 不能是一个已经存在的文件。SELECT...INTO OUTFILE 语句的基本格式如下:

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

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

SELECT columnlist FROM table WHERE condition 为查询语句，查询结果返回满足指定条件的一条或多条记录；INTO OUTFLE 语句的作用就是把 SELECT 语句查询出来的结果导出到名称为 filename 的外部文件中，[OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句。

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 的前面。

SELECT...INTO OUTFILE 语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件，不能使用 SELECT...INTO OUTFILE 语句。在这种情况下，应该在客户主机上使用 mysql -e “SELECT ... " > file_name 这样的命令，来生成文件。

【例】使用 SELECT...INTO OUTFILE 语句将 test_db 数据库中的 fruits 表中的记录导出到文本文件。输入的语句如下:

mysql> SELECT  *  FROM   test_db.fruits   INTO   OUTFILE   "G:/fruits0.txt";
Query OK, 16 rows affected (0.11 sec)

执行结果：

可以看到默认情况下，MySQL 使用制表符“\t”分隔不同的字段，字段没有用其他字符括起来。Windows系统下的回车换行为“\r\n”，默认换行符为“\n”。

默认情况下，如果遇到 NULL 值，将会返回“\N”代表空值，反斜线“\”表示转义字符。如果使用 ESCAPED BY 选项，则N前面为指定的转义字符。

【例】使用 SELECT...INTO OUTFILE 语句将 test_db 数据库中的 fruits 表中的记录导出到文本文件，使用FIELDS 和 LINES 选项，要求字段之间使用逗号隔开，所有字段值用双引号括起来，定义转义字符为单引号“\'”。

输入语句如下:

mysql> SELECT  *  FROM   test_db.person   INTO   OUTFILE   "G:/person1.txt"
    ->   FIELDS
    ->     TERMINATED BY ','
    ->     ENCLOSED BY '\"'
    ->     ESCAPED BY '\''
    ->   LINES
    ->     TERMINATED BY '\r\n';
Query OK, 0 rows affected (0.01 sec)

执行结果：

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

使用 mysqldump 工具不仅可以将数据导出为包含 CREATE、INSERT 的 sql 文件，也可以导出为纯文本文件。

Mysqldump 将创建一个包含 CREATE TABLE 语句的 tablename.sql 文件和一个包含其数据的 tablename.txt 文件。

mysqldump 导出文本文件的基本语法格式如下:

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

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

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

OPTIONS 常见的取值如下:

--fields-terminated-by=value: 设置字段之间的分隔符可以为单个或多个字符，默认情况下为制表符“\t”。
--fields-enclosed-by=value: 设置字段的包围字符。
--fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，包括 CHAR 和 VARCHAR 等字符数据字段。
--fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”。
--lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

【例】使用 mysqldump 命令将 test_db 数据库中的 person 表中的记录导出到文本文件。使用 FIELDS 选项，要求字段之间使用逗号（,）间隔，所有字符类型字段值用双引号括起来，定义转义字符为问号（?）,每行记录以回车换行符“\r\n”结尾。

SQL 命令入下:

C:\>mysqldump -T  G:\  -u  root  -p  test_db  fruits  --fields-terminated-by=,  --fields-optionally-enclosed-by=\"  
--fields-escaped-by=?  --lines-terminated-by=\r\n
Enter password: *************

【注】语句后没分号。

fruits.txt 包含数据包中的数据：

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

相比mysqldump，mysql工具导出的结果可读性更强。

使用 mysql 导出数据文本文件语句的基本格式：

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

-execute 选项表示执行该选项后面的语句并退出，后面的语句必须用双引号括起来，dbname 为要导出的数据库名称；导出的文件中不同列之间使用制表符分隔，第1行包含各字段的名称。

【例】使用 mysql 命令，将 test_db 数据库中 fruits 表中的记录导出到文本文件。

执行的命令如下:

C:\>mysql  -u root  -p  --execute="SELECT  *  FROM  fruits;"  test_db  > G:\fruits3.txt
Enter password: *************

或

C:\>mysql  -u root  -p  --execute="SELECT  *  FROM test_db.fruits;"   > G:\fruits3.txt
Enter password: *************

fruits3.txt 包含数据包中的数据：

【例】使用 mysql 命令，将 test_db 数据库中 fruits 表中的记录导出到文本文件。使用 --vertical 参数显示结果。

执行命令：

C:\>mysql  -u root  -p --vertical --execute="SELECT  *  FROM  fruits;"  test_db  > G:\fruits4.txt
Enter password: *************

fruits3.txt 包含数据包中的数据：

*************************** 1. row ***************************
   f_id: a1
   s_id: 101
 f_name: apple
f_price: 5.20
*************************** 2. row ***************************
   f_id: a2
   s_id: 103
 f_name: apricot
f_price: 2.20
*************************** 3. row ***************************
   f_id: b1
   s_id: 101
 f_name: blackberry
f_price: 10.20
*************************** 4. row ***************************
   f_id: b2
   s_id: 104
 f_name: berry
f_price: 7.60
*************************** 5. row ***************************
   f_id: b5
   s_id: 107
 f_name: xxxx
f_price: 3.60
*************************** 6. row ***************************
   f_id: bs1
   s_id: 102
 f_name: orange
f_price: 11.20
*************************** 7. row ***************************
   f_id: bs2
   s_id: 105
 f_name: melon
f_price: 8.20
*************************** 8. row ***************************
   f_id: c0
   s_id: 101
 f_name: cherry
f_price: 3.20
*************************** 9. row ***************************
   f_id: l2
   s_id: 104
 f_name: lemon
f_price: 6.40
*************************** 10. row ***************************
   f_id: m1
   s_id: 106
 f_name: mango
f_price: 15.60
*************************** 11. row ***************************
   f_id: m2
   s_id: 105
 f_name: xbabay
f_price: 2.60
*************************** 12. row ***************************
   f_id: m3
   s_id: 105
 f_name: xxtt
f_price: 11.60
*************************** 13. row ***************************
   f_id: o2
   s_id: 103
 f_name: coconut
f_price: 9.20
*************************** 14. row ***************************
   f_id: t1
   s_id: 102
 f_name: banana
f_price: 10.30
*************************** 15. row ***************************
   f_id: t2
   s_id: 102
 f_name: grape
f_price: 5.30
*************************** 16. row ***************************
   f_id: t4
   s_id: 107
 f_name: xbababa
f_price: 3.60

【例】使用 mysql 命令，将 test_db 数据库中 fruits 表中的记录导出到 xml 文件。

执行的命令如下:

C:\>mysql  -u root  -p --xml --execute="SELECT  *  FROM  fruits;"  test_db  > G:\fruits.xml
Enter password: *************

【例】使用 mysql 命令，将 test_db 数据库中 fruits 表中的记录导出到 HTML 文件。

执行的命令如下:

C:\>mysql  -u root  -p --html --execute="SELECT  *  FROM  fruits;"  test_db  > G:\fruits.html
Enter password: *************

二. 导入文件

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

语法格式如下：

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

--OPTIONS 选项
　　FIELDS TERMINATED BY 'value' /*设置字段之间分隔符，单个或多个字符，默认为'\t'*/
　　FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /*设置字段包围分隔符，单个字符*/
　　FIELDS ESCAPED BY 'value' /*如何写入或读取特殊字符，单个字符*/
　　LINES STARTING BY 'value' /*每行数据开头的字符，单个或多个*/
　　LINES TERMINATED BY 'value' /*每行数据结尾的字符，单个或多个*/

【例】使用 LOAD DATA 语句将 G:\fruits.txt 文件中的数据导入到 test_db 数据库中的 person 表。使用FIELDS 和 LINES 选项，要求字段之间使用逗号隔开，所有字段值用双引号括起来，定义转义字符为单引号“\'”。

还原之前将 fruits 表中数据全部删除：

mysql> use  test_db
Database changed
mysql> DELETE  FROM  fruits;
Query OK, 16 rows affected (0.19 sec)

mysql> SELECT  *  FROM  fruits;
Empty set (0.00 sec)

从 fruits.txt 文件中还原数据：

mysql> LOAD  DATA  INFILE  'G:\fruits.txt' INTO  TABLE test_db.fruits
    -> FIELDS
    ->  TERMINATED BY ','
    -> ENCLOSED BY '\"'
    -> ESCAPED BY '\''
    ->  LINES
    -> TERMINATED BY '\r\n';
Query OK, 16 rows affected (0.15 sec)

查看执行结果：

mysql> SELECT  *  FROM  fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.60 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

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

使用 mysqlimport 可以导入文本文件，并且不需要登录 MySQL 客户端。mysqlimport 命令提供了许多与LOAD DATA INFILE 语句相同的功能。使用 mysqlimport 语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

语法格式如下：

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

--OPTION 选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
--ignore-lines=n

dbname 为导入的表所在的数据库名称。mysqlimport 命令不指定导入数据库的表名称，数据表的名称由导入文件的名称确定，即文件名作为表名，导入数据之前该表必须存在。

OPTIONS 为可选参数选项，其常见的取值如下：

--fields-terminated-by=value: 设置字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符“\t”。
--fields-enclosed-by=value: 设置字段的包围字符。
--fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，只包括 CHAR 和 VARCHAR 等字符数据字段。
--fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”。
--lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。
--ignore-lines=n：忽视数据文件的前 n 行

【例】使用 mysqlimport 命令将 G:\fruits.txt 文件内容导入 test_db 数据库中，字段之间使用逗号隔开，所有字段值用双引号括起来，定义转义字符为问号（?）每行记录以回车换行符“\r\n”结尾。

还原之前将 fruits 表中数据全部删除：

mysql> use  test_db
Database changed
mysql> DELETE  FROM  fruits;
Query OK, 16 rows affected (0.19 sec)

mysql> SELECT  *  FROM  fruits;
Empty set (0.00 sec)

从 fruits.txt 文件中还原数据：

C:\>mysqlimport  -u  root   -p  test_db  G:\fruits.txt   --fields-terminated-by=,  --fields-optionally-enclosed-by=\" --fields-escaped-by=?  --lines-terminated-by=\r\n
Enter password: *************
test_db.fruits: Records: 16  Deleted: 0  Skipped: 0  Warnings: 0

【注】双引号要用转义字符
mysql转义字符

\0
一个ASCII   0   (NUL)字符。
\n
一个新行符。
\t
一个定位符。
\r
一个回车符。
\b
一个退格符。
\ '
一个单引号(“ '”)符。
\ "
一个双引号(“ "”)符。
\\
一个反斜线(“\”)符。
\%
一个“%”符。它用于在正文中搜索“%”的文字实例，否则这里“%”将解释为一个通配符。
\_
一个“_”符。它用于在正文中搜索“_”的文字实例，否则这里“_”将解释为一个通配符。
注意，如果你在某些正文环境中使用“\%”或“\%_”，这些将返回字符串“\%”和“\_”而不是“%”和“_”。

有几种方法在一个字符串内包括引号：

一个字符串内用“ '”加引号的“ '”可以被写作为“ ' '”。
一个字符串内用“ "”加引号的“ "”可以被写作为“ " "”。
你可以把一个转义字符（“\”）放在引号前面。
一个字符串内用“ "”加引号的“ '”不需要特殊对待而且不必被重复或转义。同理，一个字符串内用“ '”加引号的与“ "”也不需要特殊对待。