mysql - SQL 左连接插入不起作用

Question

我想要得到entity_id catalog_product_entity 中每一行的值表，以及每个store_id catalog_product_entity_int 中每一行的值表，其中entity_id第一个表的等于 entity_id第二个表的内容。

这个 SQL 插入语句有什么问题:

INSERT INTO `catalog_product_entity_varchar` ("","4","231",i.store_id,p.entity_id,"D")
SELECT p.entity_id, i.store_id
                FROM catalog_product_entity p
                LEFT JOIN catalog_product_entity_int i 
                ON i.entity_id = p.entity_id;

MySQL 说:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"","4","231",i.store_id,p.entity_id,"D")
SELECT p.entity_id, i.store_id
                FR' at line 1 

Answer 1

您混淆了两种类型的插入语句。

如果您尝试从一个表中选择值进行插入，它将如下所示:

INSERT INTO `catalog_product_entity_varchar` 
     (field1, field2, field3, field4, field5, field6)
SELECT "","4","231",i.store_id,p.entity_id,"D"
                FROM catalog_product_entity p
                LEFT JOIN catalog_product_entity_int i 
                ON i.entity_id = p.entity_id;

将值的硬编码部分添加到 select 语句中，并指定它们要在插入中进入哪些字段。

阅读 mysql doc page 中的 insert select 语句.