How to set character set in mysql

2. Set the character set of the database

When we create a new MySQL database, we can set the default character set of the database so that all tables in the database All character sets will use this character set. The specific steps are as follows:

2.1 First check the character sets supported by MySQL

mysql> SHOW CHARACTER SET;

2.2 When creating a new database, add a character set setting Set

CREATE DATABASE new_db CHARACTER SET utf8;

or

The following sentence is rewritten: "Create a new database called 'new_db' with a default character set of utf8."

where utf8 is one of the commonly used character sets.

3. Set the character set of the MySQL table

In order to ensure that the table can store and display data correctly, we need to set the character encoding for the table. In MySQL, the character set of a table can be set when creating the table. Already created tables can be modified using the ALTER command.

3.1 Set the character set when creating the table

CREATE TABLE new_table (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The storage engine used to set the table is ENGINE=InnoDB, and the default character set used to set the table is CHARSET=utf8.

3.2 Modify the character set of the table

ALTER TABLE old_table CONVERT TO CHARACTER SET utf8;

or

ALTER TABLE old_table MODIFY COLUMN name VARCHAR(50) CHARACTER SET utf8;

Among them, the CONVERT TO command is used to modify the default character set of the table, and the MODIFY COLUMN command is used to modify the character set of a column in the table.

4. Set the character set for MySQL connection

When connecting to the MySQL server, you can also set the character set for the operation. The character set setting, often called "client character set", refers to the character set used for data transmitted on the client side.

4.1 View the character set of the current connection

mysql> SELECT @@character_set_connection;

4.2 Modify the character set of the connection

SET character_set_connection = utf8;

or

mysql --default-character-set=utf8 -u root -p

Among them, the SET command can modify the default character set of the connection, --default-character The -set command can specify the client character set.

5. Other character set settings

In some cases, in order to handle some less common data storage and conversion requirements, it may be necessary to enable Additional character set settings for MySQL. In this case, you may need to modify the MySQL configuration file-my.cnf file with administrator privileges. The modification method is as follows:

5.1 Find the my.cnf file

In Linux, the my.cnf file is usually stored in the /etc/my.cnf or /etc/mysql/my.cnf directory .

In Windows, the my.cnf file is usually stored in the installation directory of the MySQL database.

5.2 Modify the my.cnf file

Add the following statement in the my.cnf file:

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'

Among them, the character_set_server command is used to set the listed character set, and the init_connect command is used to automatically set the character set when creating a connection.