MySQL中SELECT ... INTO的用法

MySQL中SELECT... INTO的用法

官方文档表述：

1.SELECT... INTO var_list selects column values and stores them intovariables.

2.SELECT... INTO OUTFILE writes the selected rows to a file. Column andline terminators can be specified to produce a specific outputformat.

3.SELECT... INTO DUMPFILE writes a single row to a file without anyformatting.

4.mysql –e “SELECT .. FROM” >FILENAME

>第一种测试：

mysql>select * from dx_tt;

+----+------+

| id| name |

+----+------+

| 1 | lyn |

| 2 | moon |

+----+------+

2rows in set (0.00 sec)

mysql>select id,name INTO @x,@y from dx_tt limit 1;--这里必须限制一行否则报如下错误：

mysql>select id,name INTO @x,@y from dx_tt;

ERROR1172 (42000): Result consisted of more than one row

QueryOK, 1 row affected (0.00 sec)

mysql>select @x,@y;

+------+------+

|@x   |@y  |

+------+------+

|1   | lyn |

+------+------+

1 rowin set (0.00 sec)

>第2种测试：

mysql>select id,name INTOOUTFILE 'D:\mysql_dir\\dx_tt3.txt'

->fieldsterminatedby ','  --字段的结束符

->optionallyenclosedby '"' –-字符串的结束符

   -> linesterminatedby '\n' --行的结束符

   -> from dx_tt;

QueryOK, 2 rows affected (0.03 sec)

文件内容如下：

D:\mysql_dir>catdx_tt3.txt

1,"lyn"

2,"moon"

>第3种测试：

mysql>show tables;

+----------------+

|Tables_in_mydb |

+----------------+

|dx_lob        |

|dx_t          |

|dx_tt         |

|dx_users      |

+----------------+

4rows in set (0.00 sec)

mysql>drop table dx_lob;

QueryOK, 0 rows affected (0.42 sec)

mysql>create table dx_lob(id int not null primary key,pigblob);

QueryOK, 0 rows affected (0.14 sec)

mysql>insert into dx_lob values(1,'helloworld');

QueryOK, 1 row affected (0.08 sec)

mysql>insert into dx_lob values(2,'李德华'),(3,'卖空杰克逊');

QueryOK, 2 rows affected (0.06 sec)

Records:2 Duplicates: 0  Warnings:0

mysql>select * from dx_tt;

+----+------+

| id| name |

+----+------+

| 1 | lyn |

| 2 | moon |

+----+------+

2rows in set (0.00 sec)

 

mysql>select * from dx_lob;

+----+------------+

| id| pig       |

+----+------------+

| 1 | helloworld |

| 2 | 李德华         |

| 3 | 卖空杰克逊       |

+----+------------+

3rows in set (0.00 sec)

 

mysql>select pig INTO DUMPFILE 'D:\mysql_dir\\dx_lob1.dmp'

   -> fields terminated by ','

   -> optionally enclosed by '"'

   -> lines terminated by '\n'

   -> from dx_lob;

ERROR1064 (42000): You have an error in your SQL syntax; check themanual that corresponds to your MySQL server version for the rightsyntax to use near 'DUMPFILE'D:\mysql_dir\\dx_lob1.dmp'

fieldsterminated by ','

optionallyenclose' at line 1

mysql>select pig INTO DUMPFILE 'D:\mysql_dir\\dx_lob3.dmp'

   -> from dx_lob;

ERROR1172 (42000): Result consisted of more than one row

这也就说明INTODUMPFILE写入文件是不带任何格式的，而且只能写入一行

官方解释：

If youuse INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only onerow into the file, without any column or line termination andwithout performing any escape processing. This is useful if youwant to store a BLOB value ina file.

mysql>select pig INTO DUMPFILE 'D:\mysql_dir\\dx_lob1.dmp'

   -> from dx_lob limit 1;

QueryOK, 1 row affected (0.00 sec)

文件内容如下：

D:\mysql_dir>catdx_lob1.dmp

helloworld

>第4种测试：

将数据库mydb中的表dx_tt数据导入文件dx_tt2.txt

C:\>mysql-ulyn -p123456 mydb -e "select id,name from dx_tt">D:\mysql_dir\dx_tt2.txt

内容如下：

D:\mysql_dir>catdx_tt2.txt

id     name

1      lyn

2      moon