mysql篇 — 导出mysql数据为excel格式显示乱码

最新推荐文章于 2024-08-18 19:52:10 发布

郝似星河

最新推荐文章于 2024-08-18 19:52:10 发布

阅读量2.3k

点赞数

分类专栏：数据库篇

本文链接：https://blog.csdn.net/weixin_44879253/article/details/105989948

版权

数据库篇专栏收录该内容

26 篇文章 0 订阅

订阅专栏

查看导出文件的编码为fileencoding=utf-16le 【方法参考补充1】

linux默认的是utf8编码，而windows是gbk编码，所以会出现上面的乱码问题。

【我的操作系统win7，win版的mysql5.7.29】想来linux下比较好处理这类问题吧

mysql导出数据

在mysql命令行的操作

起别名执行以下语句

mysql> select table_name as '数据表', TABLE_COMMENT as '数据表中文',concat(truncate(data_length/1024, 0),'KB') as '数据容量', table_rows as '数据量',create_time as '数据创建', update_time as '数据更新' from information_schema.tables where table_schema = 'qiu' group by table_name order by data_length desc into outfile  'F:/qiu.txt'; 
Query OK, 24 rows affected (0.04 sec)

缺陷：命令行中的as ‘*’，字段并不会导入导出的数据表中

如下：在界面显示的信息

mysql> select table_name as '数据表', TABLE_COMMENT as '数据表中文', concat(truncate(data_length/1024, 0),'KB') as '数据容量', table_rows as '数据量',create_time as '数据创建', update_time as '数据更新' from information_schema.tables where table_schema = 'qiu' group by table_name order by data_length desc;
+-------------------------+-----------------+--------------+-----------+---------------------+---------------------+
| 数据表                  | 数据表中文      | 数据容量     | 数据量    | 数据创建            | 数据更新            |
+-------------------------+-----------------+--------------+-----------+---------------------+---------------------+
| sequence                |                 | 16KB         |         0 | 2020-05-07 10:04:00 | NULL                |
| person_tbl01            |                 | 16KB         |         0 | 2020-05-07 10:37:00 | NULL                |
| apps                    |                 | 16KB         |         3 | 2020-04-30 20:42:52 | NULL                |
| xiaohua                 |                 | 16KB         |         0 | 2020-04-30 20:51:07 | NULL                |
| runoob_tbl              |                 | 16KB         |         5 | 2020-04-30 00:39:27 | NULL                |
| xiaoxvx                 |                 | 16KB         |         0 | 2020-04-30 21:08:36 | NULL                |
| insect0                 |                 | 16KB         |         0 | 2020-05-07 09:58:37 | NULL                |
| ss                      |                 | 16KB         |         0 | 2020-04-30 01:07:48 | NULL                |
| person_tbl011           |                 | 16KB         |         0 | 2020-05-07 10:37:32 | NULL                |
| clone_tbl               |                 | 16KB         |         5 | 2020-05-06 17:55:16 | NULL                |
| runoob_test_tbl         |                 | 16KB         |         4 | 2020-04-30 01:08:59 | NULL                |
| xiaoxx                  |                 | 16KB         |         0 | 2020-04-30 21:35:06 | NULL                |
| person_tbl              |                 | 16KB         |         1 | 2020-05-07 11:41:21 | NULL                |
| tcount_tbl              |                 | 16KB         |         3 | 2020-04-30 00:39:27 | NULL                |
| person_tbl09            |                 | 16KB         |         0 | 2020-05-07 10:29:01 | NULL                |
| xiaox                   |                 | 16KB         |         0 | 2020-04-30 21:07:18 | NULL                |
| employee_tbl            |                 | 16KB         |         6 | 2020-04-30 00:11:48 | NULL                |
| runoob_transaction_test |                 | 16KB         |         2 | 2020-04-30 14:20:35 | NULL                |
| person_tbl0             |                 | 16KB         |         0 | 2020-05-07 10:28:15 | NULL                |
| websites                |                 | 16KB         |         6 | 2020-04-29 23:17:10 | NULL                |
| qiu_tbl2                |                 | 16KB         |         6 | 2020-04-29 21:48:19 | NULL                |
| xiaoxiao                |                 | 16KB         |         0 | 2020-04-30 21:02:04 | NULL                |
| insect                  |                 | 16KB         |         3 | 2020-05-07 09:59:13 | NULL                |
| alter_tbl               |                 | 0KB          |         0 | 2020-04-30 14:58:11 | 2020-04-30 14:58:11 |
+-------------------------+-----------------+--------------+-----------+---------------------+---------------------+
24 rows in set (0.05 sec)

导出的数据如下：中文乱码（起的中文别名乱码）

不起别名执行以下语句

echo "select table_name,TABLE_COMMENT,concat(truncate(data_length/1024,0),'KB'),table_rows,create_time,update_time from information_schema.tables where table_schema = 'qiu' group by table_name order by data_length desc;"|mysql -uroot -p >F:/00.xls

导出的数据如下

在mysql命令行，使用该命令导出的数据

