How to set utf8 in mysql

MySQL is a popular relational database management system that supports various encoding formats. In this article, we will explore how to set up UTF8 encoding to ensure that the Unicode character set is stored, processed, and displayed correctly in MySQL.

What is UTF8?

UTF8 is a variable-length encoding format used to represent the Unicode character set. This character set can represent all characters in the world, including text, symbols and emoticons in various languages. UTF8 encoding uses one to four bytes to represent different characters and is flexible and scalable, so it is widely adopted.

Why should we set UTF8?

In MySQL, the default character set is Latin1, which only supports 128 characters. If you need to store international character sets, you need to use a more advanced character set, such as UTF8. If you don't set MySQL to UTF8 encoding, you may run into problems when trying to store and process non-Latin1 characters, resulting in garbled characters or incorrect display.

How to set UTF8?

Setting up UTF8 encoding in MySQL is very simple. Just follow the steps below:

1. Modify the configuration file of the MySQL server

Open the MySQL configuration file my.cnf, usually located in /etc/mysql/ or /etc/ mysql/mysql.conf.d/ directory. If you cannot find the file, you can try to find the file by running the following command in the terminal:

sudo find / -name my.cnf

Use a text editor to open the configuration file and add the following code under [mysqld]:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

This will set the MySQL server's default character set and collation to UTF8, and use utf8_general_ci as the default collation for the character set.

2. Restart the MySQL server

After modifying the my.cnf file, you need to restart the MySQL server for the changes to take effect. Use the following command in the terminal to restart the MySQL server:

sudo service mysql restart

3. Setting the character set when creating databases and tables

When creating databases and tables, you can use CREATE Set the UTF8 character set in DATABASE and CREATE TABLE statements. For example:

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE mytable (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

This will make the database and tables use UTF8 as the default character set.

4. Modify the field character set of an existing table

If you need to change the field character set in an existing table to UTF8, you can use the ALTER TABLE statement, for example:

ALTER TABLE mytable MODIFY COLUMN name VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci;

This will modify the character set of the name field in the mytable table to UTF8.

Summary:

By following the above steps to set MySQL's UTF8 encoding, you can ensure that MySQL can store, process and display the Unicode character set, ensuring the correctness and integrity of the data. If problems such as garbled characters occur, you can consider whether the character set of MySQL is set correctly. Be careful to back up your database when doing this to prevent unexpected data loss.

The above is the detailed content of How to set utf8 in mysql. For more information, please follow other related articles on the PHP Chinese website!