【无标题】

背景

通过Django模型创建的数据表，含有外键和索引，在迁移测试数据时新建的表不含索引，创建命令如下：

create table requireedit_bak as select * from requireedit

该命令只复制数据，不复制表属性

DDL源码

目的表NetworkEngineering_budgetlogic 的DLL：

CREATE TABLE `budgetlogic` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Logicname` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `Brandclass` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `Brandname` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `Multiply` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `Weight` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `Classifynum` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `Brandhidden` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `Brandsort` int NOT NULL,
  `Logicdesc` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  `CreatemanId` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `Createdate` datetime(6) NOT NULL,
  `Parentbrandid_id` int DEFAULT NULL,
  `Brandmodel` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `Brandmodelid` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `Gradestatus` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `CreatemanName` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`,`Brandsort`) USING BTREE,
  KEY `NetworkEngineering_b_Parentbrandid_id_cdec6862_fk_NetworkEn` (`Parentbrandid_id`) USING BTREE,
  KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=714 DEFAULT CHARSET=utf8mb3;

观察发现，该表中包含主键id、Brandsort，索引键Parentbrandid_id、id

--创建主键
ALTER TABLE `networkengineering_budgetlogic` ADD PRIMARY KEY (`id`,`Brandsort`) USING BTREE

--创建索引键，（NetworkEngineering_b_Parentbrandid_id_cdec6862_fk_NetworkEn 是外键）
CREATE INDEX NetworkEngineering_b_Parentbrandid_id_cdec6862_fk_NetworkEn  ON networkengineering_budgetlogic (Parentbrandid_id);
CREATE INDEX id  ON NetworkEngineering_budgetlogic (id);

目的表requireedit的DLL：

CREATE TABLE `NetworkEngineering_requireedit` (
  `id` int NOT NULL AUTO_INCREMENT,
  `RequireNum` varchar(100) NOT NULL,
  `ProjectName` longtext NOT NULL,
  `ProjectNum` varchar(50) NOT NULL,
  `Requiredesc` longtext,
  `CreateManId` varchar(50) DEFAULT NULL,
  `Createdate` datetime(6) NOT NULL,
  `LogicName_id` int DEFAULT NULL,
  `Area` varchar(100) NOT NULL,
  `Build` varchar(100) NOT NULL,
  `Factory` varchar(100) NOT NULL,
  `Floor` varchar(100) NOT NULL,
  `MachineRoom` varchar(1000) NOT NULL,
  `Fromdata` longtext,
  `Requirename` varchar(100) DEFAULT NULL,
  `Todata` longtext,
  `TodataSelect` longtext,
  `TodataPidSelect` longtext,
  `TodataNumSelect` longtext,
  `CreatemanName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NetworkEngineering_r_LogicName_id_d9b81f04_fk_NetworkEn` (`LogicName_id`),
  CONSTRAINT `NetworkEngineering_r_LogicName_id_d9b81f04_fk_NetworkEn` FOREIGN KEY (`LogicName_id`) REFERENCES `NetworkEngineering_budgetlogic` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=411 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

观察发现，该代码段包含主键id，索引键LogicName_id，LogicName_id索引键关联NetworkEngineering_budgetlogic表的id字段

--创建主管
ALTER TABLE `NetworkEngineering_requireedit` ADD PRIMARY KEY (`id`) USING BTREE

--创建索引键
CREATE INDEX NetworkEngineering_r_LogicName_id_d9b81f04_fk_NetworkEn  ON NetworkEngineering_requireedit (LogicName_id);

--关联索引networkengineering_budgetlogic的id
ALTER TABLE networkengineering_requireedit ADD CONSTRAINT `NetworkEngineering_r_LogicName_id_d9b81f04_fk_NetworkEn` FOREIGN KEY (`LogicName_id`) REFERENCES `networkengineering_budgetlogic` (`id`)

两张表的索引及外键添加完成

ps：不能先创建表，在copy数据，因为中间有用到外键