(go) How to modify the character set of MySQL under Windows

Original: http://blog.csdn.net/yjz_sdau/article/details/52135050

(1) The simplest method of modification is to modify the character set key values in the MySQL My.ini file,

such as Default-character-set = UTF8
Character_set_server = UTF8

After the modification, restart the MySQL services, service MySQL restart

Use mysql> show VARIABLES like ' character% '; view, discover that database encoding has been changed to UTF8

(2) Another way to modify the MySQL default character set is to use the MySQL command

1. Modify Database character encoding

mysql> ALTER DATABASE mydb character set UTF8;

2. When creating a database, specify the character encoding of the database

Mysql> CREATE database mydb character set UTF8;

3. Check the character encoding of MySQL database

Mysql> Show variables like ' character% '; Query character encoding for all properties of the current MySQL database

+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | Latin1 |
| character_set_connection | Latin1 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+

4. Modify the character encoding of MySQL database

Modify the character encoding must modify the MySQL configuration file my.cnf, and then restart to take effect

There are a few places to modify MY.CNF:

Under "Client", add Default-character-set=utf8, or Character_set_client=utf8

"Mysqld" under, plus character_set_server = UTF8;

Because the above configuration, MySQL default is Latin1, if only through the command line client, MySQL Restart after the no effect.

The following is how the client command line is modified, not recommended

Mysql> set Character_set_client=utf8;

Mysql> set Character_set_connection=utf8;

Mysql> set Character_set_database=utf8;

Mysql> set Character_set_database=utf8;

Mysql> set Character_set_results=utf8;

Mysql> set Character_set_server=utf8;

Mysql> set Character_set_system=utf8;

Mysql> Show variables like ' character% ';
+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.00 sec)

Http://m.blog.chinaunix.net/uid-20639775-id-154602.html

If you do not have the correct set of character sets at the beginning of the application, you will not find that the requirements need to be adjusted after running for a while.

If you do not want to discard data for this period of time, you will need to modify the character set. The modification of the character set cannot be passed directly through alter

Dataabase Character Set * * * or ALTER TABLE tablename Character Set * * *; command to proceed, and these two

command does not update the character set of the existing record, but only the newly created table or record.
The character set adjustment for an existing record requires that the data be exported before it can be completed after the appropriate adjustments are re-imported.

The following simulates the process of modifying a database of the latin1 character set to a database of the GBK character set.

1> Export Table structure:
Mysqldump-uroot-p--default-character-set=gbk-d databasename > Createtab.sql

Where--DEFAULT-CHARACTER-SET=GBK indicates which character set to connect,-D means that only the table structure is exported, the number is not exported

According to

2> manually modifies the character set in the table structure definition in Createtab.sql to be the new character set.

3> ensure that records are no longer updated and that all records are exported.

Mysqldump-uroot-p--quick--no-create-info--extended-insert--default-character-

set=latin1 databasename > Data.sql

--quick: This option is used to dump large tables. It forces mysqldump to retrieve rows from a table at a time from the server instead of checking

All rows and caches it in memory before output.

--extended-insert: Use the multiple-line insert syntax, which includes several values lists, to make the dump file smaller and reload the file

can speed up insertion.

--no-create-info: CREATE TABLE statement that re-creates each dump table is not written.

--default-character-set=latin1: Exports all data according to the original character set, so that all the Chinese in the exported file are

Visible, will not be saved as garbled.

4> Open the Data.sql and modify the set names latin1 to set names GBK.

5> creates a new database with a new character set.

Create DATABASE DatabaseName default CharSet GBK;

6> CREATE TABLE, execute Createtab.sql

Mysql-uroot-p DatabaseName < Createtab.sql

7> import data, execute Data.sql

Mysql-uroot-p DatabaseName < Data.sql


Note: When choosing a character set, be aware that it is best to have the source character super, or make sure that the font is larger than the source set, or if the target

Character set is less than the font of the source character set, then the target character set is not supported by the characters into garbled, lost part of the data. For example, if the GBK character set is larger than the GB2312 character set, then the data of the GBK character set, if imported into the GB2312 database, will lose the data of the part of the kanji that is not supported in GB2312.

(go) How to modify the character set of MySQL under Windows