MySQL 插入表 select max(column_name)+1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13282116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert into table select max(column_name)+1
提问by Norman
I have this mysql table built like this:
我有这个 mysql 表是这样构建的：
CREATE TABLE `posts` (
`post_id` INT(10) NOT NULL AUTO_INCREMENT,
`post_user_id` INT(10) NOT NULL DEFAULT '0',
`gen_id` INT(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`post_user_id`, `post_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;
When I do:
当我做：
insert into posts (post_user_id) values (1);
insert into posts (post_user_id) values (1);
insert into posts (post_user_id) values (2);
insert into posts (post_user_id) values (1);
select * from posts;
I get:
我得到：
post_id | post_user_id | gen_id
1 1 0
2 1 0
1 2 0
3 1 0
A unique post_id is generated for each unique user.
为每个唯一用户生成唯一的 post_id。
I need the gen_id column to be 1 2 3 4 5 6 etc. How can I increment this column when I do an insert. I tried the one below, but it won't work. What's the right way to do this?
我需要 gen_id 列是 1 2 3 4 5 6 等。我如何在插入时增加此列。我尝试了下面的一个，但它不起作用。这样做的正确方法是什么？
insert into posts (post_user_id,gen_id) values (1,select max(gen_id)+1 from posts);
//Select the highest gen_id and add 1 to it.
回答by Yogendra Singh
Try this:
尝试这个：
INSERT INTO posts (post_user_id,gen_id)
SELECT 1, MAX(gen_id)+1 FROM posts;
回答by GregD
Use a TRIGGERon your table. This sample code can get you started:
TRIGGER在你的桌子上使用 a 。此示例代码可以帮助您入门：
DELIMITER //
CREATE TRIGGER ai_trigger_name AFTER INSERT ON posts
FOR EACH ROW
BEGIN
UPDATE posts
SET gen_id = (SELECT MAX(gen_id) FROM posts) + 1
WHERE post_id = LAST_INSERT_ID()
LIMIT 1;
END;//
DELIMITER ;
回答by Andrea Perdicchia
For my case the first number to increment was null. I resolve with
对于我的情况，要递增的第一个数字为空。我解决了
IFNULL(MAX(number), 0) + 1
or better the query became
或者更好的查询变成
SELECT IFNULL(MAX(number), 0) + 1 FROM mytable;
回答by B.A. Baracus
Here is the table "Autos" and the data that it contains to begin with:
这是表“Autos”及其包含的数据：
AutoID | Year | Make | Model | Color |Seq
1 | 2012 | Jeep |Liberty| Black | 1
2 | 2013 | BMW | 330XI | Blue | 2
The AutoIDcolumn is an auto incrementing column so it is not necessary to include it in the insert statement.
该AutoID列是一个自动递增列，因此不必将其包含在插入语句中。
The rest of the columns are varchars except for the Seqcolumn which is an integer column/field.
除了Seq作为整数列/字段的列之外，其余列都是 varchars 。
If you want to make it so that when you insert the next row into the table and the Seqcolumn auto increments to the # 3 you need to write your query as follows:
如果您想让它在将下一行插入表中并且Seq列自动增加到 #3 时，您需要按如下方式编写查询：
INSERT INTO Autos
(
Seq,
Year,
Make,
Model,
Color,
)
Values
(
(SELECT MAX(Seq) FROM Autos) + 1, --this increments the Seq column
2013,'Mercedes','S550','Black');
The reason that I put the Seqcolumn first is to ensure that it will work correctly... it does not matter where you put it, but better safe than sorry.
我把这个Seq列放在第一位的原因是为了确保它可以正常工作……你把它放在哪里并不重要，但安全总比抱歉好。
The Seqcolumn should now have a value of 3along with the added values for the rest of that row in the database.
该Seq列现在应该有一个值3以及数据库中该行其余部分的添加值。
回答by B.A. Baracus
The way that I intended that to be displayed did not happen...so I will start from the beginning: First I created a table.
我打算显示的方式没有发生......所以我将从头开始：首先我创建了一个表格。
create table Cars (
AutoID int identity (1,1) Primary Key,
Year int,
Make varchar (25),
Model varchar (25),
TrimLevel varchar (30),
Color varchar (30),
CreatedDate date,
Seq int
)
Secondly I inserted some dummy values
其次我插入了一些虚拟值
insert into Cars values (
2013,'Ford' ,'Explorer','XLT','Brown',GETDATE(),1),
(2011,'Hyundai' ,'Sante Fe','SE','White',GETDATE(),2),
(2009,'Jeep' ,'Liberty','Jet','Blue',GETDATE(),3),
(2005,'BMW' ,'325','','Green',GETDATE(),4),
(2008,'Chevy' ,'HHR','SS','Red',GETDATE(),5);
When the insertion is complete you should have 5 rows of data. Since the Seq column is not an auto increment column and you want to ensure that the next Seq's row of data is automatically incremented to the # 6 and its subsequent rows are incremented as well you would need to write the following code:
插入完成后，您应该有 5 行数据。由于 Seq 列不是自动递增列，并且您希望确保下一个 Seq 的数据行自动递增到 # 6 并且其后续行也递增，因此您需要编写以下代码：
INSERT INTO Cars
(
Seq,
Year,
color,
Make,
Model,
TrimLevel,
CreatedDate
)
Values
(
(SELECT MAX(Seq) FROM Cars) + 1,
2013,'Black','Mercedes','A550','AMG',GETDATE());
I have run this insert statement many times using different data just to make sure that it works correctly....hopefully this helps!
我使用不同的数据多次运行这个插入语句，只是为了确保它正常工作......希望这会有所帮助！
