MySQL INSERT在同一张表上使用带有COUNT（）的子查询

问题描述：

我无法正确执行INSERT查询，而且我似乎无法在Google或Stack Overflow上找到解决此特定问题的任何内容。MySQL INSERT在同一张表上使用带有COUNT（）的子查询

我试图为精选条目创建一个简单的表格，其中entry_id与其当前订单一起保存到表格中。

我所需的输出是这样的：

如果featured表目前有以下三项：

featured_id entry_id featured_order 
1    27   0 
2    54   1 
4    23   2

我想下一个条目保存与featured_order = 3。

我试图让下面的查询，没有运气的工作：

INSERT INTO `featured` 
(
    `entry_id`, `featured_order` 
) 
VALUES 
(
    200, 
    (SELECT COUNT(*) AS `the_count` FROM `featured`) 
)

我得到的错误是：You can't specify target table 'featured' for update in FROM clause。

任何人都可以帮助一个解决方案，获得计数而不会导致错误？

在此先感谢！

可能重复http://stackoverflow.com/questions/45494/sql-delete-不能重复的 - 这个问题是删除;不能重复指定目标表中的更新从条款 – Tim 2011-05-27 01:52:06

不是重复的 - 这个问题是删除;这是为了插入。这里根本不适用。 – Bohemian 2011-05-27 03:09:45

答

这是一个很酷的事情：MySQL的INSERT . . . SELECT：

INSERT INTO `featured` 
(
    `entry_id`, `featured_order` 
) 
SELECT 200, COUNT(*) + 1 
FROM `featured`

无需子查询。

@Bohemian好点：

Better to use max(featured_order) + 1 if you use this approach

因此，一个更好的查询可能会是：

INSERT INTO `featured` 
(
    `entry_id`, `featured_order` 
) 
SELECT 200, MAX(`featured_order`) + 1 
FROM `featured`

他的触发方式，在他的回答形容也是完成一个好办法你想要什么。

与查询1中的潜在问题是，如果你删除行的排名将被揭去，你会在featured_order有重复。对于第二个查询，这不是问题，但是您将有空位，就像您使用自动增量列一样。

如果你绝对必须有没有间隙的订单最好的解决办法，我知道的是运行这一系列的查询：

SET @pos:=0; 

DROP TABLE IF EXISTS temp1; 

CREATE TEMPORARY TABLE temp1 LIKE featured; 

ALTER TABLE featured ORDER BY featured_order ASC; 

INSERT INTO temp1 (featured_id, entry_id, featured_order) 
SELECT featured_id, entry_id, @pos:[email protected]+1 FROM words; 

UPDATE featured 
JOIN temp1 ON featured.featured_id = temp1.featured_id 
SET featured.rank = temp1.rank; 

DROP TABLE temp1;

当你删除行

我不认为这会奏效。从['INSERT ... SELECT']（http://dev.mysql.com/doc/refman/5.5/en/insert-select.html）的文档：“但是，您不能插入到表中并选择来自子查询中的同一张表。“ – 2011-05-27 01:59:11

问题与此：a）您将要添加一个计数（*），b）如果编号不与count（*）对齐 - 例如，如果行被删除。如果使用这种方法，最好使用max（featured_order）+ 1 – Bohemian 2011-05-27 01:59:40

@Ted - 在本地数据库上测试它 - 在那里工作得很好。 – jisaacstone 2011-05-27 02:01:52

答

从MySQL manual关于子查询：

Another restriction is that currently you cannot modify a table and select from the same table in a subquery.

也许一个别名或连接（否则无用）在子查询中会有所帮助。

编辑：事实证明，有一种解决方法。解决方法描述为http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/。

“解决问题”涉及到后续的“更新”陈述 - 几乎没有“优雅”。 – Bohemian 2011-05-27 02:07:57

我希望我不暗示“优雅”:) – 2011-05-27 02:16:53

答

使用触发器：

drop trigger if exists featured_insert_trigger; 

delimiter // 
create trigger featured_insert_trigger before insert on featured 
for each row 
begin 
    set new.featured_order = ifnull((select max(featured_order) from featured), -1) + 1; 
end; // 
delimiter ;

现在你的刀片是这样的：

insert into featured (entry_id) values (200);

featured_order将被设置为最高值featured_order加一。这适用于删除/更新的行，并始终保证唯一性。

ifnull在表中没有行的情况下，在这种情况下，第一个值将为零。

此代码已经过正确测试。

我对触发器做了一些研究（我以前从未听说过），这绝对听起来像是最好的解决方案。然而，当我尝试了，我得到这个错误： 'TRIGGER命令拒绝用户“XXXX” @“XXXX”表“featured'' 我已经给MySQL用户‘所有特权’，根据HostGator，所以它看起来像一个触发器不在这个项目的卡片中。：/ – jlengstorf 2011-05-27 18:23:37

这是否工作，如果有两个插入完全在同一时间？ – xms 2017-08-11 08:04:51

@xms从来没有任何更新/插入发生在“完全相同的时间”。数据库串行化所有变异查询。每一个都始终以原子方式开始和完成。 – Bohemian 2017-08-11 10:36:49

答

你必须simpley，将解决这个问题使用别名：

INSERT INTO `featured` 
(
    `entry_id`, `featured_order` 
) 
VALUES 
(
    200, 
    (SELECT COUNT(*) AS `the_count` FROM `featured` as f1) 
)

如果对eith -vote提供的解释会更好，这将有所帮助 – 2011-05-27 06:45:55

错误拼写还是错误答案？ – 2011-05-27 07:29:50

答

INSERT INTO `featured` 
(
    `entry_id`, `featured_order` 
) 
VALUES 
(
    200, 
    (SELECT COUNT(*) AS `the_count` FROM `featured` F1) 
)

修正只是将 “F1” 表的别名。（正如Angelin纳达尔的正上方建议）

这应该是一个多分贝的环境中更好的解决方案，因为同一sintax工作在MySQL + ORACLE + DB2精细

我也建议过的改进：

SELECT COUNT（*）+1（问题：如果行被删除你的错误）
SELECT MAX（featured_order）+1（问题：第一个行误差）

SELECT（COALESCE（MAX（featured_order），0）+1）（没有问题）

我只是想知道...如果有两个插入在同一时间，这工作会好吗？ – xms 2017-08-11 08:05:46

MySQL INSERT在同一张表上使用带有COUNT（）的子查询

相关推荐