Summary of how to use sequence Sequence in MySQL

This article brings you relevant knowledge about mysql. The sequence is a set of integers such as 1, 2, 3,...In order to be generated when needed, the following is an introduction to MySQL I hope this will be helpful to everyone.

Recommended learning: mysql video tutorial

If you want a continuous self-increasing data type value in the Oracle database , which can be achieved by creating a sequence. There is no sequence in the MySQL database. Usually if a table only needs one auto-increment column, then we can use MySQL's auto_increment (a table can only have one auto-increment primary key). If we want to use sequences in MySQL like Oracle, how should we do it?

For example, there is the following table definition:

create table `t_user`(
    `id` bigint auto_increment primary key,
    `user_id` bigint unique comment '用户ID',
    `user_name` varchar(10) not null default '' comment '用户名'
);

where user_id requires auto-increment, order and uniqueness. There are many implementation methods, such as the snowflake algorithm, using Redis or Zookeeper, etc. to obtain a value that meets the conditions. I will not introduce them one by one here. Here we introduce how to use MySQL's auto_increment and last_insert_id() to implement a sequence similar to that in Oracle.

Method 1. Use stored procedures

1. Create a simple table containing an auto-incrementing primary key.

Examples are as follows:

create table `t_user_id_sequence` (
    `id` bigint not null auto_increment primary key,
    `t_text` varchar(5) not null default '' comment 'insert value'
);

2. Create a stored procedure

delimiter &&
create procedure `pro_user_id_seq` (out sequence bigint)
begin
    insert into t_user_id_sequence (t_text) values ('a');
    select last_insert_id() into sequence from dual;
    delete from t_user_id_sequence;
end &&
delimiter ;

3. Test

call pro_user_id_seq(@value);
select @value from dual;

To use a stored procedure, you need to call the stored procedure once and then Assignment is a little troublesome.

Method 2. Use function

1. Create a function that generates sequence

delimiter &&
create function user_id_seq_func() returns bigint
begin
    declare sequence bigint;
    insert into t_user_id_sequence (t_text) values ('a');
    select last_insert_id() into sequence from dual;
    delete from t_user_id_sequence;
    return sequence;
end &&
delimiter ;

2. Test

select user_id_seq_func() from dual;
 
insert into t_user (user_id, user_name) values (user_id_seq_func(), 'java');
select * from t_user;

Recommended learning: mysql video Tutorial

The above is the detailed content of Summary of how to use sequence Sequence in MySQL. For more information, please follow other related articles on the PHP Chinese website!