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/imghw/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/imghw/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/imghw/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/imghw/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L112535940-WL9.jpg" class="lazy" alt=""></p> <p><img src="/static/imghw/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L1125F310-92S6.jpg" class="lazy" alt=""></p> <p><img src="/static/imghw/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405L11261250-105O0.jpg" class="lazy" alt=""></p> <p><strong>四、总结。 </strong></p> <p>似乎很多领导做报告都喜欢加个总结，说上一堆废话，虽然回回都听不懂，但感觉很厉害的样子。于是我也加一个总结：中文乱码真特么折腾人，这些年跟你斗争了好多回了，好了，总结完毕。</p>
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1359
52
How to export the queried data in navicat
Apr 24, 2024 am 04:15 AM
Export query results in Navicat: Execute query. Right-click the query results and select Export Data. Select the export format as needed: CSV: Field separator is comma. Excel: Includes table headers, using Excel format. SQL script: Contains SQL statements used to recreate query results. Select export options (such as encoding, line breaks). Select the export location and file name. Click "Export" to start the export.
How does Hibernate implement polymorphic mapping?
Apr 17, 2024 pm 12:09 PM
Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.
iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos
Jul 18, 2024 am 05:48 AM
Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps
Detailed tutorial on establishing a database connection using MySQLi in PHP
Jun 04, 2024 pm 01:42 PM
How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())
How to handle database connection errors in PHP
Jun 05, 2024 pm 02:16 PM
To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
How to use explain in oracle
May 03, 2024 am 12:06 AM
The EXPLAIN command in Oracle is used to analyze the execution plan of a SQL statement. The method of use is to add the EXPLAIN keyword before the SQL statement. EXPLAIN results contain information such as ID, operator type, row count estimate, cost estimate, output row count estimate, access predicates, and filter predicates, which can be used to optimize query performance, identify costly operators, and tables that may benefit from optimization techniques.
How to write auto-increment in mysql
Apr 27, 2024 am 01:54 AM
Auto-increment in MySQL is a mechanism that automatically generates a unique number sequence, often used for primary keys and unique index fields. To set auto-increment, you need to specify the AUTO_INCREMENT attribute when creating the table, for example: CREATE TABLE my_table (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL). The advantages of auto-increment include: simplifying primary key generation, improving insertion performance, and ensuring uniqueness. However, fields with auto-increment enabled cannot be set to other values. The auto-increment value cannot be predicted before insertion. Manually specifying the value of an auto-increment field may conflict with the automatically generated sequence. Deleting or updating the value of an auto-increment field may affect
How to use database callback functions in Golang?
Jun 03, 2024 pm 02:20 PM
Using the database callback function in Golang can achieve: executing custom code after the specified database operation is completed. Add custom behavior through separate functions without writing additional code. Callback functions are available for insert, update, delete, and query operations. You must use the sql.Exec, sql.QueryRow, or sql.Query function to use the callback function.
