MySql数据库导出csv

阳光穿透心脏的1/2处 2021-06-24 15:56 512阅读 0赞

**MySql数据库导出csv文件命令：**

mysql> select first\_name,last\_name,email from account into outfile 'e://output1.csv' fields terminated by ','optionally enclosed by ''lines terminated by '/n';

csv文件效果：

<table style="border:1px solid silver; border-collapse:collapse; word-break:break-word; color:rgb(75,75,75); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; font-size:13px; width:629px">      
 <tbody> 
  <tr style="height:15pt"> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; height:15pt; width:48pt"> sunny</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; width:48pt"> Grigoryan</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; width:136pt"> lovechoosesun@gmail.com</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; width:48pt"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; width:48pt"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; width:48pt"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; width:48pt"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; width:48pt"> &nbsp;</td> 
  </tr> 
  <tr style="height:15pt"> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; height:15pt"> Jon</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> Siegal</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> sun@riliantech.net</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
  </tr> 
  <tr style="height:15pt"> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; height:15pt"> Joe</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> Siegal</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> <a href="mailto:zhao@gmail.com" style="color:rgb(26,139,200); text-decoration:none" rel="nofollow">zhao@gmail.com</a></td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
  </tr> 
  <tr style="height:15pt"> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px; height:15pt"> alejandro</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> medina</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> <a href="mailto:wei@gmail.com" style="color:rgb(26,139,200); text-decoration:none" rel="nofollow">wei@gmail.com</a></td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
   <td style="color:rgb(69,69,69); font-family:Verdana,Geneva,Arial,Helvetica,sans-serif; border:1px solid silver; border-collapse:collapse; padding:3px"> &nbsp;</td> 
  </tr> 
 </tbody> 
</table>

**cvs文件导入MySql数据库命令: **  
mysql> load data local infile 'e://input1.csv' into table test1 fields termin  
ated by ','lines terminated by '/n'(first\_name,last\_name,email);  
Query OK, 1 row affected, 1 warning (0.00 sec)  
Records: 69  Deleted: 0  Skipped: 68  Warnings: 0  
  
mysql> select \* from test1;  
\+----+------------+-----------+--------------------------+  
| id | first\_name | last\_name | email                    |  
\+----+------------+-----------+--------------------------+  
 | 0 | sunny      | Grigoryan | lovechoosesun@gmail.com  
\+----+------------+-----------+--------------------------+  
  
**FIELDS TERMINATED BY ---- 字段终止字符   
OPTIONALLY ENCLOSED BY ---- 封套符   
LINES TERMINATED BY ---- 行终止符**

通过mysql客户端shell连接到服务器，选择使用的数据库，输入sql代码：

select \* from test\_info     
into outfile '/tmp/test.csv'     
fields terminated by ',' optionally enclosed by '"' escaped by '"'     
lines terminated by '\\r\\n';

里面最关键的部分就是格式参数

这个参数是根据RFC4180文档设置的，该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中详细描述了CSV格式，其要点包括：  
(1)字段之间以逗号分隔，数据行之间以\\r\\n分隔；

(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

通过执行上述代码，便可以将需要的数据以csv格式导出到执行的文件中。

另外，MySQL中导入CSV格式数据的sql代码如下:

load data infile '/tmp/test.csv'    
into table test\_info     
fields terminated by ','  optionally enclosed by '"' escaped by '"'    
lines terminated by '\\r\\n';

当csv文件中的每一行记录的列数小于数据库表时，以下语句将十分有用：

load data infile "/tmp/appleAppV2\_search\_day\_20110525.csv" into table apple\_search\_log fields terminated by ';' lines terminated by '/n' (apptypeId,keyword,searchTime,model) ;

蓝色字部分是亮点。如果数据表包含自增字段，例如自增的ID， 这个语句将十分有用。