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

2021-11-20 00:00:00 sql insert mysql mysql-error-1093

我正在尝试插入一个新行并使用 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;

相关文章