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)
# 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)
# 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分割的
# 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: 0Duplicates: 0Warnings: 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: 4Deleted: 0Skipped: 0Warnings: 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有选择性的往表的某些特定列里导入数据，也可以再导入的过程中对数据进行一些运算。
# 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: 0Duplicates: 0Warnings: 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: 4Deleted: 0Skipped: 0Warnings: 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: 4Deleted: 0Skipped: 0Warnings: 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参数决定从每行的哪个字段开始导入数据：
# 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: 3Deleted: 0Skipped: 0Warnings: 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学习文章分享，关注我哦
原来Select还可以这么用。
页:
[1]