数据库设计-查找表
你如何标记你的“代码”，序列号（1,2,3,4...n）？
示例
我的 DeviceType 查找表保存移动设备，并具有以下属性
Id
Code
Name
how do you label your "code", sequencial number (1,2,3,4...n)?
Example
My DeviceType lookup table holds mobile devices, and has the following attributes
Id
Code
Name
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果您可以容忍序列中的间隙，您可以使用其他答案中的任何建议，也可以使用 Oracle 中的
SEQUENCE、IDENTITYIBM DB2 中的自动增量字段或 MS SQL Server 中的自动增量字段。但是，如果您绝对需要避免间隙，即使在并发环境中，事情也会突然变得不那么简单。本质上，您必须识别“候选”值，然后尝试插入它，然后准备好重试整个过程（如果并发事务在您之前插入它）。
顺便说一句，您可以使用
code作为主键并完全删除Id吗？If you can tolerate gaps in the sequence, you can use any of the suggestions in other answers and you can also use
SEQUENCEin Oracle,IDENTITYin IBM DB2 or auto-increment fields in MS SQL Server.If, however, you absolutely need to avoid gaps, even in the concurrent environment, things suddenly become much less straightforward. In essence, you gonna have to identify the "candidate" value, then try to insert it and then be prepared to retry the whole process if a concurrent transaction inserted it before you.
BTW, could you use
codeas primary key and drop theIdaltogether?在 MySQL 中，您可以将字段（即列）描述为“自动增量”，这将在您向表中插入新闻行时更新您的
code字段。我相信这对于许多（如果不是全部）SQL 系统来说都是常见的。看看这里< /a> 获取 MySQL 操作的描述。In MySQL, you can characterize fields (i.e., columns) as "auto-increment," which will update your
codefield as you insert news rows into the table. I believe this is common to many, if not all, SQL systems. Have a look over here for a description of the MySQL operation.在postgreSQL中，列类型将是serial（或bigserial）来完成同样的事情。然后在您的插入中保留串行列，数据库引擎将为您自动增量，正如皮特的优秀链接所述。
In postgreSQL the column type would be serial ( or bigserial ) to accomplish the same thing. Then in your inserts leave the serial column out and the db engine will auto-increment for you as Pete's excellent link said.
我们大多数人不会构建使用 ID 号（通常是连续的）和附加连续代码的表。没有意义。
如果 code 是连续的、唯一的数字，则删除 id 列并将 code 作为主键。
Most of us don't build tables that use an id number--which is usually sequential--and an additional code that's also sequential. There's no point to it.
If code is a sequential, unique number, drop the id column and make code the primary key.