mysql encoding conversion

MySQL is an open source relational database management system used to store and manage data. When using MySQL, sometimes it is necessary to encode the data. Encoding conversion refers to converting one character set encoding into another character set encoding. Common character set encodings include UTF-8, GBK, GB2312, etc.

In MySQL, you can use the convert function and cast function to perform encoding conversion.

1. Use the convert function for encoding conversion

The convert function can convert a string from one character set encoding to another character set encoding. The syntax is as follows:

convert(expr,charset,[binary])

Among them, expr is the string that needs to be converted, charset is the character set of the target encoding, and binary is an optional parameter. Used to specify whether to use binary mode for conversion.

For example, convert a gb2312-encoded string to a utf8-encoded string:

SELECT CONVERT('中国',CHARACTER SET utf8);

The execution result is:

+---------------+
| CONVERT('中国',CHARACTER SET utf8) |
+---------------+
| 中国         (中文字符)        |
+---------------+

2. Use the cast function for encoding conversion

The cast function can also be used to convert a string from one character set encoding to another. The syntax is as follows:

cast(expr as type)

Among them, expr is the string that needs to be converted, and type is the target character set encoding type.

For example, convert a gbk-encoded string to a utf8-encoded string:

SELECT CAST('中国' as CHAR CHARACTER SET utf8);

The execution result is:

+------------------------------------+
| CAST('中国' as CHAR CHARACTER SET utf8) |
+------------------------------------+
| 中国                            |
+------------------------------------+

It should be noted that the cast function can only Convert an existing data type to a different data type. If the data types themselves do not match, conversion via the cast function is not possible.

In addition to using functions for encoding conversion, you can also configure it in the MySQL configuration file my.ini. In my.ini, you need to set the character-set-server and collation-server parameters to specify the character set and collation of the MySQL server. For example, change the MySQL server's default character set encoding to utf8:

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

At the same time, you can also specify the encoding type when creating a database and table. For example, create a database using utf8 character set encoding and utf8_general_ci collation:

CREATE DATABASE mydatabase
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_general_ci;

Create a table using utf8 character set encoding and utf8_general_ci collation:

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

Summary:

MySQL supports multiple character set encodings, common ones include UTF-8, GBK, GB2312, etc. When using MySQL, you need to set the correct character set encoding and collation to support multiple languages ​​and correct positioning of strings. You can use the convert function and cast function for encoding conversion, or you can specify the character set and collation in the MySQL configuration file my.ini.

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