> For the complete documentation index, see [llms.txt](https://close.gitbook.io/yun-wei-bi-ji/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://close.gitbook.io/yun-wei-bi-ji/centos/mysql/9mysql-shu-ju-dao-chu-csv-ge-shi.md).

# 9、MySQL数据导出csv格式

**查询**

```
SELECT * FROM table001 into outfile '/tmp/test.csv' 
CHARACTER SET gbk 
fields terminated by ',' 
optionally enclosed by '\"';
```

**方法二：Mysqldump（本地导出）**

> 由于mysqldump的实现方法，其根本还是into outfile，故导出的文件也只能到本地

```bash
mysqldump -h 172.16.81.236 -uusername -ppassword 
-t -T/tmp/waring.csv nms_db table001  
--fields-enclosed-by=\" --fields-terminated-by=,
```

**方法三：mysql（远程导出）推荐**

> 推荐使用这个方法： mysql和sed结合的方法，将查出的文件内容使用sed进行相应的转化， 提升时间效率还能远程操作

```bash
mysql -uusername -ppassword -h 172.16.81.236 
–D my_db  
--default-character-set=gbk  
-e 'select * from server_warning_unrepaired'  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/file.csv
```

**数据导入**

```
load data infile 'C:\\Users\\UserName\\Desktop\\test.csv' 
into table `table` fields terminated by ',' 
optionally enclosed by '"' 
escaped by '"' 
lines terminated by '\n';
```

**mysql导入csv**

```
select * from `table`
load data infile 'C:\\Users\\UserName\\Desktop\\test.csv' 
fields terminated by ',' optionally enclosed by '"' escaped by '"' 
lines terminated by '\n';
```

如果乱码，可用相关编辑器打开.csv文件，另存为utf-8的csv