免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2850 | 回复: 1

MySQL SELECT ... INTO OUTFILE和LOAD DATA INFILE用法(二) [复制链接]

论坛徽章:
3
数据库技术版块每日发帖之星
日期:2015-06-25 22:20:00数据库技术版块每日发帖之星
日期:2015-08-06 06:20:00数据库技术版块每日发帖之星
日期:2015-08-28 06:20:00
发表于 2015-08-11 17:37 |显示全部楼层
昨天分享了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学习文章分享,关注我哦
api.png

论坛徽章:
59
2015七夕节徽章
日期:2015-08-24 11:17:25ChinaUnix专家徽章
日期:2015-07-20 09:19:30每周论坛发贴之星
日期:2015-07-20 09:19:42ChinaUnix元老
日期:2015-07-20 11:04:38荣誉版主
日期:2015-07-20 11:05:19巳蛇
日期:2015-07-20 11:05:26CU十二周年纪念徽章
日期:2015-07-20 11:05:27IT运维版块每日发帖之星
日期:2015-07-20 11:05:34操作系统版块每日发帖之星
日期:2015-07-20 11:05:36程序设计版块每日发帖之星
日期:2015-07-20 11:05:40数据库技术版块每日发帖之星
日期:2015-07-20 11:05:432015年辞旧岁徽章
日期:2015-07-20 11:05:44
发表于 2015-08-12 13:40 |显示全部楼层
原来Select还可以这么用。
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP