mysql将查询结果导出到Excel表格

如何将mysql查询的结果导出成Excel表格呢？1.echo "select a.book_id,b.name as book_name,a.name from newbook.sy_book_chapter a INNER JOIN newbook.sy_book b ON a.book_id = b.id GROUP BY a.name,a.book_id HAVING count(*)&g

缘来侍你

3486人浏览 · 2021-07-02 17:22:54

缘来侍你 · 2021-07-02 17:22:54 发布

如何将mysql查询的结果导出成Excel表格呢？

echo "select a.book_id,b.name as book_name,a.name from newbook.sy_book_chapter a INNER JOIN newbook.sy_book b ON a.book_id = b.id GROUP BY a.name,a.book_id HAVING count(*)>1 order by a.book_id" | mysql -uroot -p > /home/wwwroot/book_chapter_exist.xls

echo 查询语句 | mysql -u用户名 -p > 导出路径及文件名

2. 回车输入数据库密码（不能直接带上密码，会报错）

3. 此时在你指定的路径下就可以看到导出的Excel文件了，但文件如果出现中文会乱码

4. 解决Excel乱码问题

选择文件，右键-用记事本打开

然后另存为，选择ANSI保存

然后用Excel打开乱码就解决了

示例：

echo "select id,case when seed=1 then '源1' when seed=2 then '源2' when seed=3 then '二者共有' when seed='' then '源3' end as '授权商',name as '书名',author as '作者',if(gender_type=1,'男频','女频') as '频道',substring_index(substring_index(category, ',', 2) , ',', -1) as '分类',if(over_type=1,'连载','完结') as '状态',word_number as '字数',if(free_type=2,'收费','免费') as '是否付费' from newbook.sy_book where status=1" | mysql -uroot -p > /home/wwwroot/book.xls

效果如下图所示具体if和case的使用可参考另一篇文章