MySQL中导出CSV格式数据 | Java处理CSV数据_mysql 导出csv

1. 导出不带表头的CSV数据

SELECT dataid, recordfilename INTO OUTFILE '/tmp/uk_callcenter_event3.csv' 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
FROM table_name
WHERE createtime >= '2024-03-27 22:00:00' AND createtime <= '2024-04-29 23:59:59' AND timehour >= '22:00:00' AND timehour <= '23:59:59'
order by createtime DESC;
1
2
3
4
5
6

解释：

FIELDS TERMINATED BY ',' # 是字段分割，即每个导出的字段值通过这个指定的符号分割
LINES TERMINATED BY '\n' # 是一行数据分割
1
2

2. 导出带表头的CSV数据

SELECT 'id', 'starttime', 'discalled', 'discaller', 'dataid', 'language', 'idno', 'serviceid'
UNION ALL
SELECT ID, starttime, discalled, DISCALLER, dataid, language, idno, serviceid
INTO OUTFILE '/tmp/uk_callcenter_event1.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name
WHERE createtime >= '2024-03-27 22:00:00' AND createtime <= '2024-04-29 23:59:59' AND timehour >= '22:00:00' AND timehour <= '23:59:59'
ORDER BY createtime DESC;
1
2
3
4
5
6
7
8
9
10

3. Java中如何处理CSV数据

可以使用Hutool工具类中的方法处理CSV数据。

public static void main(String[] args) {
    CsvReader reader = CsvUtil.getReader(); // 加载 Csv 字符处理对象
	reader.setFieldSeparator(','); // 设置字段分割符，默认就是逗号
	List<TestBean> result = reader.read(ResourceUtil.getUtf8Reader("./your.csv"), TestBean.class); // 把 CSV 数据映射到 JavaBean 对象中
}

@Data
private class TestBean {
    private String dataid;
    private String recordfilename;
}
1
2
3
4
5
6
7
8
9
10
11

更多内容欢迎访问我的博客。