How to import data from Mysql8 to Mysql5.7

Order

The default character set of Mysql8 is utf8mb4, and the sorting rule is utf8mb4_0900_ai_ci. When we need to import Mysql8 data into Mysql5.7 , it will appear that Mysql5.7 does not support the sorting rule of utf8mb4_0900_ai_ci, then we can have the following 2 methods to solve it.

Modify the character set and collation of the xx.sql file

You can first export the Mysql8 data as a sql script, and then completely replace the character set and collation through the editor. This method is suitable for those who are not sure which version of Mysql to import, and can be modified as needed.

Directly modify the character set and sorting rules of the database and table

During our development stage, we may use the Mysql8 database, but when it is actually online, other versions of Mysql databases will be provided. In order to avoid the problem of character set and collation mismatch, we can make the character set and collation of the Mysql8 database for development compatible with the database version of the production environment.

Modify the database character set and collation rules

-- database_name = 你的库名
ALTER DATABASE `database_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

Modify the table character set and collation rules

-- database_name = 你的库名
SELECT
	concat( "ALTER TABLE `", TABLE_NAME, "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;" ) AS `target`
FROM
	information_schema.TABLES 
WHERE
	table_schema = "databse_name"

After executing the above SQL, you will get the SQL to modify the table character set and collation rules. We just copy it and execute it again.

Import MySQL8.0 to MySQL5.7 through Navicat

Open Navicat, select the database, click Tools–Data Transfer

Click on the file and select the version to be exported

Open the .sql file

utf8mb4替换为utf8
utf8mb4_0900_ai_ci替换为utf8_general_ci
utf8_croatian_ci替换为utf8_general_ci
utf8mb4_general_ci替换为utf8_general_ci

The above is the detailed content of How to import data from Mysql8 to Mysql5.7. For more information, please follow other related articles on the PHP Chinese website!