Mysql中文乱码以及导出为sql语句和Excel问题解决方法[图文]_MySQL

bitsCN.com

一、导出数据。

先说明一下自己的环境：Mac OS X 10.8.3, MySQL Community Server 5.6.10, MySQL Workbench 5.2.47。

我想把本机数据库内的数据迁移到另一台机器上，于是使用Workbench中自带的import/export功能，其实就是调用mysqldump。不幸的是，出现了版本不一致的错误。

错误没治了，最终找到解决方案，可以指定mysql的mysqldump，路径为：/usr/local/mysql/bin/mysqldump，这样是把数据导出为sql语句的insert语句。

由于需要是把数据导出为excel，所以通过mysql控制台使用select语句把数据导出到excel文件中。

下面先介绍怎么导出为excel文件，然后介绍怎么导出为insert语句。

1、通过终端操作。

1 cd /usr/local/mysql/bin/<p></p> <p>2、到达bin目录后，可以ls -l命令看看当前目录有哪些程序可以用，这里先用mysql，命令格式为：</p> <p>mysql -h主机IP -u用户名 -p密码</p> <p>如：</p> <p></p><pre class="brush:php;toolbar:false">1 ./mysql -hlocalhost -uroot -p123456<p></p> <p>注意前面加的"./"。</p> <p>这时就进入mysql命令控制台，终端上显示为：</p> <p><img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L112406250-52419.jpg" class="lazy" alt=""></p> <p>3、然后通过show databases命令查看当前的所有数据库，使用use命令选择进入某个数据库，<strong>注意每个命令都要以英文分号“;”结束。</strong></p> <p><strong><img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L11244DP-B4Z.jpg" class="lazy" alt=""></strong></p> <p>4、使用sql语句导出需要的数据，sql语句不限于单个表的查询。由于我的数据库编码是utf8格式，而office默认的编码则是gb2312，所以当某个字段中包含中文时，导出到excel后，中文内容是会乱码的，此时需要convert转换编码，具体使用方式：</p> <p><img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L112493K0-L115.jpg" class="lazy" alt=""></p> <p>我试着把文件保存到桌面，但始终提示没有权限，应该是和用户有关吧，无视了。当使用“./”这个路径保存时，实际是保存到了/usr/local/mysql/data下面。打开看看，哟西，不乱码了。</p> <p>5、下面是把数据导出为sql的insert语句。</p> <p>使用mysqldump命令，可以指定是单个表还是整个数据库导出。</p> <p>打开终端，定位到/usr/local/mysql/bin，使用这个目录下的mysqldump。</p> <p><strong>导出单个表：</strong></p> <p>命令格式为：</p> <p class="p1">mysqldump -u用户名 -p密码 -h主机地址 数据库名 表名 > 导出文件存储路径</p> <p class="p1">例如：</p> <p></p><pre class="brush:php;toolbar:false">/usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost -t --extended-insert=false --default-character-set=utf8 SpiderBBSDB Catalog > /Users/ethan/Desktop/Catalog.sql<p></p> <p>其中用到了几个参数，简单说明一下：</p> <p><strong>-t：</strong>等同于--no-create-info，只导出数据，而不添加CREATE TABLE 语句。默认导出的文件中也有create table语句。</p> <p><strong>--extended-insert：</strong>使用具有多个VALUES列的INSERT语法，也就是传说中一次插入多条数据的INSERT句式。这样使导出文件更小，并加速导入时的速度，但是有可能sql语句会有长度限制，所以我并不推荐此种方式，比如我某个表中有500W条数据，难保能用一条insert语句可以执行完毕。此选项默认为打开状态，把他置为false，就是一条数据一个insert语句了。</p> <p><strong>--default-character-set：</strong>设置默认字符集，由于我的数据库和表均是设定为utf8编码格式，当不设置此选项时，导出的中文是乱码，奇怪的是官方说明中，说这个选项的默认值是utf8，表示不解。</p> <p><strong>导出整个数据库：</strong></p> <p></p><pre class="brush:php;toolbar:false">/usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost -t --extended-insert=false --default-character-set=utf8 SpiderBBSDB > /Users/ethan/Desktop/SpiderBBSDB.sql<p></p> <p>二、导入数据。 </p> <p>有导出就有导入。上面第5步导出的sql文件，可以直接在mysql workbench中执行，也可以使用mysqldump导入，这里说明一下如何使用mysqldump导入：</p> /usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost --default-character-set=utf8 SpiderBBSDB 三、关于java连接mysql写入中文乱码。  <p>关于这个中文乱码问题，着实折腾了我好久好久。一开始就百度谷歌bing，网上大多复制粘贴的答案，在这里记录一下自己的情况，希望同路人不再走弯路。</p> <p>其实我的修改很简单，把数据库的编码改为utf-8，在新建表时，把表的默认编码也改为utf-8，就可以了。就这么个小小的改动，让我足足折腾了一个通宵，表示有解决问题强迫症，问题不解决真的睡不着，唉~~~</p> <p><img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L112535940-WL9.jpg" class="lazy" alt=""></p> <p><img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L1125F310-92S6.jpg" class="lazy" alt=""></p> <p><img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L11261250-105O0.jpg" class="lazy" alt=""></p> <p><strong>四、总结。 </strong></p> <p>似乎很多领导做报告都喜欢加个总结，说上一堆废话，虽然回回都听不懂，但感觉很厉害的样子。于是我也加一个总结：中文乱码真特么折腾人，这些年跟你斗争了好多回了，好了，总结完毕。</p>

bitsCN.com