Solve MySQL Chinese garbled characters and version inconsistencies

1. Export data

First, describe your environment: Mac OS X 10.8.3, MySQL Community Server 5.6.10, and MySQL Workbench 5.2.47.

I want to migrate the data in the local database to another machine, so I used the import/export function in Workbench to call mysqldump. Unfortunately, a version inconsistency error occurs.

If the error persists, find the solution. You can specify the mysql mysqldump path:/usr/local/mysql/bin/mysqldump, this is an insert statement that exports data as an SQL statement.

To export data to excel, use the select statement on the mysql console to export data to an excel file.

The following describes how to export an excel file and how to export it as an insert statement.

1. operate on a terminal.

 
 

  
  
cd /usr/local/mysql/bin/ 
 
 

2. After arriving at the bin directory, you can run the ls-l command to check which programs can be used in the current directory. mysql is used first. The command format is as follows:

Mysql-h Host IP-u username-p Password

For example:

 
 

  
  
./mysql -hlocalhost -uroot -p123456 
 
 

Pay attention to the previously added "./".

Then, go to the mysql command console and the terminal displays:

3. Run the show databases command to view all the databases. use the use command to select a database. Note that each command must end with a semicolon.

4. Use SQL statements to export the required data. SQL statements are not limited to queries of a single table. Because my database encoding is in utf8 format, and the default office encoding is gb2312, when a field contains Chinese characters, the Chinese content will be garbled after being exported to excel, in this case, you need to convert the convert encoding. The specific usage is as follows:

I tried to save the file to the desktop, but I always indicated that I didn't have the permission. It should be related to the user. When the path "./" is used for saving, it is actually saved to/usr/local/mysql/data. Open it and check it out. It's not garbled.

5. The following is an insert statement that exports data as an SQL statement.

Use the mysqldump command to specify whether to export a single table or the entire database.

Open the terminal, locate/usr/local/mysql/bin, and use mysqldump in this directory.

Export a single table:

Command Format:

Mysqldump-u user name-p password-h host address database name Table Name> export file storage path

For example:

 
 

  
  
/usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost -t --extended-insert=false --default-character-set=utf8 SpiderBBSDB Catalog > /Users/ethan/Desktop/Catalog.sql 
 
 

Several parameters are used, which are briefly described as follows:

-T: equivalent to -- no-create-info. Only data is exported without the create table statement. The default exported file also contains the create table statement.

-- Extended-insert: Use the INSERT syntax with multiple VALUES columns, that is, the INSERT sentence that inserts multiple data records at a time. In this way, the exported file is smaller and the import speed is accelerated. However, it is possible that SQL statements have length restrictions. Therefore, I do not recommend this method. For example, a table contains million pieces of data, it is difficult to use an insert statement to complete execution. This option is enabled by default. Setting it to false is an insert statement for data.

-- Default-character-set: sets the default character set. Because both my database and table are set to utf8 encoding format, when this option is not set, the exported Chinese characters are garbled, it is strange that the default value of this option is utf8 in the official instructions, which indicates that it is puzzled.

Export the entire database:

 
 

  
  
/usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost -t --extended-insert=false 
  
  
 --default-character-set=utf8 SpiderBBSDB > /Users/ethan/Desktop/SpiderBBSDB.sql 
 
 

2. import data.

If there is an export, there will be an import. The SQL file exported in step 1 above can be directly executed in mysql workbench or imported using mysqldump. Here we will explain how to import using mysqldump:

/usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost --default-character-set=utf8 SpiderBBSDB < /Users/ethan/Desktop/Catalog.sql

3. garbled characters are written into mysql by connecting java.

The Chinese Garbled text has really been around for a long time. Baidu Google bing at the beginning. Most of the answers on the Internet are copied and pasted. Here, I will record my own situation and hope that fellow travelers will not make any detours.

In fact, my modification is very simple. I changed the database encoding to UTF-8. When creating a new table, I changed the default encoding of the table to UTF-8. With such a small change, I had to go through the whole night, indicating that I had problem-solving obsessive-compulsive disorder and that I could not sleep after the problem was not solved ~~~

Edit recommendations]

1. Wikipedia escaped from MySQL and supported open source databases
2. MariaDB has become a turning point in the fate of MySQL?
3. Rollback in case of a DELETE misoperation in MySQL
4. One-step migration from MySQL to Redis
5. Two ways to solve the problem of master-slave synchronization in mysql