案例1—MySQL8.0-将表主键UUID改为自增列UUID

2024年3月17日 1232点热度 0人点赞 0条评论

一、方案1：利用DML的插入方式
三、方案3：sed修改mysqldump的内容

注：有多种方案

一、方案1：利用DML的插入方式

1、获取源表的创建方式

mysql> show create table operationproject.operation_workorder\g;

CREATE TABLE `operation_workorder` (
`Id` char(36) NOT NULL,
`Code` varchar(50) NOT NULL,
`Type` varchar(50) NOT NULL,
`System` varchar(50) NOT NULL,
`Name` varchar(100) NOT NULL,
`Description` longtext NOT NULL,
`Attachment` varchar(2000) DEFAULT NULL,
`Status` varchar(50) NOT NULL,
`SubmitDate` datetime DEFAULT NULL,
`AssignDate` datetime DEFAULT NULL,
`AssignCode` varchar(50) DEFAULT NULL,
`AssignName` varchar(50) DEFAULT NULL,
`AssignMemo` varchar(500) DEFAULT NULL,
`ToDevelopDate` datetime DEFAULT NULL,
`ToDevelopCode` varchar(50) DEFAULT NULL,
`ToDevelopName` varchar(50) DEFAULT NULL,
`ToRequirementDate` datetime DEFAULT NULL,
`ToRequirementCode` varchar(50) DEFAULT NULL,
`ToRequirementName` varchar(50) DEFAULT NULL,
`SolvePersonCode` varchar(50) DEFAULT NULL,
`SolvePersonName` varchar(50) DEFAULT NULL,
`SolveDate` datetime DEFAULT NULL,
`Grade` varchar(50) DEFAULT NULL,
`QuestionType` varchar(50) DEFAULT NULL,
`IsToKnowledgeBase` varchar(50) DEFAULT NULL,
`ToKnowledgeBaseDate` datetime DEFAULT NULL,
`ConfirmDate` datetime DEFAULT NULL,
`EvaluateDate` datetime DEFAULT NULL,
`EvaluateScore` decimal(18,2) DEFAULT NULL,
`EvaluateMemo` varchar(200) DEFAULT NULL,
`CompanyID` int DEFAULT '0',
`CreatedTime` datetime DEFAULT NULL,
`CreatedBy` char(36) NOT NULL,
`ModifiedTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ModifiedBy` char(36) NOT NULL,
`Deleted` bit(1) NOT NULL DEFAULT b'0',
`DeletedTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`DeletedBy` char(36) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `idx_id` (`Id`),
KEY `idx_createdtime` (`CreatedTime`),
KEY `idx_status` (`Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC

2、创建新表（主键列使用uuid自增列）

CREATE TABLE `operation_workorder2` (
`id` BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),
`Code` varchar(50) NOT NULL,
`Type` varchar(50) NOT NULL,
`System` varchar(50) NOT NULL,
`Name` varchar(100) NOT NULL,
`Description` longtext NOT NULL,
`Attachment` varchar(2000) DEFAULT NULL,
`Status` varchar(50) NOT NULL,
`SubmitDate` datetime DEFAULT NULL,
`AssignDate` datetime DEFAULT NULL,
`AssignCode` varchar(50) DEFAULT NULL,
`AssignName` varchar(50) DEFAULT NULL,
`AssignMemo` varchar(500) DEFAULT NULL,
`ToDevelopDate` datetime DEFAULT NULL,
`ToDevelopCode` varchar(50) DEFAULT NULL,
`ToDevelopName` varchar(50) DEFAULT NULL,
`ToRequirementDate` datetime DEFAULT NULL,
`ToRequirementCode` varchar(50) DEFAULT NULL,
`ToRequirementName` varchar(50) DEFAULT NULL,
`SolvePersonCode` varchar(50) DEFAULT NULL,
`SolvePersonName` varchar(50) DEFAULT NULL,
`SolveDate` datetime DEFAULT NULL,
`Grade` varchar(50) DEFAULT NULL,
`QuestionType` varchar(50) DEFAULT NULL,
`IsToKnowledgeBase` varchar(50) DEFAULT NULL,
`ToKnowledgeBaseDate` datetime DEFAULT NULL,
`ConfirmDate` datetime DEFAULT NULL,
`EvaluateDate` datetime DEFAULT NULL,
`EvaluateScore` decimal(18,2) DEFAULT NULL,
`EvaluateMemo` varchar(200) DEFAULT NULL,
`CompanyID` int(11) DEFAULT '0',
`CreatedTime` datetime DEFAULT NULL,
`CreatedBy` char(36) NOT NULL,
`ModifiedTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ModifiedBy` char(36) NOT NULL,
`Deleted` bit(1) NOT NULL DEFAULT b'0',
`DeletedTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`DeletedBy` char(36) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `idx_id` (`Id`),
KEY `idx_createdtime` (`CreatedTime`),
KEY `idx_status` (`Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

3、将源表（operation_workorder）数据导入目标表（operation_workorder2）

INSERT INTO operation_workorder2 (
CODE,
Type,
`System`,
NAME,
Description,
Attachment,
STATUS,
SubmitDate,
AssignDate,
AssignCode,
AssignName,
AssignMemo,
ToDevelopDate,
ToDevelopCode,
ToDevelopName,
ToRequirementDate,
ToRequirementCode,
ToRequirementName,
SolvePersonCode,
SolvePersonName,
SolveDate,
Grade,
QuestionType,
IsToKnowledgeBase,
ToKnowledgeBaseDate,
ConfirmDate,
EvaluateDate,
EvaluateScore,
EvaluateMemo,
CompanyID,
CreatedTime,
CreatedBy,
ModifiedTime,
ModifiedBy,
Deleted,
DeletedTime,
DeletedBy 
) SELECT CODE
,
Type,
`System`,
NAME,
Description,
Attachment,
STATUS,
SubmitDate,
AssignDate,
AssignCode,
AssignName,
AssignMemo,
ToDevelopDate,
ToDevelopCode,
ToDevelopName,
ToRequirementDate,
ToRequirementCode,
ToRequirementName,
SolvePersonCode,
SolvePersonName,
SolveDate,
Grade,
QuestionType,
IsToKnowledgeBase,
ToKnowledgeBaseDate,
ConfirmDate,
EvaluateDate,
EvaluateScore,
EvaluateMemo,
CompanyID,
CreatedTime,
CreatedBy,
ModifiedTime,
ModifiedBy,
Deleted,
DeletedTime,
DeletedBy 
FROM
operation_workorder;

4、完成

插入时间为255s，数据行为：11001518

5、对比MySQL8.0下UUID，自增ID的差别

（1）执行如下语句耗时对比：

原主键UUID非单调：11.25s
主键uuid单调自增：9.95s
主键id单调自增：8.3s

（2）查询语句：

SELECT
`T1`.`Id` AS `Id`,
`T1`.`Code` AS `Code`,
`T1`.`Type` AS `Type`,
`T1`.`System` AS `System`,
`T1`.`Name` AS `Name`,
`T1`.`Attachment` AS `Attachment`,
`T1`.`Status` AS `Status`,
`T1`.`SubmitDate` AS `SubmitDate`,
`T1`.`AssignDate` AS `AssignDate`,
`T1`.`AssignCode` AS `AssignCode`,
`T1`.`AssignName` AS `AssignName`,
`T1`.`SolveDate` AS `SolveDate`,
`T1`.`IsToKnowledgeBase` AS `IsToKnowledgeBase`,
`T1`.`CreatedTime` AS `CreatedTime`,
`T2`.`Name` AS `Creator`,
`T3`.`Name` AS `TypeName`,
`T4`.`Name` AS `SystemName`,
`T5`.`Name` AS `StatusName`,
`T6`.`Name` AS `Grade`,
`T7`.`Name` AS `QuestionType`,
`T8`.`cFullName` AS `CompanyName` 
FROM
`operationproject`.`Operation_WorkOrder` AS `T1`
LEFT JOIN `operationproject`.`Admin_Account` AS `T2` ON ( `T1`.`CreatedBy` = `T2`.`Id` ) 
AND `T2`.`Deleted` = 0 
AND `T2`.`TenantId`
IS NULL LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T3` ON ((((
`T3`.`GroupCode` = 'OperateProject' 
) 
AND ( `T3`.`DictCode` = 'WorkOrderType' )) 
AND ( `T3`.`Extend` = 'zh' )) 
AND ( `T1`.`Type` = `T3`.`Value` ))
LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T4` ON ((((
`T4`.`GroupCode` = 'OperateProject' 
) 
AND ( `T4`.`DictCode` = 'System' )) 
AND ( `T4`.`Extend` = 'zh' )) 
AND ( `T1`.`System` = `T4`.`Value` ))
LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T5` ON ((((
`T5`.`GroupCode` = 'OperateProject' 
) 
AND ( `T5`.`DictCode` = 'WorkOrderStatus' )) 
AND ( `T5`.`Extend` = 'zh' )) 
AND ( `T1`.`Status` = `T5`.`Value` ))
LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T6` ON ((((
`T6`.`GroupCode` = 'OperateProject' 
) 
AND ( `T6`.`DictCode` = 'Grade' )) 
AND ( `T6`.`Extend` = 'zh' )) 
AND ( `T1`.`Grade` = `T6`.`Value` ))
LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T7` ON ((((
`T7`.`GroupCode` = 'OperateProject' 
) 
AND ( `T7`.`DictCode` = 'QuestionType' )) 
AND ( `T7`.`Extend` = 'zh' )) 
AND ( `T1`.`QuestionType` = `T7`.`Value` ))
LEFT JOIN `operationproject`.`Master_Company` AS `T8` ON ( `T1`.`CompanyID` = `T8`.`Id` ) 
AND `T8`.`Deleted` = 0
LEFT JOIN `operationproject`.`Admin_Account_Role` AS `T9` ON ( `T1`.`CreatedBy` = `T9`.`AccountId` ) 
WHERE
( `T1`.`Status` <> '1' ) 
AND (
`T1`.`CreatedTime` >= TIMESTAMP ( '2024-01-01 00:00:00' )) 
AND (
`T1`.`CreatedTime` <= TIMESTAMP ( '2024-01-20 00:00:00' )) 
AND `T1`.`Deleted` = 0 
ORDER BY
`T1`.`Id` DESC;-- LIMIT 0,50;

二、方案2：mysqldump方式

原计划使用mysqldump导出非主键列所有列数据，然后用新表自增列的方式，自动填补新表的ID，但最后发现mysqldump不支持根据列导出。

三、方案3：sed修改mysqldump的内容

计划使用sed替换方式将mysqldump导出的内容进行统一替换，将'LLL01',替换为空,但是发现sed没有模糊替换方式，所以此方式也暂时放弃。

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES ('LLL01',1),('LLL02',2);

本作品采用知识共享署名 4.0 国际许可协议进行许可