Mysql Chinese garbled and exported to SQL statements and Excel problem resolution [graphic]_mysql

first, export the data.

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

I want to migrate the data in the native database to another machine, so using the Import/export function in Workbench is to call mysqldump. Unfortunately, there was an inconsistent version of the error.

The error is not true, finally found the solution, you can specify the mysqldump of MySQL, the path is:/usr/local/mysql/bin/mysqldump, which is to export the data as an INSERT statement of the SQL statement.

Because you need to export data to Excel, use the SELECT statement from the MySQL console to export the data to an Excel file.

Here's how to export as an Excel file, and then explain how to export as an INSERT statement.

1, through the terminal operation.

1 cd/usr/local/mysql/bin/
 
2, to reach the bin directory, you can ls-l command to see what the current directory of the program can be used, here first with MySQL, the command format:
 
Mysql-h host Ip-u user name-p password
 
Such as:
 
1./mysql-hlocalhost-uroot-p123456
 
Notice the "./" in front.
 
Then enter the MySQL command console, the terminal appears as:
 
 
3, then through the show Databases command to view all the current database, using the use command to choose to enter a database, note that each command should be in English semicolon ";" End. 
 
 
4, use the SQL statement to export the required data, SQL statements are not limited to a single table query. Because my database encoding is UTF8 format, and Office default encoding is gb2312, so when a field contains Chinese, exported to Excel, Chinese content will be garbled, at this time need convert conversion encoding, the specific use of the way:
 
 
I tried to save the file to the desktop, but always prompted no permissions, should be related to the user, ignore the. When the "./" Path is saved, it is actually saved under/usr/local/mysql/data. Open Look, yo West, not garbled.
 
5. The following is an INSERT statement that exports data to SQL.
 
Using the mysqldump command, you can specify whether a single table or an entire database is exported.
 
Open the terminal, navigate to/usr/local/mysql/bin, and use the mysqldump in this directory.
 
To export a single table:
 
The command format is:
 
Mysqldump-u User name-p password-H host Address database name Table name > export File Store 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
 
It uses a few parameters, a simple explanation:
 
- T: equivalent to--no-create-info, export only data without adding a CREATE TABLE statement. The CREATE TABLE statement is also available in the default exported file.
 
--extended-insert: Insert syntax with multiple values columns, which is the legend of inserting more than one piece of data at a time. This makes the export file smaller and accelerates the speed of the import, but it is possible that the SQL statement has a length limit, so I don't recommend it, such as having 500W of data in one of my tables, which can be done with an INSERT statement. This option is turned on by default and set to False, which is an INSERT statement for the data.
 
--default-character-set: Set the default character set, because my database and table are set to UTF8 encoding format, when not set this option, the export of Chinese is garbled, strange is the official description, said this option is the default value is UTF8, expressed confusion.
 
To 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 
 
 
 Two, import data. The 
 
 has an import if it has an export. The SQL file exported in step 5th above can be executed directly in MySQL Workbench, or you can use mysqldump import, which explains how to use mysqldump import: 
 
     
     

      
      /usr/local/mysql/bin/ Mysqldump-uroot-p123456-hlocalhost--default-character-set=utf8 Spiderbbsdb </users/ethan/desktop/catalog.sql /div> 
      
      
 Third, about Java connection MySQL write Chinese garbled. 
 
      
      
 About this Chinese garbled problem, really toss me for a long time. Baidu Google at the beginning of Bing, most of the online copy and paste the answer, here to record their own situation, hoping that fellow travellers no longer detours. 
 
      
      
 In fact, my modification is very simple, the encoding of the database to Utf-8, in the new table, the table's default encoding also changed to Utf-8, you can. 
      
      
 
 
      
      
 
 
      
      
 strong> four, summary. 
 
      
      
 It seems that many leaders like to add a summary of the report, said a pile of nonsense, although the Hui can not understand, but feel very powerful appearance. So I also add a summary: Chinese garbled Genter to toss people, these years with you fight a lot of back, well, summed up.