MySQL中的增量字段 [英] increment field in mysql

问题描述

大家好，

我在mysql中有一个名为"dbtrial"的数据库，在此数据库中，有一个名为"test"的表
在测试表中，我只有两个栏目"BranchId"，"BranchName"，其中BranchId是主键
我想每次输入记录时都将BranchId字段加1.为此，我编写了如下代码.....

Hello all,

i have a databse in mysql named as"dbtrial" and in this database i have a table named as"test"
in test table i have only two coloumns "BranchId","BranchName" where BranchId is the primary key
i want tha every time i enter the records the BranchId field should be incremented by 1. for this i write a code which is as follows........

use dbtrial;
create procedure sp_insert5
(
 in branchid int(11),
in branchname varchar(50)
)
 select max(branchid)+1  into @branchid from test;
insert into test (branchid,branchname)
values(branchid,branchname);
call sp_insert5(
'zeroerror1')

但是我运行它时遇到了问题


(受影响的0行)
(耗时0毫秒)

错误代码:1062
键"PRIMARY"的条目"0"重复
(耗时0毫秒)

我不想使用自动增量.
请帮帮我

谢谢和问候
subiya

but i got the proble when i run it


(0 row(s)affected)
(0 ms taken)

Error Code : 1062
Duplicate entry ''0'' for key ''PRIMARY''
(0 ms taken)

i dont want to use autoincrement.
please help me out

thanks and regard
subiya

推荐答案

我可以看到两个问题.
1).您正在尝试为"in"参数分配一个值.可能会失败.
2).当表为空时，第一个选择将返回NULL值.您根本没有处理这种情况.建议您将选择更改为

Two issues I can see.
1). You are trying to assign a value to an ''in'' parameter. It''s possible that is failing.
2). When the table is empty, the very first select is going to return a NULL value. You are not handling this situation at all. Suggest you change your select to

select max(nvl(branchid,0))+1 into ....

:)

这篇关于MySQL中的增量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！