使用max()+1插入和设置值的问题

Question

我尝试插入一行新数据，并使用max()+1设置customer_id。这样做的原因是该表中另一个列名为id的列已经有了自动增长，因此该表将会有多行具有相同的customer_id。

以下是实现代码:

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers) +1, 'jim', 'sock')

我一直收到以下错误：

#1093 - You can't specify target table 'customers' for update in FROM clause

我应该如何防止同时添加两个不同的客户并且他们没有相同的customer_id?

- Cozzy

Answer 1

您可以使用INSERT ... SELECT语句同时获取MAX()+1值并插入:

INSERT INTO 
customers( customer_id, firstname, surname )
SELECT MAX( customer_id ) + 1, 'jim', 'sock' FROM customers;

^{注意：您需要从INSERT语句中删除VALUES，并确保SELECT所选字段与INSERT声明的字段匹配。}

- emco

---

如果这些查询同时运行，可能会导致竞态条件。在此之前，您需要使用“lock table”命令锁定表。 - xmedeko

@xmedeko 我认为一个查询总是原子的，所以既然这里只有一个 INSERT，那么它就是一个原子操作并且是竞争安全的？ - Xenos

不行，至少对于MySQL来说不行。这个查询语句先进行选择然后再进行插入操作。它们并不是原子性的。存在轻微的竞态条件可能性。 - xmedeko

今天我学到了新的东西。我以为在一个查询中执行所有操作时，重复的查询是不可能的，但是看来我错了。现在是时候重新编写一些内容，以避免未来可能出现的问题了。 - Fusseldieb

我有一个类似的情况，可能需要开一个单独的SO问题；选择max()是我正在寻找的方法，但在阅读上面的评论后，我开始质疑自己。在我的实例中，我有一个WHERE子句，它限制了我的返回结果到一个特定的子集，所以我认为我相对安全，不会出现竞态条件...对吧？ - HPWD

Answer 2

正确的，你不能在同一查询中修改并从同一张表中选择。你需要分别在两个单独的查询中执行上述操作。

最好的方法是使用事务，但如果您不使用innodb表，则下一个最佳方法是锁定表然后执行您的查询。所以：

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;

获取最大的ID，然后执行插入操作

INSERT INTO customers( customer_id, firstname, surname )
VALUES ($max+1 , 'jim', 'sock')

unlock tables;

- RDL

---

@RDL - 我正在使用MyISAM，因此您的示例将正常工作，并允许同时添加具有不同客户ID的2个客户？谢谢。 - Cozzy

@Cozzy，正确。当表被锁定时，只有当前会话才能访问它。所有对该表的其他请求都将排队等待，直到该表被解锁。因此，在同时添加记录时不会发生重复。 - RDL

如果在customer_id列上没有唯一键来验证，那么不能保证重复值不存在。此外，由于隔离级别处理，插入语句可能会读取相同的最大值。为什么不使用本地的AUTO_INCREMENT？ - OMG Ponies

@Cozzy：你DECLARE了@max变量吗？为什么你想要这种方法让人困惑。 - OMG Ponies

@Cozzy：如果你认为这是审计表的正确方式，我对你的数据模型感到担忧。 - OMG Ponies

Answer 3

像这样在内部查询中使用别名

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id )+1 FROM customers cust), 'sharath', 'rock')

- Sharath Nadig

---

@xmedeko 这根本不是重复的。EmCos的答案展示了如何用SELECT替换VALUES()，而这个答案展示了如何在VALUES()内嵌套SELECT语句（读EmCos答案的人可能会认为这是不可能的，因为他的“注释”）。 - Wipqozn

@Wipqozn 你是对的，这不是重复的，但应该在答案中提到有什么不同。此外，我关于竞态条件的评论也适用于这个答案。 - xmedeko

Answer 4

在子查询中使用表别名：

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers C) +1, 'jim', 'sock')

- Guillaume Renoult

