mysql121_mysql-12序列使用

mysql序列是一组整数：1，2，3....，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段自动增加，就可以使用mysql序列来实现。

使用auto_increment来定义列

drop table if EXISTS test_autoincrement ;

create table `test_autoincrement`(

`id` int UNSIGNED not null auto_increment,

PRIMARY KEY (id),

`name` varchar(20) not null,

`date` date not null,

`origin` varchar(30)

);

insert into test_autoincrement (name,date,origin) values

('housefly','2001-09-10','kitchen');

-- 使用last_insert_id() 可以查看当前操作的次数

select LAST_INSERT_ID();

select * from test_autoincrement;

insert into test_autoincrement values

(null,"millipede",'2001-09-10','driverway'),

(null,"grasshopper",'2001-09-10','front yard');

-- 即便插入多行，也只显示插入第一行时产生的值

select LAST_INSERT_ID();

select * from test_autoincrement;

重置序列

先删除列，再添加列

drop table if EXISTS test_autoincrement ;

create table `test_autoincrement`(

`id` int UNSIGNED not null auto_increment,

PRIMARY KEY (id),

`name` varchar(20) not null,

`date` date not null,

`origin` varchar(30)

);

insert into test_autoincrement (name,date,origin) values

('housefly','2001-09-10','kitchen'),

("millipede",'2001-09-10','driverway'),

("grasshopper",'2001-09-10','front yard');

alter table test_autoincrement drop id;

select * from test_autoincrement;

alter table test_autoincrement add id int(3) UNSIGNED not null auto_increment FIRST,

add primary key (id);

select * from test_autoincrement;

设置序列的开始值

建表时设置序列值

create table `test_autoincrement`

(`id` int(3) UNSIGNED not null auto_increment,

primary key (id),

`name` varchar(20) not null )

engine=innodb auto_increment=100 charset=utf8;

insert into test_autoincrement values

(null,"tom"),

(null,"jerry");

select * from test_autoincrement;