posts - 300,  comments - 1617,  trackbacks - 0

文:阿蜜果

日期:2011-10-16

版权所有,转载请注明出处

1、 何谓数据库主键

数据库主键是指表中一个列或列的组合,其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束,而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据,所以经常用来定义标识列。

1.1 主键的作用

主键的主要作用如下:

1)保证实体的完整性;

2)加快数据库的操作速度;

3 在表中添加新记录时,数据库会自动检查新记录的主键值,不允许该值与其他记录的主键值重复;

4) 数据库自动按主键值的顺序显示表中的记录。如果没有定义主键,则按输入记录的顺序显示表中的记录。

1.2 主键的特点

1)唯一性一个表中只能有一个主键。如果在其他字段上建立主键,则原来的主键就会取消。

2)非空性:主键的值不可重复,也不可为空;

3)必要性:在有些数据库中,虽然主键不是必需的,但最好为每个表都设置一个主键,不管是单主键还是复合主键。它存在代表着表结构的完整性,表的记录必须得有唯一区分的字段,主键主要是用于其他表的外键关联,以及本记录的修改与删除;

4)无意义性:在开发过程中,读者可能会看到将一些表使用有意义的字段表示主键,例如“用户登录信息表”将“登录名”(英文名)作为主键,“订单表”中将“订单编号”作为主键,如此设计主键一般都是没什么问题,因为将这些主键基本不具有“意义更改”的可能性。但是,也有一些例外的情况,例如“订单表”需要支持需求“订单可以作废,并重新生成订单,而且订单号要保持原订单号一致”,那将“订单编号”作为主键就满足不了要求了。因此读者在使用具有实际意义的字段作为主键时,需要考虑是否存在这种可能性。

2、 主键的创建和更改

  在MySQL中,创建时指定某字段为主键的举例如下:

create table article (
id 
int(4primary key not null auto_increment,
name 
varchar(100not null,
author 
varchar(32not null,
publishTime 
datetime default NULL
);

  标示为主键的字段需要使用“primary key”标识。

若要删除某个表中之前的主键,并设置另外一个字段作为主键,参考语句如下:

ALTER TABLE 表名 DROP PRIMARY KEYADD PRIMARY KEY (新的主键字段);

主键可以在执行create table操作时,在字段的后面使用“primary key”指定,也可以不位于字段后面,例如如下语句:

create table ss_schedule
(
    schedulekey            
varchar(30not null,
    schedulename           
varchar(255),
    updatekey              
varchar(30),
    
primary key (schedulekey)
);

 3、 主键的选择以及优缺点比较

3.1 自增主键

       这种方式是使用数据库提供的自增数值型字段作为自增主键,它的优点是:

1)数据库自动编号,速度快,而且是增量增长,按顺序存放,对于检索非常有利;

2)数字型,占用空间小,易排序,在程序中传递也方便;

3)如果通过非系统增加记录时,可以不用指定该字段,不用担心主键重复问题。

其实它的缺点也就是来自其优点,缺点如下:

1)因为自动增长,在手动要插入指定ID的记录时会显得麻烦,尤其是当系统与其它系统集成时,需要数据导入时,很难保证原系统的ID不发生主键冲突(前提是老系统也是数字型的)。特别是在新系统上线时,新旧系统并行存在,并且是异库异构的数据库的情况下,需要双向同步时,自增主键将是你的噩梦;

2)在系统集成或割接时,如果新旧系统主键不同是数字型就会导致修改主键数据类型,这也会导致其它有外键关联的表的修改,后果同样很严重;

3)若系统也是数字型的,在导入时,为了区分新老数据,可能想在老数据主键前统一加一个字符标识(例如“o”,old)来表示这是老数据,那么自动增长的数字型又面临一个挑战。

MySQLauto_increment)、SQL ServerIDENTITY)、InformixOracle(首先创建自增序列,接着为自增主键的表创建插入时的触发器,给自增主键ID赋值)等数据库都支持这种自增主键,这种主键在各种系统中应用广泛,但是如果考虑到有新旧系统并存等问题,为了避免不必要的麻烦,使用自增主键要三思。

3.2 max+1主键

由于增主键存在的缺点,所以有些开发人员就采用自己生成同样是数字型的的主键,但不是系统自动生成的。具体的方式是:在INSERT时,读取主键IDMax值后,加1,这种方法可以避免自动编号的问题,但是也存在各种缺点:

1)效率问题:如果记录非常大的话,那么Max()也会影响效率的;

2)并发性问题:如果同时有两人读到相同的Max后,加一后插入的ID值会重复。

基于这些明显的缺点,笔者不提倡使用此种方式。

3.3 自制+1主键

考虑max+1的效率后,有的开发人员采用自制加1,也就是建一个特别的表,字段为:表名、当前序列值。这样在往表中插入值时,先从此表中找到相应表的最大值后加一,进行插入,但也可能会存在并发处理,但是开发人员可以采用lock线程的方式来避免:在生成此值的时,先Lock,取到值以后,再unLock出来,这样不会有两人同时生成了。这比max+1的速度要快多了。

就算解决了并发的问题,但此种方式同样存在致命的缺点:

1)在与其他系统集成时,脱离了系统中的生成方法后,很麻烦保证自制表中的最大值与导入后的保持一致。

因为此种还需要创建新表,比较麻烦,因此笔者也不推荐此种方式。

