How to generate sequence in MySQL 5.1community edition

Can somebody please show m开发者_如何学Goe how generate a sequence in MySQL 5.1?

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

you could also do

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;

MySQL doesn't have sequences, however it does have the auto-increment feature.

http://www.experts123.com/q/does-mysql-5.1-have-sequences.html

CREATE TABLE test (
  customer_id bigint(21) NOT NULL PRIMARY KEY AUTO_INCREMENT 
);