How to create a table in mysql and set the table primary key id to increase from 1

1. Problem description

When we are working on a project and create a user table, how do we make the primary key id of the table increase from 0? I searched many solutions online and finally found a solution that is sure to be implemented and effective. The following will introduce the implementation method

2. Solution

1. Implementation steps

(1) Create a new table and specify the auto-increment rules (if the table already exists , just delete it and create it again)

(2) Insert a table data with a specified id of 1 into the database

(3) Then insert a table data without specifying the id content Data table, let the id of the table increase automatically

(4) When using mybatisPlus to create and generate entity classes, specify the id self-increment rule of the entity class as the database only increase rule

2. Demonstration

1. Create a new user table and specify the auto-increment rules (if the table already exists, just delete it and create it again)

AUTO_INCREMENT=1, only add the next rule The table record id only increases by 1

create table if not exists `sys_user`
(
`id` bigint(20)  not null auto_increment PRIMARY KEY comment '主键' ,
`opend_id` varchar(256) DEFAULT null comment '微信用户唯一id',
`account_number` varchar(256) DEFAULT null comment'账号',
`username` varchar(256) not null DEFAULT '' comment '用户名',
`password` varchar(256)  comment '密码',
`nick_name` varchar(256) DEFAULT null comment '昵称',
`gender` varchar(25) DEFAULT null comment '性别',
`phone` varchar(256) DEFAULT null comment '手机号',
`role` varchar(10) default '0' not null comment '角色,0为普通用户，1为会员',
`age` varchar(256) not null DEFAULT'110'comment '年龄',
`user_status` varchar(25) default '0' not null comment '状态',
`update_time` datetime  not NULL DEFAULT CURRENT_TIMESTAMP comment '更新时间',
`create_time` datetime  not NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',
`is_deleted` tinyint default 0 not null comment '是否删除(0-未删, 1-已删)',
`email` varchar(256) DEFAULT null comment '邮箱',
`avatar` varchar(256) not null DEFAULT 'https://thirdwx.qlogo.cn/mmopen/vi_32/POgEwh5mIHO4nibH0KlMECNjjGxQUq24ZEaG
T4poC6icRiccVGKSyXwibcPq4BWmiaIGuG1icwxaQX6grC9VemZoJ8rg/132' comment '头像图片',
`city` varchar(50) DEFAULT '北京' comment '城市',
`province` varchar(50) DEFAULT null comment '省份',
`country` varchar(50) comment '国家'
 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 comment '用户表信息';

2. Insert a table data specifying the id of 1 to the table in the database

insert into sys_user values(1,'微信用户唯一id','账号','用户名','mima',
'昵称','xb','sjh','js','nl','0',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,
0,'邮箱','头像图片','城市','省份','国家')

3. Insert another data table without specifying the id content. Let the id of the table increase by itself

insert into sys_user(username,password,country) values('dscdc','8979777','中国');

After these three steps, we open navicat to view the records in the sys_user table in the database

It can be seen that , the generated table ID has been increased according to the rule of only increasing by 1.

Finally, we only need to add an annotation to the id field of the Java entity class, stipulating that when adding a new table, mybatisplus can only increase the id according to the only increase rule of the database table design. The annotation is @TableId(value ="id",type = IdType.AUTO)

@TableId annotation of mybatis-plus

@TableId(value="xxx", type = IdType.xxx):

"value": Set the database field value

"type": Set the primary key type. If the database primary key is set to auto-increment, it is recommended to use "AUTO"

@TableId(value ="id",type = IdType.AUTO)

The complete Java code of the entity class is given below. At this point we have also implemented the function of creating a table id that can be incremented according to the auto-increment rules

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("sys_user")
public class SysUser  {
 
 
//  type = IdType.AUTO id按照数据库里设置的只增规则自己增加
    @TableId(value ="id",type = IdType.AUTO)
    private Long id;
    //用户名
    private String username;
    //账号
    private String accountNumber;
    //密码
    private String password;
    //昵称
    private String nickName;
    //手机号
    private String phone;
    //角色
    private String role;
    //年龄
    private String age;
    //状态
    private String userStatus;
    //更新时间
    private Date updateTime;
    //创建时间
    private Date createTime;
    //是否删除(0-未删, 1-已删)
    private Integer isDeleted;
    //邮箱
    private String email;
    //头像图片
    private String avatar;
    
    private String gender;
    
    private String city;
    
    private String province;
    
    private String country;
 
    private String opendId;
 
}

The above is the detailed content of How to create a table in mysql and set the table primary key id to increase from 1. For more information, please follow other related articles on the PHP Chinese website!