3.4 具有实际意义的主键

         有些表可以使用具有实际意义的主键,但这种表为数不多,因为要保证该字段长久的具有行记录唯一的特点,如若有可能变成该表中的非唯一字段,那它就不适合将其变成主键。

         笔者建议有些表可以使用具有实际意义的主键,例如“用户信息表”的“用户登录名”字段基本都是唯一的,而且几乎不可能变成一个登录名对应两条记录,因此可以使用其作为主键。另外,例如“一号通用户信息表”中,“一号通号码”肯定是唯一的,因此也可作为主键。

3.5 GUID主键

目前一个比较好的主键是采用GUIDGlobally Unique Identifier,全球唯一标识符),GUID的特点如下:

(1)   在空间上和时间上具有唯一性,保证同一时间不同地方产生的数字不同;

(2)   世界上的任何两台计算机都不会生成重复的GUID值;

(3)   需要GUID的时候,可以完全由算法自动生成,不需要一个权威机构来管理;

(4)   GUID的长度固定,并且相对而言较短小,非常适合于排序、标识和存储。

可以将GUID主键定义为字符型,但值由GUID生成,GUID是可以自动生成,也可以程序生成,而且键值不可能重复,可以解决系统集成问题,几个系统的GUID值导到一起时,也不会发生重复,就算有“o”老数据也可以区分,而且效率很高。在SQL里也可以使用 NewID()生成。主要优点是:

1)同 IDENTITY 列相比,uniqueidentifier 列可以通过 NewID() 函数提前得知新增加行的ID,为应用程序的后续处理提供很大方便;

2)便于数据库移植,其它数据库中并不一定具有 IDENTITY 列,而 GUID列可以作为字符型列转换到其它数据库中,同时将应用程序中产生的GUID值存入数据库,它不会对原有数据带来影响。

缺点是:

1GUID值较长,不容易记忆和输入,而且这个值是随机、无顺序的。

2GUID的值有16个字节,与其它诸如 4 字节的整数相比要相对大一些。这意味着如果在数据库中使用 uniqueidentifier 键,可能会带来两方面的消极影响:存储空间增大、索引时间较慢。

基于上面的分析,使用GUID的利大于弊,笔者推荐可以采用此种方式。

3.6 自制唯一字符型主键

         为了系统集成等的方便,笔者建议将主键定义成字符型,可以使用GUID作为主键,也可以定义一个字符型的主键字段,但是它的值使用变成编程指定。该种方式的优点是:

1)在异库异构数据库的情况下,若旧系统这些表的主键ID是自增的(数值型),而新系统生成的主键是比较长的字符串型(例如15位),那样旧系统生成或之前的旧数据同步到新系统时,都不会冲突。

这种方式的缺点是:

(1)       需要程序能生成定长的唯一字符串,例如:当前时间+自动机号+进程ID+……;

(2)       由第三方系统生成或手动生成该主键时,处理比较麻烦。

3.7 总结

         在上面几种主键中,较为常用的是“3.1 自增主键”和“3.5 GUID主键”,当在有些特殊的表和某些特定情况中,也可以采用“3.4 具有实际意义的主键”和“3.6 自制唯一的字符型主键”。

4、 参考文档

1)《主键设计用什么字段类型比较好》:

http://www.th7.cn/Article/bc/de/200902/20090220080511.html

2)《主关键字_百度百科》:

http://baike.baidu.com/view/68068.htm

3)《GUID_百度百科》:

http://baike.baidu.com/view/185358.htm

 

posted on 2011-10-16 13:10 阿蜜果 阅读(3375) 评论(4)  编辑  收藏 所属分类: database


FeedBack:
# re: 蜜果私塾:数据库主键的设计和思考
2011-10-17 09:56 | jeffma
太好了,困扰了我很久的主键问题。
我的理解:能使用有意义的键做主键的,就使用有意义的键做主键,常见的如组织机构、用户、权限、角色、菜单等,方便系统初始化时基本数据的导入,如果使用自增长主键,很难设置这些数据关联的外键;不能使用有意义主键,如为了避免复合主键,可以考虑使用自增长或GUID。  回复  更多评论
  
# re: 蜜果私塾:数据库主键的设计和思考
2011-10-17 09:59 | 阿蜜果
@jeffma
其实我是推荐尽量少用有意义的主键,其实可以用这种有意义的主键的表是很少的,例如“用户信息表”的“用户名”等,你说的权限、角色、菜单等其实最好用GUID或自增主键。  回复  更多评论
  
# re: 蜜果私塾:数据库主键的设计和思考
2011-10-17 22:09 | tb
恩 很不错   回复  更多评论
  
# re: 蜜果私塾:数据库主键的设计和思考
2011-10-20 17:54 | jeffma
像组织机构、菜单这些都是树状结构的数据,可能会在系统初始化时先以SQL插入,但是如果主键使用GUID或自增主键其中的parent字段如何填写?

code name parent
14010000 省公司 null
14010100 xx市 14010000
14010200 yy市 14010000
14010101 xx县 14010100  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航:
 
<2011年10月>
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

      生活将我们磨圆,是为了让我们滚得更远——“圆”来如此。
      我的作品:
      玩转Axure RP  (2015年12月出版)
      

      Power Designer系统分析与建模实战  (2015年7月出版)
      
     Struts2+Hibernate3+Spring2   (2010年5月出版)
     

留言簿(252)

随笔分类

随笔档案

文章分类

相册

关注blog

积分与排名

  • 积分 - 2043407
  • 排名 - 3

最新评论

阅读排行榜

评论排行榜