MySQL入门--导出和导入数据

MySQL导出和导入数据

数据库导出可用于将数据库复制到另一个服务器。可以将数据库传输到在另一台主机上运行的服务器，这是最典型的数据导出任务。也可以将数据传输到运行在同一主机上的不同服务器。如果正在针对新版本 Mysql 测试服务器，并且想使用生产服务器中的实际数据，则可以执行此操作。还可以将数据装入外部应用程序，数据导出也可用于将数据从一个 RDBMS 传输到另一个 RDBMS 。

完成导出和导入操作的两种最常用的方法是：

Ø   使用 SELECT ... INTO OUTFILE 将数据导出到文件

Ø   使用 LOAD DATA INFILE 语句从文件中导入数据

1.1.        使用 SELECT...INTO OUTFILE 导出数据

可以对 SELECT 语句使用 INTO OUTFILE 子句，将结果集直接写入文件。要以这种方式使用 SELECT ，请将 INTO OUTFILE 子句置于 FROM 子句之前。

文件名称指示输出文件的位置。 mysql 会将文件写入服务器主机上的指定路径。输出文件具有以下特征：文件将写入服务器主机，而不是通过网络发送到客户机。文件不能已存在。服务器将在服务器主机上写入新文件。

要运行 SELECT … INTO OUTFILE 语句，必须使用有 FILE 权限的帐户连接到服务器。 Mysql 使用如下权限创建文件：运行 MySQL 进程的帐户将拥有文件、文件对所有用户可读。

针对语句所选的每一行，文件中都包含对应的一行。默认情况下，列值由制表符分隔，而行在换行符处终止。

         语法：

SELECT ... INTO OUTFILE 'file_name'

        [CHARACTER SET charset_name]

        [export_options]

 

export_options:

    [{FIELDS | COLUMNS}

        [TERMINATED BY 'string']

        [[OPTIONALLY] ENCLOSED BY 'char']

        [ESCAPED BY 'char']

    ]

    [LINES

        [STARTING BY 'string']

        [TERMINATED BY 'string']

 

1)        数据文件格式说明符

SELECT...INTO OUTFILE 采用默认的数据文件格式 TSV ，其中列值由制表符分隔，记录由换行符终止。要使用 SELECT...INTO OUTFILE 写入使用不同分隔符或终结符的文件，请使用 FIELDS 和 LINES 子句指定输出格式。

Ø   FIELDS 子句指定如何显示列。

l   TERMINATED BY 指定字段分隔符，默认情况下是制表符。

l   ENCLOSED BY 指定如何引住列值。默认设置为不使用引号（即，默认值为空字符串）。

l   ESCAPED BY 指明当表示换行符或制表符之类的非打印字符时要使用的转义符。默认转义符是反斜杠 (\) 字符。

Ø   LINES TERMINATED BY 子句指定行分隔符，默认情况下是换行符。

MySQL 使用反斜杠来转义特殊字符，所以必须将换行符和制表符之类的字符分别表示为“ \n ”和“ \t ”。同样，要表示反斜杠字符，则必须将其转义为如下所示：“ \\ ”。

 

2)        转义字符

命令行终结符包括换行符和回车 / 换行符对。默认的换行符终结符常见于 linux 系统，而回车 / 换行符对常见于 windows 系统。

ESCAPED BY

ESCAPED BY 子句仅控制数据文件中值的输出；它不会更改 MySQL 解释语句中特殊字符的方式。例如，如果通过写入 ESCAPED BY '@' 指定数据文件转义符为“ @ ”，并不表示您必须使用“ @ ”来转义语句中其他的特殊字符。您必须使用 MySQL 的转义符（反斜杠： \ ）来转义语句中的特殊字符，使用 LINES TERMINATED BY '\r\n' （而不是 LINES TERMINATED BY '@r@n' ）之类的语法。

转义字符含义

\N NULL

\0 NULL （零）字节

\b 退格

\n 换行

\r 回车

\s 空格

\t 制表符

\ ′ 单引号

\" 双引号

\\ 反斜杠

以上所有转义字符可以单独使用或者在较长的字符串中使用，但 \N 除外，该序列只有在单独出现时才用作 NULL 。

3)        用法示例

mysql> select * into outfile 't1.tsv' from t1;

ERROR 1290 (HY000): The MySQL Server is running with the --secure-file-priv option so it cannot execute this statement

mysql> show variables like 'secure%';

+------------------+-----------------------+

| Variable_name    | Value                 |

+------------------+-----------------------+

| secure_auth      | ON                    |

| secure_file_priv | /var/lib/mysql-files/ |

+------------------+-----------------------+

2 rows in set (0.05 sec)

 

mysql> select * into outfile '/var/lib/mysql-files/t1.tsv' from t1;

Query OK, 7 rows affected (0.01 sec)

注意：如果配置了 secure_file_priv 则必须将导出文件导出到该目录，否则报错 ERROR 1290 ；

[root]# cat /var/lib/mysql-files/t1.tsv

100     a

200     a

300     a

mysql> select * into outfile '/var/lib/mysql-files/t1a.tsv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

    -> from t1;

Query OK, 7 rows affected (0.00 sec)

[root]# cat t1a.tsv

100,"a"

200,"a"

300,"a"

 [root]#

 

1.2.        使用 LOAD DATA INFILE 导入数据

LOAD DATA INFILE 语句将数据文件中的值读入表。 LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的逆向操作。如果要导入的数据文件包含使用制表符或逗号分隔的表数据，请使用 LOAD DATA INFILE 命令。此类文件最重要的特征是：

