mysql的数据导入和导出

骑猪看日落 2022-06-09 14:18 190阅读 0赞

将表中数据导出为一个文本文件，最简单的方法是用 SELECT...INTO OUTFILE 语句，它会将查询结果直接导出为服务器主机上的一个文件。

## 利用 SELECT...INTO OUTFILE 语句组合导出数据 ##

该语句组合的语法为：使用正常的 SELECT 语句，后跟 INTO OUTFILE，最后加上要导出的文件名。默认的输出格式和 LOAD DATA 一样，因此下列语句会将表 tutorials\_tbl 导出为 /tmp/tutorials.txt，其中的数据以制表符分隔开，以换行符作为每行的终止符。

mysql> SELECT * FROM tutorials_tbl 
        -> INTO OUTFILE '/tmp/tutorials.txt';

你可以通过一些选项来改变输出格式，来指定如何以引用并限定列与记录。下面这个例子将表 tutorials\_tbl 以逗号分隔各值，以 CRLF（回车换行符）来作为行的终止符：

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
        -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
        -> LINES TERMINATED BY '\r\n';

SELECT ... INTO OUTFILE 具有下列特点：

*  输出文件直接由 MySQL 服务器创建，因此文件名应该指明其在服务器主机上的保存位置。该语句没有 LOCAL 版，这一点跟 LOAD DATA 不同。
 *  必须拥有 MySQL 的 FILE 权限，才能执行 SELECT ... INTO OUTFILE。
 *  输出文件不能是已有文件。这一特点保证了 MySQL 不会覆盖掉一些可能是非常重要的文件。
 *  你必须有服务器主机的登录账号，或者能够利用其它方式获取主机文件，否则 SELECT ... INTO OUTFILE 对你来说没有任何用处。
 *  在 UNIX 系统下，创建的文件是全局可读的，但可写权限却属于 MySQL 服务器。这意味着虽然你可以读取文件，但可能无法删除它。

## 将表导出为原始数据 ##

`mysqldump` 程序用于复制或备份表与数据库。它能把表输出为一个原始数据文件，或者是一个能重建表中记录的 INSERT 语句集合。

要想把表转储为一个数据文件，必须指定一个 `--tab` 选项，用它来指明 MySQL 服务器写入文件的目录。

例如，把数据库 TUTORIALS 中的表 tutorials\_tbl 转储为 /tmp 中的一个文件，需要使用如下命令：

$ mysqldump -u root -p --no-create-info \
                --tab=/tmp TUTORIALS tutorials_tbl
    password ******

## 将表内容或定义以 SQL 格式导出 ##

以 SQL 格式将表导出为文件，使用类似下列命令：

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
    password ******

这样创建的文件将包含如下内容：

-- MySQL dump 8.23
    --
    -- Host: localhost    Database: TUTORIALS
    ---------------------------------------------------------
    -- Server version       3.23.58
    
    --
    -- Table structure for table `tutorials_tbl`
    --
    
    CREATE TABLE tutorials_tbl (
      tutorial_id int(11) NOT NULL auto_increment,
      tutorial_title varchar(100) NOT NULL default '',
      tutorial_author varchar(40) NOT NULL default '',
      submission_date date default NULL,
      PRIMARY KEY  (tutorial_id),
      UNIQUE KEY AUTHOR_INDEX (tutorial_author)
    ) TYPE=MyISAM;
    
    --
    -- Dumping data for table `tutorials_tbl`
    --
    
    INSERT INTO tutorials_tbl 
           VALUES (1,'Learn PHP','John Poul','2007-05-24');
    INSERT INTO tutorials_tbl 
           VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
    INSERT INTO tutorials_tbl 
           VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

转储多张表，按照数据库命名》》。转储整个数据库，不需要命名数据库中的任何表：

$ mysqldump -u root -p TUTORIALS > database_dump.txt
    password ******

备份主机上的所有数据库，使用如下命令：

$ mysqldump -u root -p --all-databases > database_dump.txt
    password ******

自MySQL 3.23.12版本开始，可以使用 `--all-databases` 选项。

这种方法可以实现数据库备份。

## 将一台主机上的表或数据库复制到另一台主机上 ##

如果想把一台 MySQL 服务器上的表或数据库复制到另一台主机上，可以使用 mysqldump 程序，加上数据库名称和表名称。

