MySQL SELECT ... INTO OUTFILE和LOAD DATA INFILE用法（二）

屠夫吧

昨天分享了MySQL SELECT ... INTO OUTFILE和LOAD DATA INFILE的语法以及一些注意事项，下面分享下SELECT ... INTO OUTFILE和LOAD DATA INFILE的用法

    昨天关于理论部分的分享链接：http://bbs.chinaunix.net/thread-4185186-1-1.html


1、SELECT ... INTO FILE，用实验结果演示下FIELDS项下面ENCLOSED BY加与不加OPTIONALLY选项的区别


mysql> select * from t1;                                                                  
+------+-------+-----------+------+
| id   | name  | city      | age  |
+------+-------+-----------+------+
|    1 | Alex  | Beijing   |   24 |
|    2 | Blue  | Shanghai  |   22 |
|    3 | Carvi | Guangzhou |   32 |
|    4 | Davy  | Shenzhen  |   11 |
+------+-------+-----------+------+
4 rows in set (0.00 sec)


mysql> select * into outfile '/tmp/t1.txt'
    -> fields terminated by ',' enclosed by '"'
    -> lines terminated by '\n'
    -> from t1;
Query OK, 4 rows affected (0.00 sec)


[root@gusha tmp]# cat t1.txt
"1","Alex","Beijing","24"
"2","Blue","Shanghai","22"
"3","Carvi","Guangzhou","32"
"4","Davy","Shenzhen","11"


mysql> select * into outfile '/tmp/t11.txt'
    -> fields terminated by ',' optionally enclosed by '"'
    -> lines terminated by '\n'
    -> from t1;
Query OK, 4 rows affected (0.06 sec)


[root@gusha tmp]# cat t11.txt
1,"Alex","Beijing",24
2,"Blue","Shanghai",22
3,"Carvi","Guangzhou",32
4,"Davy","Shenzhen",11

看到了吧，没有加OPTIONALLY之前，会对每一个字段都加上双引号，加上OPTIONALLY选项后，有选择行的加，对于数字类型的，就没有加双引号。




2、如果没有指定FIELDS或者LINES，则默认是：
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''


//把t1表里的数据导入到t12.txt里，不指定任何参数
mysql> select * into outfile '/tmp/t12.txt' from t1;
Query OK, 4 rows affected (0.00 sec)
//从导出结果可以看到，字段之间是用tab分割的
[root@gusha tmp]# cat t12.txt
1
Alex
Beijing
24
2
Blue
Shanghai
22
3
Carvi
Guangzhou
32
4
Davy
Shenzhen
11
//我们再创建一个和t1表结构一样的空表t2，然后把t12.txt里的文件导入到t2表中，验证：
mysql> create table t2 as select * from t1 where 0=1;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


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


mysql> load data infile '/tmp/t12.txt'
    -> into table t2
    -> fields terminated by '\t' enclosed by '' escaped by '\\'
    -> lines terminated by '\n' starting by '';
Query OK, 4 rows affected (0.03 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t2;
+------+-------+-----------+------+
| id   | name  | city      | age  |
+------+-------+-----------+------+
|    1 | Alex  | Beijing   |   24 |
|    2 | Blue  | Shanghai  |   22 |
|    3 | Carvi | Guangzhou |   32 |
|    4 | Davy  | Shenzhen  |   11 |
+------+-------+-----------+------+
4 rows in set (0.00 sec)


数据正常导入进去了，从侧面证明了，如果没有指定FIELDS或者LINES，则默认是：
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
其实对于上面的t12.txt里的内容，我们直接可以这样导入：load data infile '/tmp/t12.txt';




3、我们可以使用LOAD DATA有选择性的往表的某些特定列里导入数据，也可以再导入的过程中对数据进行一些运算。


[root@gusha tmp]# cat t1.txt
"1","Alex","24"
"2","Blue","22"
"3","Carvi","32"
"4","Davy","11"
mysql> create table t3 as select * from t1 where 0=1;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> load data infile '/tmp/t1.txt'
    -> into table t3
    -> fields terminated by ',' enclosed by '"'
    -> lines terminated by '\n'
    -> (id,name,age);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t3;
+------+-------+------+------+
| id   | name  | city | age  |
+------+-------+------+------+
|    1 | Alex  | NULL |   24 |
|    2 | Blue  | NULL |   22 |
|    3 | Carvi | NULL |   32 |
|    4 | Davy  | NULL |   11 |
+------+-------+------+------+
4 rows in set (0.00 sec)


我们可以再LOAD DATA语句最后面加上列的信息，从而指定往哪些列里导入数据。


接下来演示再导入数据的过程中通过变量来对导入的数据进行运算：


mysql> truncate t3;
Query OK, 0 rows affected (0.11 sec)


mysql> load data infile '/tmp/t1.txt'
    -> into table t3
    -> fields terminated by ',' enclosed by '"'
    -> lines terminated by '\n'
    -> (id,name,@v1)
    -> set age=@v1+1;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t3;
+------+-------+------+------+
| id   | name  | city | age  |
+------+-------+------+------+
|    1 | Alex  | NULL |   25 |
|    2 | Blue  | NULL |   23 |
|    3 | Carvi | NULL |   33 |
|    4 | Davy  | NULL |   12 |
+------+-------+------+------+
4 rows in set (0.00 sec)




4、STARTING BY参数决定从每行的哪个字段开始导入数据：


[root@gusha tmp]# cat t11.txt
wyzc1,"Alex","Beijing",24
Welcome to wyzc2,"Blue","Shanghai",22
3,"Carvi","Guangzhou",32
wyzc4,"Davy","Shenzhen",11


mysql> load data infile '/tmp/t11.txt'
    -> into table t3
    -> fields terminated by ',' enclosed by '"'
    -> lines terminated by '\n' starting by 'wyzc';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t3;
+------+------+----------+------+
| id   | name | city     | age  |
+------+------+----------+------+
|    1 | Alex | Beijing  |   24 |
|    2 | Blue | Shanghai |   22 |
|    4 | Davy | Shenzhen |   11 |
+------+------+----------+------+
3 rows in set (0.00 sec)


从实验结果中，我们看到，id为1、2、4的行全部导入t3表了，而id=3的行在t11.txt里面没有wyzc字段，因此没有导入到t3表中。


更多MySQL学习文章分享，关注我哦

renxiao2003

原来Select还可以这么用。