MySQL 5.7数据迁移到8.0，报错主键冲突？

最新推荐文章于 2026-07-18 10:01:21 发布

原创最新推荐文章于 2026-07-18 10:01:21 发布 · 206 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MySQL

问题现象:

MySQL 5.7(源库) 通过mysqldump迁移到MySQL 8.0(目标库)，导入报错如下:

ERROR 1062 (23000) at line 1138: Duplicate entry '2025-10-21 13:30:00-IT（数据库）小Chen-7949' for key 't1aa_aaa_aaaa_aaaa.PRIMARY'

问题分析:

检查原库数据:

mysql> select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_NAME='t1aa_aaa_aaaa_aaaa';
+--------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME         |
+--------------+--------------------+
| cjc          | t1aa_aaa_aaaa_aaaa |
+--------------+--------------------+
1 row in set (0.03 sec)

查看表结构:

mysql> show create table cjc.t1aa_aaa_aaaa_aaaa\G;
*************************** 1. row ***************************
       Table: t1aa_aaa_aaaa_aaaa
Create Table: CREATE TABLE `t1aa_aaa_aaaa_aaaa` (
  `col_01` varchar(50) NOT NULL COMMENT 'aa日期 yyyymmdd',
  `col02x_aaa_aaa` varchar(10) DEFAULT NULL COMMENT 'aa期数 ',
  `col03x_aaa` varchar(500) DEFAULT NULL COMMENT 'abcd ',
  `col4_aa` varchar(500) DEFAULT NULL COMMENT 'aa编号 ',
  `col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ',
  `col6_col6_co` varchar(50) DEFAULT NULL COMMENT 'aaaa代码 ',
  `col_07` varchar(50) NOT NULL COMMENT 'bbbb代码 ',
  `col8_aaa_aaaa_aa` varchar(50) DEFAULT NULL COMMENT 'cccccaa编号 ',
  `col9_grp_col5_aa` varchar(500) DEFAULT NULL COMMENT 'ccccccaa名称 ',
  `col10x_mth` varchar(10) NOT NULL COMMENT '报送月份 yyyymm',
  PRIMARY KEY (`col_01`,`col5_aa`,`col_07`,`col10x_mth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试0515'
1 row in set (0.00 sec)

检查是否存在主键值超过2行的数据:无

mysql> select count(*),`col_01`,`col5_aa`,`col_07`,`col10x_mth` from cjc.t1aa_aaa_aaaa_aaaa group by `col_01`,`col5_aa`,`col_07`,`col10x_mth` order by 1 desc limit 10;

检查详细数据:

mysql> select `col_01`,`col5_aa`,`col_07`,`col10x_mth` from cjc.t1aa_aaa_aaaa_aaaa where col5_aa like 'IT%' order by 1;
+---------------------+-----------------------------------------+-----------+------------+
| col_01              | col5_aa                                 | col_07    | col10x_mth |
+---------------------+-----------------------------------------+-----------+------------+
| 2025-10-21 13:30:00 | IT(数据库)小Chen                | 7949***** | 202109     |
| 2025-10-21 13:30:00 | IT（数据库）小Chen              | 7949***** | 202109     |
......

查看指定库中表字段的字符集和排序规则：

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='cjc' AND TABLE_NAME='t1aa_aaa_aaaa_aaaa';

原库:

+--------------+--------------------+------------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME         | COLUMN_NAME      | CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------+--------------------+------------------+--------------------+--------------------+
| cjc       | t1aa_aaa_aaaa_aaaa | col_01           | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col02x_aaa_aaa   | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col03x_aaa       | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col4_aa          | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col5_aa          | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col6_col6_co     | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col_07           | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col8_aaa_aaaa_aa | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col9_grp_col5_aa | utf8mb4            | utf8mb4_general_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col10x_mth       | utf8mb4            | utf8mb4_general_ci |
+--------------+--------------------+------------------+--------------------+--------------------+
10 rows in set (0.00 sec)

mysql> show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql> show global variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

目标库:

+--------------+--------------------+------------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME         | COLUMN_NAME      | CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------+--------------------+------------------+--------------------+--------------------+
| cjc       | t1aa_aaa_aaaa_aaaa | col_01           | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col02x_aaa_aaa   | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col03x_aaa       | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col4_aa          | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col5_aa          | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col6_col6_co     | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col_07           | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col8_aaa_aaaa_aa | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col9_grp_col5_aa | utf8mb4            | utf8mb4_0900_ai_ci |
| cjc       | t1aa_aaa_aaaa_aaaa | col10x_mth       | utf8mb4            | utf8mb4_0900_ai_ci |
+--------------+--------------------+------------------+--------------------+--------------------+
10 rows in set (0.00 sec)

mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

mysql> show global variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

问题原因:

怀疑和MySQL 5.7和MySQL 8.0默认排序规则有关。
源库MySQL 5.7使用建表时没有指定具体列的排序规则，默认使用 utf8mb4_general_ci 排序规则。
目标库MySQL 8.0，导入5.7的表数据时，默认使用 utf8mb4_0900_ai_ci 排序规则。
而 8.0 默认排序规则 utf8mb4_0900_ai_ci ，不区分"中文/英文括号"，太奇葩了，“()” 和 “（）” 被认为是相同的数据？

问题重现:

建表，使用 utf8mb4_0900_ai_ci 排序规则，不区分"中文/英文括号"，主键冲突:

mysql> create table t0514bbb (id int,name varchar(10) COLLATE utf8mb4_0900_ai_ci,primary key (name));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t0514bbb values(1,'数据库(MySQL)');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t0514bbb values(1,'数据库（MySQL）');
ERROR 1062 (23000): Duplicate entry '数据库（MySQL）' for key 't0514bbb.PRIMARY'

而使用 utf8mb4_general_ci 排序规则，没有这个问题。

mysql> create table t0514ccc (id int,name varchar(10) COLLATE utf8mb4_general_ci,primary key (name));
insert into t0514ccc values(1,'数据库(MySQL)');
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t0514ccc values(1,'数据库(MySQL)');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t0514ccc values(1,'数据库（MySQL）');
Query OK, 1 row affected (0.00 sec)

解决方案:

查看备份文件里的表结构

mysql@CJC-DB-001:/mysqldata/dbtmpfile$ls -lrth /mysqldata/dbtmpfile/cjc_mysql_bak_20260514.sql 
-rw-r--r-- 1 mysql mysql 7.3G May 14 17:22 /mysqldata/dbtmpfile/cjc_mysql_bak_20260514.sql

mysql@CJC-DB-001:/mysqldata/dbtmpfile$sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1aa_aaa_aaaa_aaaa`/!d;q'  cjc_mysql_bak_20260514.sql

DROP TABLE IF EXISTS `t1aa_aaa_aaaa_aaaa`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1aa_aaa_aaaa_aaaa` (
  `col_01` varchar(50) NOT NULL COMMENT 'aa日期 yyyymmdd',
  `col02x_aaa_aaa` varchar(10) DEFAULT NULL COMMENT 'aa期数 ',
  `col03x_aaa` varchar(500) DEFAULT NULL COMMENT 'abcd ',
  `col4_aa` varchar(500) DEFAULT NULL COMMENT 'aa编号 ',
  `col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ' ,
  `col6_col6_co` varchar(50) DEFAULT NULL COMMENT 'aaaa代码 ',
  `col_07` varchar(50) NOT NULL COMMENT 'bbbb代码 ',
  `col8_aaa_aaaa_aa` varchar(50) DEFAULT NULL COMMENT 'cccccaa编号 ',
  `col9_grp_col5_aa` varchar(500) DEFAULT NULL COMMENT 'ccccccaa名称 ',
  `col10x_mth` varchar(10) NOT NULL COMMENT '报送月份 yyyymm',
  PRIMARY KEY (`col_01`,`col5_aa`,`col_07`,`col10x_mth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试0515';
/*!40101 SET character_set_client = @saved_cs_client */;

将

`col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 '

改成

`col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ' COLLATE utf8mb4_general_ci

具体修改方法如下:
按关键字或者行号进行操作和修改:
按关键字搜索:

mysql@CJC-DB-001:/mysqldata/dbtmpfile$grep -n " 'aa名称 '" cjc_mysql_bak_20260514.sql|wc -l
4

mysql@CJC-DB-001:/mysqldata/dbtmpfile$grep -n " 'aa名称 '" cjc_mysql_bak_20260514.sql
974:  `col5_aa` varchar(255) DEFAULT NULL COMMENT 'aa名称 ',
1122:  `col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ',
6201:  `col5_aa` varchar(500) DEFAULT NULL COMMENT 'aa名称 ',
6231:  `col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ',
mysql@CJC-DB-001:/mysqldata/dbtmpfile$

预览修改:

mysql@CJC-DB-001:/mysqldata/dbtmpfile$sed -n "s/'aa名称 '/'aa名称 ' COLLATE utf8mb4_general_ci/pg" cjc_mysql_bak_20260514.sql
  `col5_aa` varchar(255) DEFAULT NULL COMMENT 'aa名称 ' COLLATE utf8mb4_general_ci,
  `col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ' COLLATE utf8mb4_general_ci,
  `col5_aa` varchar(500) DEFAULT NULL COMMENT 'aa名称 ' COLLATE utf8mb4_general_ci,
  `col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ' COLLATE utf8mb4_general_ci,

正式修改:

mysql@CJC-DB-001:/mysqldata/dbtmpfile$sed -i "s/'aa名称 '/'aa名称 ' COLLATE utf8mb4_general_ci/g" cjc_mysql_bak_20260514.sql

再次检查表结构,修改正确:

mysql@CJC-DB-001:/mysqldata/dbtmpfile$sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1aa_aaa_aaaa_aaaa`/!d;q'  cjc_mysql_bak_20260514.sql

DROP TABLE IF EXISTS `t1aa_aaa_aaaa_aaaa`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1aa_aaa_aaaa_aaaa` (
  `col_01` varchar(50) NOT NULL COMMENT 'aa日期 yyyymmdd',
  `col02x_aaa_aaa` varchar(10) DEFAULT NULL COMMENT 'aa期数 ',
  `col03x_aaa` varchar(500) DEFAULT NULL COMMENT 'abcd ',
  `col4_aa` varchar(500) DEFAULT NULL COMMENT 'aa编号 ',
  `col5_aa` varchar(500) NOT NULL COMMENT 'aa名称 ' COLLATE utf8mb4_general_ci,
  `col6_col6_co` varchar(50) DEFAULT NULL COMMENT 'aaaa代码 ',
  `col_07` varchar(50) NOT NULL COMMENT 'bbbb代码 ',
  `col8_aaa_aaaa_aa` varchar(50) DEFAULT NULL COMMENT 'cccccaa编号 ',
  `col9_grp_col5_aa` varchar(500) DEFAULT NULL COMMENT 'ccccccaa名称 ',
  `col10x_mth` varchar(10) NOT NULL COMMENT '报送月份 yyyymm',
  PRIMARY KEY (`col_01`,`col5_aa`,`col_07`,`col10x_mth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试0515';
/*!40101 SET character_set_client = @saved_cs_client */;

清理数据，重新导入，导入成功!

标签

#mysql #数据库