MYSQL 字段递增如何按照规律性
来源:百度知道 编辑:UC知道 时间:2024/06/05 18:36:30
mysql 一个字段递增,譬如id递增,他会 1、2、3、4这样递增!
我想实现譬如AB000001 递增AB000002这种规律性递增!
通过mysql设置能实现吗?还是采用php?
触发程序?能否具体说说~
我想实现譬如AB000001 递增AB000002这种规律性递增!
通过mysql设置能实现吗?还是采用php?
触发程序?能否具体说说~
create TRIGGER udt_memberi
ON member
FOR INSERT AS
IF @@rowcount = 0 RETURN
DECLARE @no int
SELECT @no = no
FROM inserted
declare @l int
declare @i int
declare @tmp varchar(6)
declare @tmp1 varchar(6)
declare @max_id varchar(8)
select @max_id=max(id) from member
set @i=0
set tmp1=''
set @tmp=right(@max_id,6)
set @l=cast(@tmp as int)+1
while @i<6-len(@l)
begin
set tmp1=tmp1+'0'
end
UPDATE member set id='TK'+stuff(convert(varchar,@l),1,0,tmp1)
where no=@no
自己手动每次去的 编码字段最大数字ID
sqlserver写法,思路都一样把。
CREATE TABLE #Test
(
num VARCHAR(400)
)
INSERT INTO #Test
SELECT 'A00000101'
UNION ALL
SELECT 'A00000102'
UNION ALL
SELECT 'A00000103'
UNION ALL
SELECT 'A00000104'