n   列值分隔符

n   行分隔符

n   用于引住值的字符（例如：引号）

n   文件中是否指定了列名

n   导入前是否有标头指示要跳过的表行

n   文件在文件系统中的位置

n   访问文件是否需要有相应权限

n   列的顺序

n   文件和表中的列数是否匹配

语法：

LOAD DATA

    [LOW_PRIORITY | CONCURRENT] [LOCAL]

    INFILE 'file_name'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [PARTITION (partition_name [, partition_name] ...)]

    [CHARACTER SET charset_name]

    [{FIELDS | COLUMNS}

        [TERMINATED BY 'string']

        [[OPTIONALLY] ENCLOSED BY 'char']

        [ESCAPED BY 'char']

    ]

    [LINES

        [STARTING BY 'string']

        [TERMINATED BY 'string']

    ]

    [IGNORE number {LINES | ROWS}]

    [(col_name_or_user_var

        [, col_name_or_user_var] ...)]

    [SET col_name={expr | DEFAULT},

        [, col_name={expr | DEFAULT}] ...]

示例：

LOAD DATA INFILE '/tmp/City.txt' FIELDS TERMINATED BY ',' INTO TABLE City;

 

1)        跳过或转换输入数据

Ø   忽略数据文件行

要忽略数据文件的开始部分，可以使用 IGNORE n LINES 子句，其中， n 是一个整数，表示要忽略的输入行数。当文件以列名行（而不是数据值行）开始时，请使用此子句。

mysql> LOAD DATA INFILE '/tmp/City.txt'

-> INTO TABLE City IGNORE 2 LINES;

 

Ø   忽略或转换列值

您可在列列表和可选的 SET 子句中提供用户变量，该子句的语法类似于 UPDATE 语句中的 SET 子句。在将从文件中读取的数据值插入表中之前， LOAD DATA INFILE 将对其进行转换，处理用户变量中所包含的值。要将输入数据列分配给用户变量而不是表列，请以列列表的形式提供用户变量的名称。如果将列分配给 SET 表达式中未使用的用户变量，则语句将忽略该列中的值，不会将其插入表中。

LOAD DATA INFILE '/tmp/City.txt'

INTO TABLE City ( @skip, @Name,CountryCode, @District, Population)

SET name=CONCAT(@Name,' ',@District);

在语句列的列表中指定用户变量（而不是列名称），通过使用 SET 子句（可选）转换列值，该语句将忽略 SET 表达式中未使用的变量的值。

 

2)        重复记录

使用 INSERT 或 REPLACE 语句向表添加新行时，可以控制语句对包含表中已有键的行的处理方法。可以允许语句生成错误，可以使用 IGNORE 子句放弃该行，也可以使用 ON DUPLICATE KEY UPDATE 子句修改现有的行。

LOAD DATA INFILE 提供了对重复行的相同级别控制，即通过使用两个修饰符关键字 IGNORE （放弃包含重复键的行）和 REPLACE （替换为文件中包含相同键的版本）；但是，其重复项处理行为根据数据文件是位于服务器主机上还是位于客户机主机上而稍有不同，所以使用 LOAD DATA INFILE 时，必须考虑数据文件的位置。

3)        从服务器主机装入文件

装入位于服务器主机上的文件时， LOAD DATA INFILE 对包含重复唯一键的行的处理方法如下：

Ø   默认情况下，输入记录造成重复键违规将产生一个错误；不会装入数据文件的剩余部分。该点之前的已处理记录将被装入表中。

Ø   如果在文件名后提供 IGNORE 关键字，将忽略造成重复键违规的新记录，并且语句不会生成错误。 LOAD DATA INFILE 将处理整个文件，装入所有不包含重复键的记录，并放弃剩余记录。

Ø   如果在文件名后提供 REPLACE 关键字，造成重复键违规的新记录将替换表中现存的包含重复键值的任何记录。 LOAD DATA INFILE 将处理整个文件，将文件中的所有记录装入表中。

4)        从客户机主机装入文件

从客户机主机装入文件时，默认情况下 LOAD DATA INFILE 将忽略包含重复键的记录。即，默认行为与指定 IGNORE 选项时相同。这是因为客户机 / 服务器协议不允许在传输开始后中断从客户机主机到服务器的数据文件传输，因此不方便在操作过程中中止操作。

5)        用法示例

mysql>use test

mysql> CREATE TABLE `t1` (

    ->   `f1` int(11) DEFAULT NULL,

    ->   `f2` varchar(20) DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    -> ;

Query OK, 0 rows affected (0.17 sec)

导入默认格式 t1.tsv 文件

mysql> LOAD DATA local INFILE '/var/lib/mysql-files/t1.tsv' IGNORE INTO TABLE t1;

Query OK, 7 rows affected (0.01 sec)

Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

 

mysql>

mysql>

mysql> select * from t1;

+------+------+

| f1   | f2   |

+------+------+

|  100 | a    |

|  200 | a    |

|  300 | a    |

|  400 | a    |

|    1 | a    |

|  101 | a    |

|  111 | b    |

+------+------+

7 rows in set (0.00 sec)

 

导入指定格式 t1.tsv 文件

mysql> LOAD DATA local INFILE '/var/lib/mysql-files/t1a.tsv' IGNORE INTO TABLE t1

    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ignore 1 lines ;

Query OK, 6 rows affected (0.00 sec)

Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

 

mysql>

示例：

LOAD DATA local INFILE '/Users/xxx/Downloads/loaddata.txt' IGNORE INTO TABLE testLoadData

FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ignore 1 lines (username, age, description);