MySQL modifies encoding and Character Set

MySQL modifies the encoding and character set to enter the database installation directory: C:/program files/MySQL Server 5.0. find the INI file at www.2cto.com client section # -------------------------------------------------------------------- # The following options will be read by MYSQL client applications. # note that only client applications are shipped by MySQL are guaranteed # to read this section. if you want your own MYSQL client program to # honor these Values, You need to specify it as an option during the # MYSQL client library initialization. # [client] Port = 3306 [MySQL] default-character-set = utf8 # ------------------------------------------------------------------ in this section, you can modify the character set ********************************* used by the database ********************************* **************************************** ** [mysqld] # The TCP/IP Port the MySQL server will listen onport = 3306 # path to installation Directory. all paths are usually resolved relative to this. basedir = "C:/program files/MySQL Server 5.0/" # path to the database rootdatadir = "C: /program files/MySQL Server 5.0/data/"# The default character set that will be used when a new schema or table is # created and no character set is defineddefault-character- set = utf8 #------------------------------------------------------------------ ---- In this section, you can modify the default MySQL encoding used by the database. The default encoding is Latin1, which does not support Chinese characters. You need to change the default encoding of the database to GBK or utf8 at noon.

1. You need to log in as the root user to view the database encoding method (the command for logging in as the root user is:> mysql-u root-P, enter the password of the root user two times later). The encoding command for viewing the database is:
> Show variables like 'character % ';
+ -------------------------- + ---------------------------- +
| Variable_name | value |
+ -------------------------- + ---------------------------- +
| Character_set_client | Latin1 |
| Character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir |/usr/share/MySQL/charsets/|
+ -------------------------- + ---------------------------- +
From the above information, we can see that the database is encoded as Latin1 and needs to be modified to GBK or utf8;
Character_set_client indicates the client encoding method, character_set_connection indicates the encoding used to establish a connection, and character_set_database indicates the encoding of the database;
Character_set_results result set encoding;
Character_set_server database server code;
As long as the above four encoding methods are the same, there will be no garbled problem.
Another command for viewing database encoding:
> Show variables like 'collation % ';
 
2. in Linux, follow these steps to modify the default encoding of the MySQL database:
Ü stop MySQL running
/Etc/init. d/MySQL start (STOP) is used to start and stop the server.
Ü the MySQL main configuration file is my. CNF, and the General Directory is/etc/MySQL
Var/lib/MySQL/stores the database table folder. Here, MySQL is equivalent to the date folder of MySQL in windows.
Ü when we need to modify the default encoding of the MySQL database, we need to edit my. CNF file for encoding modification. in Linux, modify the MySQL configuration file my. CNF, default file location/etc/My. CNF File
Find the client configuration [client] and add it below
Default-character-set = utf8 default Character Set: utf8
Add in [mysqld]
Default-character-set = utf8 default Character Set: utf8
Init_connect = 'set names utf8' (utf8 encoding is used to set the connection to the MySQL database to run utf8)
After modification, restart MySQL and re-query the database encoding to find the encoding method changed:
> 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/|
+ -------------------------- + ---------------------------- +
This method is also valid for the standard MySQL version, for the/etc/My. CNF file, you need to copy from the MySQL/support-Files Folder CP my-large.cnf to/etc/My. CNF
 
3. In Windows, you can delete the MySQL database and reinstall it. During the installation process, you can directly use MySQL server instance config wizard for settings.
 
4. When the MySQL database server has data that is not suitable for deletion and reinstallation, you can specify the encoding method of the database individually. The encoding method specified by MySQL is flexible and diverse. You can specify the table-level encoding, row-level encoding, or even field-level encoding.
The following example shows how to specify the encoding when creating a database:
1) Create Database ms_db Character Set utf8 collate utf8_general_ci;
2) create database if not exists netctoss default Character Set utf8;
5. If you are using external access, you can determine the Request Encoding format in the connection, for example, JDBC: mysql: // localhost: 3306/MySQL? Useunicode = true & characterencoding = UTF-8 (Note: do not contain any spaces; otherwise, an error occurs)
6. Run the Script: Specify the encoding format set names GBK (note, not UTF-8) can be modified
Before execution:
After execution:
It can be seen from the execution before and after the command that set names GBK can only modify character_set_client, character_set_connection, character_set_results encoding method, and this modification is at the window level, only valid for this window, the modification in another window is invalid. We can also find that the encoding method at the underlying layer of the database has not changed, and the data is still maintained by UTF-8 encoding after being inserted.