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;
解释:
FIELDS TERMINATED BY ',' # 是字段分割,即每个导出的字段值通过这个指定的符号分割
LINES TERMINATED BY '\n' # 是一行数据分割
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;
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;
}
Q.E.D.