Home  >  Article  >  Database  >  How to import data from Mysql8 to Mysql5.7

How to import data from Mysql8 to Mysql5.7

WBOY
WBOYforward
2023-06-03 09:43:263492browse

    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

    How to import data from Mysql8 to Mysql5.7

    Click on the file and select the version to be exported

    How to import data from Mysql8 to Mysql5.7

    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!

    Statement:
    This article is reproduced at:yisu.com. If there is any infringement, please contact admin@php.cn delete