mysql set encoding format

MySQL is an open source relational database management system that supports multiple encoding formats. When setting the encoding format of MySQL, you need to consider the adaptability between different encoding formats of databases, tables, and columns to ensure the correctness and integrity of the data. This article will provide a detailed introduction and explanation of the encoding format of MySQL.

1. Introduction to MySQL encoding format

The encoding format of MySQL is divided into two types, namely character set and proofreading set. Character sets are used to define character sets, while collation sets are used to define string comparison and collation rules. Character sets and collation sets are important parts of MySQL because they directly affect the storage and display of data.

1.Character set

Character set refers to a set of character encoding rules. The character set modes supported by MySQL include:

(1) ASCII: corresponds to the English character set and only contains 128 character encodings.

(2) Latin1 or ISO-8859-1: Contains the special characters of most European languages, including a total of 256 character codes.

(3) Unicode: It is the most commonly used character set type now. It supports characters in various languages ​​around the world and contains more than 100,000 character encodings, including UCS-2, UTF-16 and UTF-8. wait.

2. Proofing set

Proofing set is used to define string comparison and sorting rules. The collation sets supported by MySQL include:

(1) ascii_general_ci: It is not case-sensitive for characters in the ASCII character range, but it is case-sensitive in other character ranges.

(2) utf8_general_ci: It localizes all characters when matching, sorting and comparing.

(3) utf8_unicode_ci: It will perform standard Unicode comparison on characters and support complex character set languages.

2. Set the MySQL encoding format

Setting the MySQL encoding format is mainly divided into the following aspects:

1. Set the server encoding format

In MySQL When the service starts, a default encoding format is set. We need to set the encoding format we need in the my.cnf file.

Open the my.cnf file, find the [mysqld] settings block, and set the values ​​of the character set and proofreading set.

Character set setting

character-set-server = utf8

Collation set setting

collation-server = utf8_general_ci

Restart mysql service to make changes take effect.

2. Set the database encoding format

If you are using MySQL Workbench to manage the database and create tables, you can directly set the encoding format and proofreading rules during the table creation process. Under the "Data Definition" tab, set the encoding format and proofreading rules of the table.

3. Set the table encoding format

When creating a table, we need to clearly set the encoding format and proofreading set of the table, and number it in the table creation statement.

For example:

CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the above example, we set the table The character set is utf8.

4. Set the column encoding format

If you want to set the encoding format for the columns of the created table, you can use the ALTER TABLE statement, the MODIFY COLUMN clause and the character set and collation rules. specified.

For example:

ALTER TABLE test MODIFY COLUMN name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci;

Here we The character set and collation rules are manually specified for the name column of the test table.

Through the above settings, we can make MySQL operate data that conforms to different encoded character types, and can compare, sort and store data correctly. At the same time, pay attention to the encoding format setting of the database connection to ensure that the encoding of all data submitted to the database matches the encoding format defined in the database to avoid data anomalies and ensure data integrity.

In short, setting the MySQL encoding format is very important to ensure normal data storage and display. You need to carefully consider the adaptability of the character set and proofreading set, and ensure that the connection encoding matches the server encoding to avoid data anomalies. situation occurs.

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