MySQL Chinese garbled and exported as SQL statement and Excel problem solving
These days based on Heritrix wrote a crawler, used to MySQL, import and export data, encountered some garbled problems, 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 that comes with Workbench is actually called mysqldump. Unfortunately, there was a version inconsistency error.
Error hopeless, finally found a solution, you can specify the MySQL mysqldump, the path is:/usr/local/mysql/bin/mysqldump, this is the data export to SQL statement INSERT statement.
Because the need is to export the data to Excel, the MySQL console uses the SELECT statement to export the data to an Excel file.
The following first describes how to export to an Excel file, and then describes how to export it as an INSERT statement.
1, through the terminal operation.
1 CD/USR/LOCAL/MYSQL/BIN/2, after the arrival bin directory, you can ls-l command to see which programs in the current directory can be used, here first with MySQL, the command format is:
Mysql-h host Ip-u user name-p password
Such as:
1./mysql-hlocalhost-uroot-p123456 note the "./" in front of the Add.
The MySQL command console is then displayed on the terminal 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 to the English semicolon ";" End.
4. Use the SQL statement to export the required data, and the SQL statement is 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, when exported to Excel, Chinese content is garbled, you need to convert the conversion encoding, how to use:
I try to save the file to the desktop, but always prompt no permissions, should be related to the user, ignoring. When using the "./" This 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 it is a single table or an entire database export.
Open the terminal and navigate to/usr/local/mysql/bin, using 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 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 A few parameters are used to illustrate briefly:
-T: equivalent to--no-create-info, exporting only data without adding a CREATE TABLE statement. The CREATE TABLE statement is also available in the default exported file.
--extended-insert: Use the INSERT syntax with multiple values columns, which is the legend of the insert sentence that inserts more than one piece of data at a time. This makes the export file smaller and accelerates the import speed, but it is possible that the SQL statement will have a length limit, so I do not recommend this way, for example, I have 500W data in a table, and can be executed with an INSERT statement. This option is turned on by default and set to False, which is an INSERT statement for a single piece of data.
--default-character-set: Set the default character set, because my database and table are set to UTF8 encoding format, when this option is not set, the exported Chinese is garbled, strange is the official note, said that the default value of this option is UTF8, expressed puzzled.
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
Second, import data.
There is an import on the export. The SQL file exported from the 5th step above can be executed directly in MySQL Workbench, or can be imported using mysqldump, which shows how to use mysqldump import:
/usr/local/mysql/bin/mysqldump-uroot-p123456-hlocalhost--default-character-set=utf8 SpiderBBSDB </Users/ethan /desktop/catalog.sql third, about Java connection MySQL write Chinese garbled.
About this Chinese garbled problem, really toss me for a long time. Baidu Google Bing at the outset, most of the online copy and paste the answer, here to record their own situation, hope that fellow travelers no longer detours.
In fact, my changes are very simple, the database encoding to Utf-8, in the new table, the default encoding of the table is also changed to Utf-8, you can. Just such a small change, let me a full night, said to solve the problem of obsessive-compulsive disorder, the problem does not solve the real sleep, alas ~ ~ ~
Iv. Summary.
It seems that a lot of leaders do report like to add a summary, said a pile of nonsense, although the Hui can not understand, but feel very strong appearance. So I also add a summary: Chinese garbled akzent Toss people, these years with you fight a lot back, good, summed up. Http://www.2cto.com/database/201303/197637.html
MySQL Chinese garbled and exported as SQL statement and Excel problem solving
