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

Insert and set value with max()+1 problems

提问by Cozzy

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.

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

With this:

有了这个：

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

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

另外，我如何阻止同时添加 2 个不同的客户并且没有相同的 customer_id？

采纳答案by RDL

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.

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

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

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

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;

Grab the max id and then perform the insert

获取最大 id 然后执行插入

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

unlock tables;

回答by EmCo

You can use the INSERT ... SELECTstatement to get the MAX()+1value and insert at the same time:

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

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

^{Note:You need to drop the VALUESfrom your INSERTand make sure the SELECTselected fields match the INSERTdeclared fields.}

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

回答by Sharath Nadig

Use alias name for the inner query like this

像这样为内部查询使用别名

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

回答by OMG Ponies

SELECT MAX(col) +1is not safe -- it does not ensure that you aren't inserting more than one customer with the same customer_idvalue, regardless if selecting from the same table or any others. The proper way to ensure a unique integer value is assigned on insertion into your table in MySQL is to use AUTO_INCREMENT. The ANSI standard is to use sequences, but MySQL doesn't support them. An AUTO_INCREMENT column can only be defined in the CREATE TABLE statement:

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

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`)
)

That said, this worked fine for me on 5.1.49:

也就是说，这在 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;

回答by test

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

回答by Guillaume Renoult

Use table alias in subquery:

在子查询中使用表别名：

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

回答by zipper

None of the about answers works for my case. I got the answer from here, and my SQL is:

没有一个关于答案适用于我的情况。我从这里得到了答案，我的 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())
)

回答by Marc B

You can't do it in a single query, but you could do it within a transaction. Do the initial MAX() select and lock the table, then do the insert. The transaction ensures that nothing will interrupt the two queries, and the lock ensures that nothing else can try doing the same thing elsewhere at the same time.

您不能在单个查询中执行此操作，但可以在事务中执行此操作。执行初始 MAX() 选择并锁定表，然后执行插入。事务确保没有任何东西会中断两个查询，而锁确保没有其他东西可以同时尝试在其他地方做同样的事情。

回答by Alex Shel

We declare a variable 'a'

我们声明一个变量'a'

SET **@a** = (SELECT MAX( customer_id ) FROM customers) +1;

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  (**@a**, 'jim', 'sock')

回答by silverbow25

Mine is an insert, but here is what I ended up with using the suggestions here.

我的是一个插入，但这是我最终在这里使用的建议。

update
   m
set
   m.host_id = max(h.host_id)
from
   t_po_master as m
   inner join t_al_host_po_master as h on m.po_number = h.po_number
where
   m.host_id is NULL
   and m.client_code = '01'

EDIT - This actually didn't work. I'm not certain why not since turning it into a select statement appears that it would work, but this updated all the host_ids to the same id. So I wouldn't use this is a good example.

编辑 - 这实际上没有用。我不确定为什么不把它变成一个 select 语句似乎可以工作，但这会将所有 host_ids 更新为相同的 id。所以我不会使用这是一个很好的例子。