如何使用INSERT ... SELECT从1开始的特定列自动递增？ [英] How to use INSERT ... SELECT with a particular column auto-incrementing, starting at 1?

问题描述

我正在使用插入...选择将视图中特定行中特定列的数据插入表中。目标表如下：

I am using INSERT ... SELECT to insert a data from specific columns from specific rows from a view into a table. Here's the target table:

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，第二行将是优先级1，第三个3。

The INSERT ... SELECT SQL I have works, but I would like to improve it if possible, as follows: I would like the inserted rows to start with 1 in the priority column, and each subsequent row to increment the priority value by 1. So, if three rows were inserted, the first would be priority 1, the second 2, and the third 3.

从1开始规则的例外：如果目标客户的目标表中已有行，我希望插入的行为该客户以 MAX（priority）+1 开头。

A exception to the "start at 1" rule: if there are existing rows in the target table for the specified customer, I would like the inserted rows to start with MAX(priority)+1 for that customer.

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

I thought I could use a subquery, but here's the problem: sometimes the subquery returns NULL (when there are no records in the queue table for the specified customer), which breaks the insert, as the priority column does not allow nulls.

我尝试将列转换为整数，但是当有没有该custo的记录

I tried to CAST the column to an integer, but that still gave me NULL back when there are no records with that customer ID in the table.

在此示例中，我已经对客户ID进行了硬编码，但是在我的应用程序中自然可以将其作为输入参数。

I've hardcoded the customer ID in this example, but naturally in my application that would be an input parameter.

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语句？

Is there a way to do this in one SQL statement, or a small number of SQL statements, i.e., less than SQL statement per row?

我不认为可以将优先级列设置为auto_increment，因为该列不一定是唯一的，并且auto_increment属性为用于为新行生成唯一标识。

I do not think I can set the priority column to auto_increment, as this column is not necessarily unique, and the auto_increment attribute is used to generate a unique identity for new rows.

推荐答案

在此处进行增量操作：

INSERT INTO queue (customerId, productId, priority, status, orderId)
SELECT 123, product_no, @priority := @priority + 1, 'queued', null
FROM queue_eligible_products_view
JOIN (SELECT @priority := IFNULL(MAX(priority), 0)
      FROM queue
      WHERE customerId = 123) var

这篇关于如何使用INSERT ... SELECT从1开始的特定列自动递增？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！