插入并设置带有max()+ 1问题的值 [英] Insert and set value with max()+1 problems

问题描述

我正在尝试插入新行，并使用max()+ 1设置customer_id.原因是该表已经在名为id的另一列上具有auto_increatment，并且该表将具有具有相同customer_id的多行.

I am trying to insert a new row and set the customer_id with max()+1. The reason for this is the table already has a auto_increatment on another column named id and the table will have multiple rows with the same customer_id.

与此:

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

...我一直收到以下错误:

...I keep getting the following error:

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

我又如何阻止同时添加2个不同的客户并且没有相同的customer_id?

Also how would I stop 2 different customers being added at the same time and not having the same customer_id?

推荐答案

正确，您不能在同一查询中的同一表中进行修改和选择.您将必须在两个单独的查询中执行上述操作.

Correct, you can not modify and select from the same table in the same query. You would have to perform the above in two separate queries.

最好的方法是使用事务，但是如果您不使用innodb表，那么下一个最好的方法是

The best way is to use a transaction but if your not using innodb tables then next best is locking the tables and then performing your queries. So:

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;

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

Grab the max id and then perform the insert

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

unlock tables;

这篇关于插入并设置带有max()+ 1问题的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！