MySQL——表的导出和导入

https://blog.csdn.net/qq_41573234/article/details/80589048

 

有时会需要将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   
一个退格符。   
\ '   
一个单引号(“ '”)符。   
\ "   
一个双引号(“ "”)符。   
\\   
一个反斜线(“\”)符。   
\%   
一个“%”符。它用于在正文中搜索“%”的文字实例，否则这里“%”将解释为一个通配符。   
\_   
一个“_”符。它用于在正文中搜索“_”的文字实例，否则这里“_”将解释为一个通配符。   
注意，如果你在某些正文环境中使用“\%”或“\%_”，这些将返回字符串“\%”和“\_”而不是“%”和“_”。   

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

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

 【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版
————————————————
版权声明：本文为CSDN博主「时光·漫步zth」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_41573234/article/details/80589048