View and modify the mysql encoding method in CentOS

Source: Internet
Author: User

The default encoding of MySQL is Latin1, which does not support Chinese characters. To support Chinese characters, you must change the default encoding of the database to gbk or utf8. 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, and then enter the password of the root user twice). To view the encoding method of the database, run the following command:> 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/|
+ -------------------------- + Bytes + from the above information, we can see that the database code is latin1, which needs to be modified to gbk or utf8. character_set_client is the client encoding method; character_set_connection is the encoding used for establishing; character_set_database database encoding; character_set_results result set encoding; character_set_server Database Server encoding; 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 'colation % ';2. in linux, follow the following steps to modify the default encoding of the MySQL database: uStop running MySQL
/Etc/init. d/mysql start (stop) is to start and stop the server uThe MySQL main configuration file is my. cnf. The general directory is/etc/mysqlvar/lib/mysql/, where the database table folder is placed. 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 (If/etc/mysql/my. there is no my under cnf. cnf, which can be copied in/usr/share/mysql. the cnf file, such as the my-small.cnf, is renamed to my. modify cnf !)
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: utf8Or (character_set_server = utf8)
Init_connect = 'set NAMES utf8' # (SET utf8 encoding when connecting to the mysql database to run the mysql database as utf8)

After the 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. cnf3. 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 two ways to specify the encoding when creating a database: 1) CREATEDATABASEMs_dbCHARACTER SETUtf8COLLATEUtf8_general_ci; 2) createDatabaseIfNotExistsNetctossDefault character set utf8; 5. If you are using an external access method, you can determine the Request Encoding format in the connection, such as jdbc: mysql: // localhost: 3306/mysql? UseUnicode = true & characterEncoding = UTF-8 (Note: Do not see any space, otherwise error) 6. Run the Script: Specify the encoding format set names gbk (note, not a UTF-8) before you can modify the execution: after execution: we can see from the execution before and after the command that set names gbk can only modify the encoding methods of character_set_client, character_set_connection, and character_set_results. This modification is window-level and 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.