---

我认为应该是这样的： INSERT INTO customers ( customer_id, firstname, surname ) VALUES ((SELECT MAX( C.customer_id ) FROM customers C) +1, 'jim', 'sock') 但是以下语句在我的安装中可以工作[mySQL ver 5.5.41]： INSERT INTO table1(id1) SELECT (max(id1)+1) FROM table1; - ReverseEMF

Answer 5

SELECT MAX(col) +1 不安全 -- 它不能确保您插入的多个客户记录中不会出现相同的 customer_id 值，无论是从同一张表还是其他表中选择。在 MySQL 中确保在插入表时分配唯一整数值的正确方法是使用 AUTO_INCREMENT。 ANSI 标准是使用序列，但 MySQL 不支持它们。只能在 CREATE TABLE 语句中定义 AUTO_INCREMENT 列：

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
)

话虽如此，但我在5.1.49上测试过，这个方案完全可行：

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

INSERT INTO customers VALUES (1, 'a', 'b');

INSERT INTO customers 
SELECT MAX(customer_id) + 1, 'jim', 'sock'
  FROM CUSTOMERS;

- OMG Ponies

---

这如何确保您不会选择多行？除非有 group by，否则我想不到使用 max() 的查询会返回多行的情况。 - Brian Leishman

他并不是在谈论使用max()检索多行数据，而是正确地指出，通过使用max(customer_id) + 1来插入新客户，您无法确定该ID的唯一性。同时执行两个这样的语句将导致两个不同的客户具有相同的customer_id。 - jastigarraga

Answer 6

insert into table1(id1) select (max(id1)+1) from table1;

- test

---

不适用于MySQL，它不允许在INSERT和SELECT子查询中使用相同的表。 - sbrbot

适用于我使用的mySQL版本5.5.41。 - ReverseEMF

Answer 7

以上所有答案都不适用于我的情况。我从这里得到了答案，我的SQL语句如下：

INSERT INTO product (id, catalog_id, status_id, name, measure_unit_id, description, create_time)
VALUES (
  (SELECT id FROM (SELECT COALESCE(MAX(id),0)+1 AS id FROM product) AS temp),
  (SELECT id FROM product_catalog WHERE name="AppSys1"),
  (SELECT id FROM product_status WHERE name ="active"),
  "prod_name_x",
  (SELECT id FROM measure_unit WHERE name ="unit"),
  "prod_description_y",
  UNIX_TIMESTAMP(NOW())
)

- zipper

Answer 8

你的子查询只是不完整，仅此而已。请查看下面的查询语句及我所添加的内容：

INSERT INTO customers ( customer_id, firstname, surname ) 
VALUES ((SELECT MAX( customer_id ) FROM customers) +1), 'jim', 'sock')

- saikrishna

Answer 9

这是选择，插入 sequel。

我正在尝试获取 serial_no 的最大 +1 值，并且它正在给出正确的值。

SELECT MAX(serial_no)+1 into @var FROM sample.kettle;
Insert into kettle(serial_no,name,age,salary) values (@var,'aaa',23,2000);

- kani sindhu

Answer 10

你无法在单个查询中完成它，但是你可以在事务内部完成它。先进行MAX（）选择并锁定表，然后进行插入操作。事务确保没有其他操作会打断这两个查询，而锁定确保没有其他地方尝试在同一时间做相同的事情。

- Marc B

---

请提供一个 SQL 查询的示例吗？当使用事务和锁时，如果同时添加了两个客户，是否会抛出任何错误，或者简而言之，它只是将它们排队并依次运行？谢谢。 - Cozzy

实际上，你可以在单个语句中完成操作——无需事务。而且 SELECT MAX 不保证始终返回唯一值——在几乎每个数据库的默认隔离级别中，读取查询优先于更新查询。 - OMG Ponies

仅仅进行事务是不够的，你必须先锁定表。 - xmedeko