目录
MySQL5.7数据导导入到MySQL8.0(字符集及排序规则的变化)
1、MySQL5.7创建表(默认字符集-utf8)
(1)创建表test,字符集是utf8(默认既是utf8)
SQL> create table testdb.test(id int primary key,name varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; SQL> insert into test.test values(1,'LLL01'); SQL> insert into test.test values(2,'LLL02'); SQL> insert into test.test values(3,'LLL03'); SQL> insert into test.test values(4,'LLL04'); SQL> insert into test.test values(5,'LLL05'); SQL> insert into test.test values(6,'LLL06'); ...
(2)查看表的排序规则为utf8_general_ci
(3)导出后,导入到8.0
[root@mysql155 test]# mysqldump -uroot -prootroot --set-gtid-purged=OFF testdb test >test.sql [root@mysql155 test]# scp test.sql root@192.168.21.181:/mysql [root@mysql181 mysql]# mysql -uroot -prootroot testdb <test.sql
(4)查看8.0的配置规则
2、MySQL5.7创建表(字符集为utf8mb4)
(1)创建表test,字符集是utf8(默认既是utf8)
SQL> create table testdb.test1(id int primary key,name varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SQL> insert into test.test1 values(1,'LLL01'); SQL> insert into test.test1 values(2,'LLL02'); SQL> insert into test.test1 values(3,'LLL03'); SQL> insert into test.test1 values(4,'LLL04'); SQL> insert into test.test1 values(5,'LLL05'); SQL> insert into test.test1 values(6,'LLL06'); ...
(2)查看表的排序规则为utf8_general_ci
(3)导出后,导入到8.0
[root@mysql155 test]# mysqldump -uroot -prootroot --set-gtid-purged=OFF testdb test1 >test1.sql [root@mysql155 test]# scp test1.sql root@192.168.21.181:/mysql [root@mysql181 mysql]# mysql -uroot -prootroot testdb <test1.sql
(4)查看8.0的配置规则
3、结论
(1)如果MySQL5.7的表,字符集是utf8,则默认排序规则是utf8_general_ci;如果导入8.0,则使用的排序规则是utf8mb3_general_ci
(2)如果mySQL5.7的表,字符集是utf8mb4,则默认排序规则是utf8mb4_general_ci;如果导入8.0,则使用的排序规则是utf8mb4_0900_ai_ci