mysql> select table_name as '数据表', TABLE_COMMENT as '数据表中文', concat(truncate(data_length/1024, 0),'KB') as '数据容量', table_rows as '数据量',create_time as '数据创建', update_time as '数据更新' from information_schema.tables where table_schema = 'qiu' group by table_name order by data_length desc into outfile 'F:/qiu.txt';
Query OK, 24 rows affected (0.04 sec)

如下图：没有中文一行，需要手动添加

在shell命令行的操作

PS C:\Users\Administrator> echo "select table_name as '数据表', TABLE_COMMENT as '数据表中文', concat(truncate(data_length/1024, 0),'KB') as '数据容量', table_rows as '数据量',create_time as '数据创建', update_time as '数据更新' from information_schema.tables where table_schema = 'qiu' group by table_name order by data_length desc;" |mysql -uroot -p >F:/qiu000.xls
Enter password: ******

缺陷：如图，中文显示乱码

解决办法：手动添加中文

后来发现是win下powershell终端输入中文显示乱码

echo "select table_name as '数据表', TABLE_COMMENT as '数据表中文', concat(truncate(data_length/1024, 0),'KB') as '数据容量', table_rows as '数据量',create_time as '数据创建', update_time as '数据更新' from information_schema.tables where table_schema = 'qiu' group by table_name order by data_length desc;" |mysql -uroot -p123123

查看终端编码

解决方法：

其实嘛一看就知道是encoding的问题，英语为默认语言的环境里，PowerShell默认的Encoding是AscII

最简单的方法就是改用PowerShell ISE，它可以显示非英语字符。在操作SharePoint数据的时候只要在启动ISE的时候运行一下下面的语句就可以，相比于Management Console方便很多。添加Sharepoint管理单元

Add-PSSnapin Microsoft.SharePoint.PowerShell

另外一个解决方法是把Encoding改为UTF8，运行下面的语句就可以了。

$OutputEncoding = New-Object -typename System.Text.UTF8Encoding

但是，我在powershell ISE执行以下命令报错

Add-PSSnapin : 没有为 Windows PowerShell 版本 2 注册管理单元。

此处参考了：启动Powershell控制台的方式有何不同？依旧没有能解决我的问题，我分别用32位和64位启动powershell ISE和powershell都是报以上一样的错误，找遍了整个百度依旧没有能找到解决办法o(╥﹏╥)o；

虽然如此，此时有俩种折中的办法【此为博主猜想中的俩种方向】：

一、

在本地的虚拟机中启动你的linux操作系统的服务器，用xshell连接你的linux服务器，在xshell端导出本地数据，即可解决此问题，【前提的你的linux服务器需要有mysql数据库】

二、

在数据库里执行导出命令，进过测试可以显示中文，但是有一个难点：导出的数据不会带中文那一行

方法：在shell命令行转换编码

$ sudo iconv -futf-16le -tgb2312 -oqiu0.xls qiu.xls
iconv -futf-16le -tutf8 -oqiu0.xls qiu.xls

提示：以下表示失败

iconv: illegal input sequence at position 0

使用以上此方法也没能解决我的问题o(╥﹏╥)o

补充1：

iconv命令

用法: iconv [OPTION...] [FILE...]
将给定文件的编码从一种编码转换为另一种编码。

输入/输出格式规范：
  -f, --from-code=NAME      原文编码
  -t, --to-code=NAME         输出编码

信息
  -l, --list                 列出所有已知的编码字符集

 输出控制：
  -c                         从输出中省略无效字符
  -o, --output=FILE          输出文件

  -s, --silent               抑制警告
      --verbose              打印进度信息

  -?, --help                提供此帮助列表
      --usage                发出简短的使用信息
  -V, --version             打印程序版本

长选项的强制或可选参数也是强制或可选的任何相应的空头期权。

有关错误报告说明，请参阅：
<http://www.gnu.org/software/libc/bugs.html>.

补充2：

查看文件编码
在Linux中查看文件编码可以通过以下几种方式：
1.在Vim中可以直接查看文件编码
:set fileencoding
即可显示文件编码格式。

如果你只是想查看其它编码格式的文件或者想解决用Vim查看文件乱码的问题，那么你可以在
~/.vimrc 文件中添加以下内容：

set encoding=utf-8 fileencodings=ucs-bom,utf-8,cp936

这样，就可以让vim自动识别文件编码（可以自动识别UTF-8或者GBK编码的文件），其实就是依照 fileencodings提供的编码列表尝试，如果没有找到合适的编码，就用latin-1(ASCII)编码打开。

使用以上方法依旧没有能解决我的问题o(╥﹏╥)o

使用第三方工具：Navicat for MySQL

在查询编辑器中运行查询语句，直接导出为excel格式【亲测，大力推荐】

参考：

enca命令下载地址

enca进行字符集转码

powershell解决中文乱码

linux下查看文件编码及修改编码

Mysql查询结果导出为Excel的几种方法

添加powershell扩展模块可能存在的解决办法：

无法在Powershell 2.0命令行中为SQL Server Cmdlet添加一个管理单元

在SQLPS之外使用SQL Server 2008 R2 PowerShell扩展的问题

郝似星河

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录