目录
案例1—MySQL8.0-将表主键UUID改为自增列UUID
注:有多种方案
一、方案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);