小编典典

插入表中,选择max(column_name)+1

sql

我有这样构建的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;

当我做:

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;

我得到:

post_id | post_user_id | gen_id
1                  1     0
2                  1     0
1                  2     0
3                  1     0

为每个唯一用户生成一个唯一的post_id。

我需要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.

阅读 219

收藏
2021-03-10

共1个答案

小编典典

试试这个:

  INSERT INTO posts (post_user_id,gen_id) 
  SELECT 1, MAX(gen_id)+1 FROM posts;
2021-03-10