MySQL character encoding settings

When installing MySQL, if the character encoding is the default value latin1, you need to modify it to UTF8 to support Chinese data.

The command is as follows:

1. Display the database character set

Mysql> Show create DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| Test | CREATE DATABASE ' test '/*!40100 DEFAULT CHARACTER SET latin1 */| |
+----------+-----------------------------------------------------------------+
1 row in Set (0.00 sec)

2. Display the data table character set

Mysql> show create table T3;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------+
| T3 | CREATE TABLE ' T3 ' (
' Name ' varchar (DEFAULT NULL)
) Engine=innodb DEFAULT charset=latin1 |
+-------+----------------------------------------------------------------------------------------------+
1 row in Set (0.00 sec)

3. Modify the data table character set encoding

Modifying the encoding of a data table

mysql> ALTER TABLE T4 default character set UTF8;
Query OK, 0 rows Affected (0.00 sec)
records:0 duplicates:0 warnings:0

Mysql> show create table T4;
+-------+-------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------+
| t4 | CREATE TABLE ' T4 ' (
' name ' varchar () CHARACTER set latin1 DEFAULT NULL,
' sex ' varchar (5) CHARACTER set Latin1 DE FAULT NULL
) engine=innodb DEFAULT Charset=utf8 |
+-------+-------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------+
1 row in Set (0.00 sec)

Modify a field encoding

mysql> ALTER TABLE T4 change name name varchar (character) set UTF8;
Query OK, 0 rows affected (0.04 sec)
records:0 duplicates:0 warnings:0

Mysql> show create table T4;
+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------+
| T4 | CREATE TABLE ' T4 ' (
' Name ' varchar (CHARACTER) SET UTF8 DEFAULT NULL,
' Sex ' varchar (5) DEFAULT NULL
) Engine=innodb DEFAULT charset=latin1 |
+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------+
1 row in Set (0.00 sec)

Modify the character set encoding of the entire data table, including data tables and fields

mysql> ALTER TABLE T4 convert to character set UTF8 collate utf8_general_ci;
Query OK, 0 rows affected (0.02 sec)
records:0 duplicates:0 warnings:0

Mysql> show create table T4;
+-------+------------------------------------------------------------------------------------------------------ -----------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------ -----------------------+
| T4 | CREATE TABLE ' T4 ' (
' Name ' varchar () DEFAULT NULL,
' Sex ' varchar (5) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8 |
+-------+------------------------------------------------------------------------------------------------------ -----------------------+
1 row in Set (0.00 sec)

Collate, collation is the comparison method. Used to specify how the dataset is sorted, and the alignment rules for strings. All the alignment rules supported by MySQL can be viewed by the show collation command. For example, CI in UTF8_GENERAL_CI is an abbreviation for case insensitive, which is not sensitive to capitalization, and CS is the abbreviation for case sensitive, which is sensitive to capitalization.

4. Modify the database default character set encoding

mysql> ALTER DATABASE test default character set UTF8;
Query OK, 1 row Affected (0.00 sec)

Mysql> Show create DATABASE test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| Test | CREATE DATABASE ' test '/*!40100 DEFAULT CHARACTER SET UTF8 */| |
+----------+---------------------------------------------------------------+
1 row in Set (0.00 sec)

5. Modify MySQL default character set encoding

[Mysqld] under Add:

Character-set-server=utf8

Collation-server=utf8_general_ci

Then restart the MySQL service, enter the command to query the current character set encoding is correct

Mysql> Show variables like ' character% ';
+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.00 sec)

MySQL character encoding settings