mysql - 插入表时可以使用 select 语句吗？

Question

我有下表:

DROP TABLE IF EXISTS `worklist`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `worklist` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ENTITY_TYPE_CODE` bigint(20) NOT NULL,
  `TYPE_CODE` bigint(20) NOT NUll,
  `NAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


Insert INTO code (DISPLAY, CODE_GROUP, CODE_KEY, ALIAS) VALUES ('Duplicate Claims Worklist', 'WORKLIST_TYPE', 'DUPLICATE_CLAIM_WORKLIST', 'Duplicate Claims Worklist');
INSERT INTO code (DISPLAY, CODE_GROUP, CODE_KEY, ALIAS) VALUES ('Claim', 'ENTITY', 'CLAIM', 'Claim');
INSERT INTO worklist (ENTITY_TYPE_CODE, TYPE_CODE) VALUES (SELECT ID FROM code WHERE CODE_GROUP = 'ENTITY' and CODE_KEY = 'CLAIM', SELECT ID from code where CODE_GROUP = 'WORKLIST_TYPE' and CODE_KEY = 'DUPLICATE_CLAIM_WORKLIST');
ALTER TABLE workitems ADD WORKITEMS_ID bigint(20) DEFAULT NULL;

Answer 1

是的，您可以省略 Values 关键字并执行类似的操作

INSERT INTO code (DISPLAY, CODE_GROUP, CODE_KEY, ALIAS) 
  SELECT DISPLAY, CODE_GROUP, CODE_KEY, ALIAS 
  FROM SomeTable

显然，您需要包含一个 where 子句来控制 select 语句，但基本上就是这样。

编辑:

它看起来像这样(代码未经测试，已直接输入到编辑器中，因此可能有一些拼写错误。)

DROP TABLE IF EXISTS `worklist`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `worklist` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ENTITY_TYPE_CODE` bigint(20) NOT NULL,
  `TYPE_CODE` bigint(20) NOT NUll,
  `NAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


Insert INTO code (DISPLAY, CODE_GROUP, CODE_KEY, ALIAS) VALUES ('Duplicate Claims Worklist', 'WORKLIST_TYPE', 'DUPLICATE_CLAIM_WORKLIST', 'Duplicate Claims Worklist');
INSERT INTO code (DISPLAY, CODE_GROUP, CODE_KEY, ALIAS) VALUES ('Claim', 'ENTITY', 'CLAIM', 'Claim'); 
INSERT INTO worklist (ENTITY_TYPE_CODE, TYPE_CODE) SELECT A.ID, B.ID FROM (SELECT ID, 1 AS J FROM code WHERE CODE_GROUP = 'ENTITY' and CODE_KEY = 'CLAIM') A LEFT JOIN ( SELECT ID, 1 AS J FROM code WHERE CODE_GROUP = 'WORKLIST_TYPE' and CODE_KEY = 'DUPLICATE_CLAIM_WORKLIST') B ON A.J = B.J;
ALTER TABLE workitems ADD WORKITEMS_ID bigint(20) DEFAULT NULL;