在源主机上运行以下命令，它会将整个数据库都转储到 dump.txt 文件中。

$ mysqldump -u root -p database_name table_name > dump.txt
    password *****

如前所述，你可以将整个数据库都复制下来，无需使用任何具体的表名称。

接下来，在另一台主机上ftp dump.txt 文件，并运行如下命令。在运行这行命令之前，先要确保已经在目标服务器上创建了 database\_name。

$ mysql -u root -p database_name < dump.txt
    password *****

在主机间复制数据库也可以使用另一种方法，它的优点就是无需使用中介文件。将 mysqldump的输出结果直接通过网络传到远端的 MySQL 服务器上。如果你能从源数据库所在的主机上连接到两个服务器上，使用如下命令（一定要确保你能访问两台服务器）：

$ mysqldump -u root -p database_name \
           | mysql -h other-host.com database_name

以上命令的 mysqldump 部分会连接本地服务器，将转储结果写入管线。剩下的命令连接到另一台主机的远端服务器上，读取管线上传来的转储结果，将每个语句送到目的主机所在的服务器上。

\---------------------------------------------华丽的分割线-----------------------------------------------------------------------------------------------

MySQL 可以采用2种简单的方法将之前备份文件中的数据加载进 MySQL 数据库。

## 利用 LOAD DATA 导入数据 ##

MySQL 利用 LOAD DATA 语句作为批量数据加载器。下面这个范例将从当前目录中读取 dump.txt 文件，然后把它加载进当前数据库的表 mytbl 中。

`mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;`

*  如果不写 LOCAL 关键字，MySQL 会从服务器主机文件系统的根目录开始，以完整指明文件位置的绝对路径名方式开始查找数据文件。MySQL 会从给定位置读取文件。
 *  默认情况下，LOAD DATA 假定数据文件中每行都由换行符所终止，每行的数据值由制表符所分隔开。
 *  为了明确指定文件格式，使用 FIELDS 子句来描述行内字段特征， LINES 子句指定行末尾序列。下例中的 LOAD DATA 语句表明，数据文件中的值由冒号（`:`）分隔，每行由换行符及回车符所终止。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
      -> FIELDS TERMINATED BY ':'
      -> LINES TERMINATED BY '\r\n';

*  LOAD DATA 假定数据文件中的列的顺序与表中列的顺序相同。如果不为真，可以指定一个列表来指示数据文件中具体表列的加载方式。假如表有3个列：a、b和c，但数据文件中对应的是列b、c与a，则可以这样加载。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
        -> INTO TABLE mytbl (b, c, a);

## 利用mysqlimport 导入数据 ##

MySQL 还包含一个工具程序：`mysqlimport`。它相当于 LOAD DATA 的一个封装器，因而你可以直接从命令行中加载输入文件。

将 dump.txt 中的数据加载进表 mytbl，可以在 UNIX 系统的命令行中使用以下命令：

$ mysqlimport -u root -p --local database_name dump.txt
    password *****

如果使用mysqlimport，命令行选项就会提供格式说明符。mysqlimport 命令作用相当于前面的两个LOAD DATA 语句，语法如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
       --lines-terminated-by="\r\n"  database_name dump.txt
    password *****

对于 mysqlimport 来说，你怎么指定选项的次序并不重要，只要把它们写在数据库名称前面就可以了。

mysqlimport 语句使用 --columns 选项来指定列次序。

$ mysqlimport -u root -p --local --columns=b,c,a \
        database_name dump.txt
    password *****

## 处理引号与特殊字符 ##

FIELDS 子句能指定除了 TERMINATED BY 之外的其他格式选项。默认情况下，LOAD DATA 会假定值不加引号，并把反斜杠（`\`）解释为表示特殊意义的转义字符。要想明确指定引号字符，需使用 ENCLOSED BY 。MySQL 会在处理输入时将该字符从数据值末尾清除掉。改变默认的转义字符，需使用 ESCAPED BY。

在指定 ENCLOSED BY 来表示引号字符应该从数据值末尾清除时，有可能在数据值中包含引号字符，或在其之前添加转义字符。比如，如果引号和转义字符是`"`和`\`，那么输入值`"a""b\"c"`就会被解读为`a"b"c`。

对于 mysqlimport 而言，相应的指定引号和转义值的命令行选项是`--fields-enclosed-by`和`--fields-escaped-by`。