MySQL configuration utf8_mb4

http://blog.csdn.net/u013145194/article/details/51527389

Note: The above is reproduced, but when I actually use, modify the configuration file to restart the database, and then modify the type of the table can be

1. Modify the MySQL configuration file

Locate the my.cnf file under the/etc/mysql path, open the file with the VI command and edit it by adding the following configuration:

[Client]

Default-character-set=utf8mb4

[Mysqld]

Character-set-client-handshake = FALSE

Character-set-server = Utf8mb4

Collation-server = Utf8mb4_unicode_ci

init_connect= ' SET NAMES utf8mb4 '

[MySQL]

Default-character-set=utf8mb4

After the modification is completed, the exit is saved by Wq

2. Modifying the character set of database/table and column

Go to MySQL and execute the command as shown below:

1) Modify the database's character set:

ALTER database name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Example:

ALTER DATABASE xxxdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

2) Step 1) After execution, you need to execute the use database name to indicate the database that is currently required for character set modification; example: Use Xxxdb;

3) Modify the character set of the table:

ALTER table name CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Example:

ALTER TABLE user_comments CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4) Modify the character set of column:

ALTER Table table name change field Name field the original data type CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Example:

ALTER TABLE user_comments Change content content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After the above modification, exit the MySQL

3. Restart MySQL when I restarted here, I started with the service MySQL restart, and finally found that the command did not work, the reboot did not succeed, resulting in the subsequent view of the character set, and did not reach the desired character set state. so the following method can be used to correctly restart MySQL. 3.1 Stopping the operation of the mSQL

To execute the stop command through/etc/init.d/mysql

3.2 starting MySQL

Execute the start command via/etc/init.d/mysql

4. Check the character set:

Enter MySQL, with show VARIABLES where variable_name like ' character\_set\_% ' OR variable_name like ' collation% '; command to view the character set

After the above steps are implemented, you can successfully use emoji emoticons to store the functionality of the MySQL database.

————————————————————————————————————————————————————————————————

Since the MySQL database needs to be backed up and restored, the. sql file with emoji expression needs to be aware of the encoding format when it is executed, and the following are the specific operations for import and export:

5. Import and export of SQL file with emoji expression(primarily for export in Windows) (if exported in the server-side code: MYSQLDUMP–DEFAULT-CHARACTER-SET-UTF8MB4–U User name –p database name > Export the physical path of the file The imported command does not need to specify the encoding format, only the normal execution command is required) 5.1 export

In the case of a file with the suffix. sql, and the file data contains emoji expression data, when the backup is exported, do not use third-party software for export, but instead use the command line to perform the export action, the main reason is to use a third-party export of the file, due to its default export encoding format is Utf-8 , the encoding format supports a maximum of 3 bytes, while a emoji expression has 4 bytes, which causes the data of the emoji expression to become garbled. So when you export locally, the following steps are done:

First, open cmd, find mysqldump the path where the execution file resides;

Second, after the path, enter mysqldump–default-character-set-utf8mb4–u username –p database name > Export the physical path of the file, press "enter" to complete the export function, The exported file can be found in the physical path of the export.

5.2 Import

When importing. sql files from a Linux system to local, do not use Navicat premium for import, but instead use the physical path of the source. sql file in the command line as shown below to import the following.

MySQL configuration utf8_mb4