MySQL Chinese garbled and export SQL statements and Excel related solutions

These days based on Heritrix wrote a crawler, used to MySQL, in the import of exported data, encountered some garbled problems, and finally solved, record, for viewing.

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.