Mysql Chinese garbled characters and how to export SQL statements and Excel Problems

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.

1 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:
 
1./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 current databases and 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 spidproductname SDB Catalog>/Users/ethan/ desktop/Catalog. SQL
 
Several parameters are used, which are briefly described as follows:
 
-T:It is 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:Set the default character set. Because both my database and table are set to utf8 encoding format, if this option is not set, the exported Chinese characters are garbled. The strange thing is that they are in the official instructions, the default value of this option is utf8.
 
Export the entire database:
 
/Usr/local/mysql/bin/mysqldump-uroot-p123456-hlocalhost-t -- extended-insert = false -- default-character-set = utf8 spidproductname SDB>/Users/ethan/Desktop /spidproductname SDB. 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 spidbzsdb </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 ~~~
 
 
 
 
Iv. Summary.
 
It seems that many leaders like to add a summary to their reports. Even though they don't understand how to get back, they feel very bad. So I also added a summary: Chinese garbled characters are really tough. I 've struggled with you many times over the years. Well, it's done.