升级数据库表结构问题检查单(MySQL)
#3780 - Referencing column 'DomainId' and referenced column 'Id' in foreign key constraint 'FK_SiteLoginAppointedStaff_Domain_DomainId' are incompatible.
该问题是由于字段类型不兼容引起的,如果在使用中升级过 MySQL 数据库的版本,就有可能会在执行表结构升级脚本时遇到该问题。一般是因为高版本的 MySQL 放弃了对之前旧版本字符集的支持引起的。
解决方法
查看之前旧表中对应字段的字符集,在此例中,我们查看 Domain 表中 Id 的字符集。
如图,原有表中 Id 使用的字符集排序规则是 utf8mb3_general_ci
。
注意:客服系统数据库本身并没有指定字符集,使用的字符集取决于创建数据库时,其本身的默认字符集。
修改升级数据库脚本,在创建表的脚本后面增加指定字符集排序规则的内容:
ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Compact;
例如原 SQL 脚本
CREATE TABLE `SiteLoginAppointedStaff` (
`Id` char(36) NOT NULL,
`DomainId` char(36) NOT NULL,
`SiteId` char(36) NOT NULL,
`StaffId` char(36) NOT NULL,
CONSTRAINT `PK_SiteLoginAppointedStaff` PRIMARY KEY (`Id`),
CONSTRAINT `FK_SiteLoginAppointedStaff_Domain_DomainId` FOREIGN KEY (`DomainId`) REFERENCES `Domain` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_SiteLoginAppointedStaff_Site_SiteId` FOREIGN KEY (`SiteId`) REFERENCES `Site` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_SiteLoginAppointedStaff_Staff_StaffId` FOREIGN KEY (`StaffId`) REFERENCES `Staff` (`Id`) ON DELETE CASCADE
);
修改为
CREATE TABLE `SiteLoginAppointedStaff` (
`Id` char(36) NOT NULL,
`DomainId` char(36) NOT NULL,
`SiteId` char(36) NOT NULL,
`StaffId` char(36) NOT NULL,
CONSTRAINT `PK_SiteLoginAppointedStaff` PRIMARY KEY (`Id`),
CONSTRAINT `FK_SiteLoginAppointedStaff_Domain_DomainId` FOREIGN KEY (`DomainId`) REFERENCES `Domain` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_SiteLoginAppointedStaff_Site_SiteId` FOREIGN KEY (`SiteId`) REFERENCES `Site` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_SiteLoginAppointedStaff_Staff_StaffId` FOREIGN KEY (`StaffId`) REFERENCES `Staff` (`Id`) ON DELETE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Compact;
再次执行脚本即可完成升级。