How to set UTF-8 encoding in MySQL

MySQL is a commonly used relational database management system that is widely used in Web applications, large data processing and other distributed applications. MySQL's character set settings are very important for the correct operation and data storage of the database, and UTF-8 is a widely used character set that supports most languages ​​​​in the world, including Chinese, English, Japanese, etc. Therefore, setting UTF-8 is crucial when using MySQL. Here's how to correctly set up UTF-8 in MySQL.

1. Database character set setting
In MySQL, the character set setting has two parameters, one is the character set of the database, and the other is the character set of the connection. When creating a database, you can set the default character set for the database by specifying the character set. After logging in to MySQL, you can view the default character set of the database through the following command:

SHOW VARIABLES LIKE 'character_set_database';

If the character set displayed in the query results is not UTF-8, it needs to be modified. Default character set. The modification method is as follows:

1. Open the MySQL configuration file my.cnf, find the [mysqld] section, and add the following code:

[mysqld]
character-set- server=utf8

2. Restart the MySQL service to complete the modification of the default character set.

2. Connection character set setting
When connecting to the database through the MySQL client, you also need to specify the character set of the connection. This can be achieved by adding the following configuration:

mysql --default-character-set=utf8

The above command indicates that the UTF-8 character set is used by default when connecting. You can also modify it in the MySQL client. The modification method is as follows:

1. Enter the MySQL client through the following command:

mysql -u username-p

2. After entering the password, enter the following command to modify the connection character set:

SET NAMES utf8;

This completes the modification of the connection character set.

3. Table character set setting
In MySQL, each table can have its own character set. When creating a table, you can set the table's default character set by specifying the character set. If no character set is specified, the database's default character set is used. You can check the character set of the table through the following command:

SHOW CREATE TABLE table name;

If the query result is not UTF-8, you need to modify the character set of the table. The modification method is as follows:

1. Modify the character set of the table, which can be achieved through the ALTER TABLE statement. The statement is as follows:

ALTER TABLE table name CONVERT TO CHARACTER SET utf8;

2. After modification, you can check whether the character set of the table has been modified successfully by running the following command:

SHOW CREATE TABLE table name;

4. Data characters Set settings
In addition to the above character set settings, there is another situation that requires special attention, that is, when inserting data into the database, if the character set of the data source is inconsistent with the character set of the database, garbled characters may result. . In this case, it can be solved by the following method:

1. Set the client character set to UTF-8, which is the method mentioned in the connection character set setting.
2. When inserting data into the database, UTF-8 encoding needs to be used. For example, if you are inserting data into a table through a SQL statement, the data should be provided to the INSERT statement as a UTF-8 encoded string. The code is as follows:

INSERT INTO mytable (mycolumn) VALUES ('I love programming');

In PHP, you can use the following code to convert a string to UTF-8 encoding ：

$utf_str = iconv('current encoding', 'UTF-8', $str);

This can avoid garbled characters caused by inconsistent data encoding.

Summary:
It is very important to set the UTF-8 character set in MySQL. This can not only avoid garbled characters caused by inconsistent character sets, but also improve the correctness and operation of data storage. s efficiency. When setting the character set, you need to pay attention to various aspects such as database character set, connection character set, table character set, data character set, etc. to ensure that the settings are correct to ensure system stability and data security.

The above is the detailed content of How to set UTF-8 encoding in MySQL. For more information, please follow other related articles on the PHP Chinese website!