mysql数据备份与恢复之Select Into Outfile和Load Data Infile命令

SELECT ... INTO 语句

SELECT...INTO使查询结果可以存储在变量中或写入文件:

  • SELECT ... INTO var_list选择列值并将其存储到变量中。
  • SELECT ... INTO OUTFILE将选定的行写入文件。可以指定列和行终止符以产生特定的输出格式。
  • SELECT ... INTO DUMPFILE将单行写入文件而没有任何格式。

SELECT语句最多可以包含一个INTO子句,INTO可以出现在不同的位置:

  • FROM之前。例:
SELECT * INTO @myvar FROM t1;
  • 在尾随锁定子句之前。例:
SELECT * FROM t1 INTO @myvar FOR UPDATE;

一、使用Select Into Outfile命令导出数据

命令格式如下:

SELECT ...
INTO OUTFILE 'file_name'
fields terminated by 'char';

说明:
(1)OUTFILE参数指定的文件所在的路径需要有mysql的访问权限,否则会报错。
(2)每一条记录的数据之间默认以 Tab 分隔,也可使用fields terminated参数指定分隔符。
(3)执行Select into outfile和Load data infile命令需要开启在my.cnf参数文件中设置secure_file_priv参数。该参数的设置如下:
——NULL:MySQL服务会禁止导入和导出操作;
——目录名:MySQL服务只允许在这个目录中执行文件的导入和导出操作。目录必须存在,MySQL服务不会创建它;
——空字符串(’ '):代表文件可以在任意位置。

  1. 查看secure_file_priv参数的取值
mysql> show variables like '%secure_file_priv%';
   +------------------+-----------------------+
   | Variable_name    | Value                 |
   +------------------+-----------------------+
   | secure_file_priv | /var/lib/mysql-files/ |
   +------------------+-----------------------+
   1 row in set (0.01 sec)
  1. 修改secure_file_priv参数的取值
[root@Mysql11 ~]# vim /etc/my.cnf  ##编辑MySQL配置文件
#######################################################################
[mysqld]
..........
secure_file_priv=''
..........
#######################################################################

重启MySQL服务,查看secure_file_priv参数的取值

mysql> show variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)
  1. 举例
    (1)使用select导出数据——不指定分隔符
mysql> select * from stu into outfile '/tmp/stu.txt';
Query OK, 5 rows affected (0.00 sec)

查看stu.txt文件的内容,数据之间使用tab键分隔。

[root@Mysql11 ~]# cat /tmp/stu.txt
1	zhangsan	20	Xinxiang	15578941258
2	tom	20	Xinxiang	13778942222
3	jack	20	Zhengzhou	13675871454
4	john	21	Zhengzhou	13937681111
5	mark	22	Aanyang	13055882233

(2)使用select导出数据——指定分隔符

mysql> select * from stu into outfile '/tmp/stu2.txt' fields terminated by ',';
Query OK, 5 rows affected (0.00 sec)

查看stu.txt文件的内容,数据之间使用逗号(,)分隔。

[root@Mysql11 ~]# cat /tmp/stu2.txt;
1,zhangsan,20,Xinxiang,15578941258
2,tom,20,Xinxiang,13778942222
3,jack,20,Zhengzhou,13675871454
4,john,21,Zhengzhou,13937681111
5,mark,22,Aanyang,13055882233

二、使用Load Data Infile命令导入数据

命令格式如下:

LOAD DATA [LOCAL] INFILE 'file_name'
INTO TABLE tbl_name
[TERMINATED BY 'string';

说明:
(1)根据文件的格式指定相应的分隔符;
(2)在非服务端执行Load data需要使用local。比如通过B机器登录A上的mysqld,就需要用到 local 。

  1. 使用stu.txt文件导入数据
    (1)清空stu表中的数据
mysql> truncate stu;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from stu;
Empty set (0.00 sec)

(2)导入数据

mysql> load data infile '/tmp/stu.txt' into table stu;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from stu;
+----+----------+------+-----------+-------------+
| id | name     | age  | address   | phone       |
+----+----------+------+-----------+-------------+
|  1 | zhangsan |   20 | Xinxiang  | 15578941258 |
|  2 | tom      |   20 | Xinxiang  | 13778942222 |
|  3 | jack     |   20 | Zhengzhou | 13675871454 |
|  4 | john     |   21 | Zhengzhou | 13937681111 |
|  5 | mark     |   22 | Aanyang   | 13055882233 |
+----+----------+------+-----------+-------------+
5 rows in set (0.00 sec)
  1. 使用stu2.txt文件导入数据
    (1)清空stu表中的数据
mysql> truncate stu;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from stu;
Empty set (0.00 sec)

(2)导入数据

如果不指定分隔符,则会出现如下错误:

mysql> load data infile '/tmp/stu2.txt' into table stu;
ERROR 1265 (01000): Data truncated for column 'id' at row 1

指定分隔符,导入成功:

mysql> load data infile '/tmp/stu2.txt' into table stu fields terminated by ',';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from stu;
+----+----------+------+-----------+-------------+
| id | name     | age  | address   | phone       |
+----+----------+------+-----------+-------------+
|  1 | zhangsan |   20 | Xinxiang  | 15578941258 |
|  2 | tom      |   20 | Xinxiang  | 13778942222 |
|  3 | jack     |   20 | Zhengzhou | 13675871454 |
|  4 | john     |   21 | Zhengzhou | 13937681111 |
|  5 | mark     |   22 | Aanyang   | 13055882233 |
+----+----------+------+-----------+-------------+
5 rows in set (0.00 sec)
posted @ 2022-04-24 10:15  夏尔_717  阅读(802)  评论(0编辑  收藏  举报