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.
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.
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.
-- database_name = 你的库名 ALTER DATABASE `database_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
-- 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.
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!