mysql - 如何在MySQL 5.1社区版中生成序列

Question

有人可以告诉我如何在 MySQL 5.1 中生成序列吗？

Answer 1

create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
...
)
engine=innodb;

你也可以这样做

drop table if exists users_seq;
create table users_seq
(
next_val int unsigned not null default 0
)
engine = innodb;

insert into users_seq values (0);

drop table if exists users;
create table users
(
user_id int unsigned not null primary key,
username varbinary(32) unique not null
)
engine=innodb;

delimiter #
create trigger users_before_ins_trig before insert on users
for each row
begin
declare v_id int unsigned default 0;
    select next_val+1 into v_id from users_seq;
    set new.user_id = v_id;
  update users_seq set next_val = v_id;
end#

delimiter ;

insert into users (username) values ('f00'),('foo'),('bar');

select * from users;
select next_val from users_seq;