mysql - 如何将 INSERT ... SELECT 与特定列自动递增(从 1 开始)一起使用？

Question

我正在使用INSERT ... SELECT将 View 中特定行的特定列的数据插入到表中。这是目标表:

CREATE TABLE IF NOT EXISTS `queue` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `customerId` int(11) NOT NULL,
  `productId` int(11) NOT NULL,
  `priority` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `customerId` (`customerId`),
  KEY `productId` (`productId`),
  KEY `priority` (`priority`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

我的 INSERT ... SELECT SQL 可以工作，但如果可能的话我想改进它，如下所示:我希望插入的行在优先级列中以 1 开头，并且每个后续行都增加优先级值减 1。因此，如果插入三行，第一行优先级为 1，第二行优先级为 2，第三行优先级为 3。

“从 1 开始”规则的一个异常(exception):如果目标表中存在指定客户的现有行，我希望插入的行以 MAX(priority)+1 开头对于该客户。

我以为我可以使用子查询，但问题是:有时子查询返回 NULL(当 queue 表中没有指定客户的记录时)，这会中断插入，如下所示priority 列不允许为空。

我尝试将该列转换为整数，但当表中没有包含该客户 ID 的记录时，仍然返回 NULL。

在此示例中，我已对客户 ID 进行了硬编码，但在我的应用程序中，这自然会成为输入参数。

INSERT INTO `queue`
(
`customerId`,
`productId`,
`priority`,
`status`,
`orderId`)
SELECT
    123, -- This is the customer ID
    `PRODUCT_NO`,
    (SELECT (MAX(`priority`)+1) FROM `queue` WHERE `customerId` = 123),
    'queued',
    null
FROM
    `queue_eligible_products_view` 

有没有办法在一个 SQL 语句或少量 SQL 语句(即每行少于 SQL 语句)中执行此操作？

我不认为我可以将优先级列设置为auto_increment，因为该列不一定是唯一的，并且auto_increment属性是used to generate a unique identity for new rows .

Answer 1

如Barmar评论中提到:使用 IFNULL 来处理返回 null 的子查询。因此:

INSERT INTO `queue`
(
`customerId`,
`productId`,
`priority`,
`status`,
`orderId`)
SELECT
    123, -- This is the customer ID
    `PRODUCT_NO`,
    IFNULL((SELECT (MAX(`priority`)+1) FROM `queue` WHERE `customerId` = 123),1),
    'queued',
    null
FROM
    `queue_eligible_products_view`