数据库中有多张表,如何使得ID不重复呢?
1、创建表tbl_sequence
CREATE TABLE tbl_sequence
(
seq_name VARCHAR(50) NOT NULL,
minval INT NOT NULL,
maxval INT NOT NULL,
current_val INT NOT NULL,
increment_val INT DEFAULT '1' NOT NULL,
PRIMARY KEY (seq_name)
)
(
seq_name VARCHAR(50) NOT NULL,
minval INT NOT NULL,
maxval INT NOT NULL,
current_val INT NOT NULL,
increment_val INT DEFAULT '1' NOT NULL,
PRIMARY KEY (seq_name)
)
2、设置默认值
insert into tbl_sequence (seq_name, minval, maxval, current_val, increment_val)
values ('seq_no', 1, 99999999, 1, 1);
values ('seq_no', 1, 99999999, 1, 1);
3、自定义函数
create function _nextval(name varchar(50))
returns integer
begin
declare _cur int;
declare _maxvalue int; -- 接收最大值
declare _increment int; -- 接收增长步数
set _increment = (select increment_val from tbl_sequence where seq_name = name);
set _maxvalue = (select maxval from tbl_sequence where seq_name = name);
set _cur = (select current_val from tbl_sequence where seq_name = name);
update tbl_sequence -- 更新当前值
set current_val = _cur + increment_val
where seq_name = name ;
if(_cur + _increment >= _maxvalue) then -- 判断是都达到最大值
update tbl_sequence
set current_val = minval
where seq_name = name ;
end if;
return _cur;
end;
returns integer
begin
declare _cur int;
declare _maxvalue int; -- 接收最大值
declare _increment int; -- 接收增长步数
set _increment = (select increment_val from tbl_sequence where seq_name = name);
set _maxvalue = (select maxval from tbl_sequence where seq_name = name);
set _cur = (select current_val from tbl_sequence where seq_name = name);
update tbl_sequence -- 更新当前值
set current_val = _cur + increment_val
where seq_name = name ;
if(_cur + _increment >= _maxvalue) then -- 判断是都达到最大值
update tbl_sequence
set current_val = minval
where seq_name = name ;
end if;
return _cur;
end;
4、查询函数
select _nextval('seq_no')
5、在插入的时候,插入语句就可以这么写:
INSERT INTO t_member_info (id, member_name)
VALUES ( _nextval('seq_no'), 'zxd123');
6、如此一来,数据库中的表ID就不会重复了!!!