See if you can modify the mysql encoding to support Chinese of gbk or utf8
- 2020-05-15 02:19:55
- OfStack
The default code of MySQL is Latin1, which does not support Chinese. To support Chinese, you need to change the default code of the database to gbk or utf8.
1. You need to log in as root user to view the database encoding mode (the command to log in as root user is: > mysql-u root, p, and enter the password of root user twice.
> 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/ |
+--------------------------+----------------------------+
According to the above information, the code of the database is latin1, which needs to be modified to gbk or utf8.
Where, character_set_client is the client encoding method; character_set_connection the code used to establish the connection; character_set_database database code;
The encoding of character_set_results result set;
character_set_server database server code;
As long as the above four adopted coding method 1, there will be no garbled problem.
Another command to view the database encoding:
> show variables like 'collation %';
2. In the linux system, the steps to modify the default code of MySQL database are as follows:
U stops the operation of MySQL
/ etc/init d/mysql start (stop) as the start and stop the server
The u MySQL main configuration file is my.cnf, and the 1 common directory is /etc/mysql
var/lib/mysql/ is the database table folder, and mysql here is equivalent to date for mysql under windows
U when we need to modify the default encoding of MySQL database, we need to edit the my.cnf file for encoding modification, and modify the configuration file my.cnf of mysql under linux. The file location defaults to /etc/ my.cnf file under linux
Find the client configuration [client] to add below
default-character-set =utf8 the default character set is utf8
Go to [mysqld] to add
default-character-set =utf8 the default character set is utf8
init_connect='SET NAMES utf8' (set the utf8 code when connecting to mysql database to make mysql database run as utf8)
After the modification, restart mysql, and re-query the database code to find the change in the encoding mode:
> 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 /etc/ my.cnf files, you need to go from the mysql/ support-files folder cp my-large.cnf1 to /etc/ my.cnf
3. The MySQL database can be deleted from the windows system and reinstalled. During the installation process, you can directly use Mysql Server Instance Config Wizard to set up the MySQL database
4. When the MySQL database server has data that is not suitable for deletion and reinstallation, the encoding method of the database can be specified individually. The way MySQL specifies encodings is very flexible and diverse, with table level encodings, row level encodings, and even field level encodings.
The following example shows two ways to specify encoding when creating a database:
1) CREATE DATABASE ms_db CHARACTER SET utf8 COLLATE utf8_general_ci;
2) create database if not exists default character set utf8;
5, if you are using is an external access, can be determined in the connection request encoding formats such as: jdbc: mysql: / / localhost: 3306 / mysql? useUnicode=true&characterEncoding= utf-8
6. Execute the script: specify the encoding format set names gbk(note, not UTF-8) to be modified
Perform before:
After the execution:
From the command before and after execution, it can be seen that set names gbk can only modify the encoding mode of character_set_client, character_set_connection, character_set_results, and this modification is window-level, which is only valid for this window, while opening another window is invalid. It can also be found that the encoding mode at the bottom of the database has not changed, and the encoding mode of utf8 is still maintained after data is inserted.
1. You need to log in as root user to view the database encoding mode (the command to log in as root user is: > mysql-u root, p, and enter the password of root user twice.
> 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/ |
+--------------------------+----------------------------+
According to the above information, the code of the database is latin1, which needs to be modified to gbk or utf8.
Where, character_set_client is the client encoding method; character_set_connection the code used to establish the connection; character_set_database database code;
The encoding of character_set_results result set;
character_set_server database server code;
As long as the above four adopted coding method 1, there will be no garbled problem.
Another command to view the database encoding:
> show variables like 'collation %';
2. In the linux system, the steps to modify the default code of MySQL database are as follows:
U stops the operation of MySQL
/ etc/init d/mysql start (stop) as the start and stop the server
The u MySQL main configuration file is my.cnf, and the 1 common directory is /etc/mysql
var/lib/mysql/ is the database table folder, and mysql here is equivalent to date for mysql under windows
U when we need to modify the default encoding of MySQL database, we need to edit the my.cnf file for encoding modification, and modify the configuration file my.cnf of mysql under linux. The file location defaults to /etc/ my.cnf file under linux
Find the client configuration [client] to add below
default-character-set =utf8 the default character set is utf8
Go to [mysqld] to add
default-character-set =utf8 the default character set is utf8
init_connect='SET NAMES utf8' (set the utf8 code when connecting to mysql database to make mysql database run as utf8)
After the modification, restart mysql, and re-query the database code to find the change in the encoding mode:
> 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 /etc/ my.cnf files, you need to go from the mysql/ support-files folder cp my-large.cnf1 to /etc/ my.cnf
3. The MySQL database can be deleted from the windows system and reinstalled. During the installation process, you can directly use Mysql Server Instance Config Wizard to set up the MySQL database
4. When the MySQL database server has data that is not suitable for deletion and reinstallation, the encoding method of the database can be specified individually. The way MySQL specifies encodings is very flexible and diverse, with table level encodings, row level encodings, and even field level encodings.
The following example shows two ways to specify encoding when creating a database:
1) CREATE DATABASE ms_db CHARACTER SET utf8 COLLATE utf8_general_ci;
2) create database if not exists default character set utf8;
5, if you are using is an external access, can be determined in the connection request encoding formats such as: jdbc: mysql: / / localhost: 3306 / mysql? useUnicode=true&characterEncoding= utf-8
6. Execute the script: specify the encoding format set names gbk(note, not UTF-8) to be modified
Perform before:
After the execution:
From the command before and after execution, it can be seen that set names gbk can only modify the encoding mode of character_set_client, character_set_connection, character_set_results, and this modification is window-level, which is only valid for this window, while opening another window is invalid. It can also be found that the encoding mode at the bottom of the database has not changed, and the encoding mode of utf8 is still maintained after data is inserted.