mysql语句-select...into outfile

1 简介
    SELECT INTO…OUTFILE语句把表数据导出到一个文本文件中，并用LOAD DATA …INFILE语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。
    也可以将查询结果保存在变量中。
2 语法结构

[sql]  view plain copy

1. SELECT  
2. [INTO OUTFILE 'file_name'  
3.         [CHARACTER SET charset_name]  
4.         export_options  
5.       | INTO DUMPFILE 'file_name'  
6.       | INTO var_name [, var_name]]  

3 测试表结构及数据
    3.1 创建测试数据库及表

[sql]  view plain copy

1. mysql> create database loaddata;    
2. mysql> use loaddata;    
3. mysql> create table loadtest (c1 int(10), c2 varchar(20), c3 varchar(20), c4 varchar(20));   

    3.2 插入测试数据

[sql]  view plain copy

1. mysql> insert into loadtest values (100, 'column2', 'column3', 'column4');    
2. mysql> insert into loadtest values (200, 'line2', 'line3', 'line4');    

4 语法解释及实例验证
    CHARACTER SET：
    数据被转换成CHARACTER SET指定的编码格式输出。如果不指定的话默认为binary，即不做转换，如果指定多个编码格式，输出文件将不能被正确载入。
    export_options：
    用于语句的exort_options部分的语法包括部分 FIELDS 和 LINES 子句，这些子句与LOAD DATA INFILE语句同时使用，详细用法可参考我前面文章：
     http://blog.csdn.net/jesseyoung/article/details/41312337 
    SELECT***INTO OUTFILE 'file_name':
    将选择的行输出到文件，文件创建在服务器主机上，我们需要有FILE权限。字段的结束符，包裹符号以及行的结束符号可以指定输出格式。
    我们也可以将文件输出到其它客户端主机上，但不能用SELECT ... INTO OUTFILE，例如我们在192.168.1.95主机上执行操作,文件依然会被创建在192.168.1.94服务器主机上。

[sql]  view plain copy

1. [root@localhost /]# mysql -h192.168.1.94 -ujesse -pjesse -P3306  
2. mysql> select * into outfile '/tmp/outfile.txt' from loadtest;  
3. ERROR 1086 (HY000): File '/tmp/outfile.txt' already exists  

    下面的方法也一样会将文件创建在192.168.1.94服务器主机上。

[sql]  view plain copy

1. [root@localhost /]# mysql -h192.168.1.94 -ujesse -pjesse -P3306 -e"select * into outfile '/tmp/outfile.txt' from loaddata.loadtest;"  
2. Warning: Using a password on the command line interface can be insecure.  
3. ERROR 1086 (HY000) at line 1: File '/tmp/outfile.txt' already exists  

    我们只能用类似mysql -e "SELECT ..." > file_name的命令将文件输出到客户机上。

[sql]  view plain copy

1. [root@localhost /]# mysql -h192.168.1.94 -ujesse -pjesse -P3306 -e"select *  from loaddata.loadtest;" > /tmp/loadtest.txt  

    指定格式输出：

[sql]  view plain copy

1. mysql> select * into outfile '/tmp/outfile.txt' from loadtest;  
2. mysql> select * into outfile '/tmp/outfile.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '#' LINES TERMINATED BY '\n' from loadtest;  

    SELECT***INTO var_name [, var_name]：
    将列值输出到变量。into语句可以命名一个或多个变量列表，这些变量可以是用户定义变量，存储过程及函数参数，或者存储程序局部变量。查询应该返回单行装入变量，变量的个数应该和查询的列的个数相同。用户变量对大小写不敏感。

[sql]  view plain copy

1. mysql> select * into @a, @b, @c, @d from loadtest limit 1;  
2. mysql> select c1, c2, c3, c4 into @a, @b, @c, @d from loadtest limit 1;  

    变量的个数和查询的列的个数不相同

[sql]  view plain copy

1. mysql> select c1, c2, c3, c4 into @a, @b, @c from loadtest limit 1;      
2. ERROR 1222 (21000): The used SELECT statements have a different number of columns  
3. mysql> select c1, c2, c3 into @a, @b, @c, @d from loadtest limit 1;      
4. ERROR 1222 (21000): The used SELECT statements have a different number of columns  

    查询的列返回多行

[sql]  view plain copy

1. mysql> select c1, c2, c3, c4 into @a, @b, @c, @d from loadtest;  
2. ERROR 1172 (42000): Result consisted of more than one row  

    SELECT***INTO DUMPFILE 'file_name'：
    将选择的行输出到文件，输出内容没有任何格式，且所有内容都放在一行。

[sql]  view plain copy

1. mysql> select * into dumpfile '/tmp/dumpfile.txt' from loadtest;  

5 一些错误
    服务器防火墙未关闭导致远程连接mysql出错：

[sql]  view plain copy

1. [root@localhost /]# /home/ACTIONTECH-HA/mysql-install/bin/mysql -h192.168.1.94 -ujesse -pjesse -P3306  
2. Warning: Using a password on the command line interface can be insecure.  
3. ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.94' (113)  

    解决办法：关闭服务器端防火墙或开放mysql端口。

****************************************************************************************
    原文地址：http://blog.csdn.net/jesseyoung/article/details/41346861
    博客主页：http://blog.csdn.net/jesseyoung
****************************************************************************************