Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3676716
  • 博文数量: 146
  • 博客积分: 3918
  • 博客等级: 少校
  • 技术积分: 8582
  • 用 户 组: 普通用户
  • 注册时间: 2010-10-17 13:52
个人简介

个人微薄: weibo.com/manuscola

文章分类

全部博文(146)

文章存档

2016年(3)

2015年(2)

2014年(5)

2013年(42)

2012年(31)

2011年(58)

2010年(5)

分类: Mysql/postgreSQL

2013-11-02 23:20:01

    sequence 是一种特殊的数据库对象,用来产生独一无二的数字ID。Postgres中有一种数据类型serial,和sequence对应:
   
    如果我们插入一笔记录,我们希望系统自动为我们分配一个ID,那么我们需要使用serial type。如果我们创建表的时候,自动创建一个serial类型(包smallserial and bigserial),系统会自动帮我们创建sequence这种数据库对象。
  1. create table employ(
  2.       id serial,
  3.       name varchar(64),
  4.       department varchar(64)
  5. ) ;
  1. manu_db=# create table employ(id serial,name varchar(64),department varchar(128)) ;
    NOTICE:  CREATE TABLE will create implicit sequence "employ_id_seq" for serial column "employ.id"
    CREATE TABLE
    manu_db=# \d
                 List of relations
     Schema |     Name      |   Type   | Owner 
    --------+---------------+----------+-------
     public | employ        | table    | manu
     public | employ_id_seq | sequence | manu
    (2 rows)
  2. manu_db-# \d employ
                                       Table "public.employ"
       Column   |          Type          |                      Modifiers                      
    ------------+------------------------+-----------------------------------------------------
     id         | integer                | not null default nextval('employ_id_seq'::regclass)
     name       | character varying(64)  | 
     department | character varying(128) | 


    我们看到,postgres自动帮助我们创建了一个sequence,命名原则是 tablename_columnname_seq.
   下面这两条SQL,本质是相等:
  1. CREATE TABLE employ (
  2.       id serial,
  3.       name varchar(64),
  4.       department varchar(64)
  5. ) ;

  6. -----------------------------------------------------------------
  7. CREATE SEQUENCE employ_id_seq ;
  8. CREATE TABLE employ (
  9.      id integer NOT NULL DEFAULT nextval('employ_id_seq'),
  10.      name varchar(64),
  11.      department varchar(64)
  12. );

  13. ALTER SEQUENCE employ_id_seq OWNED by employ.id;
    我们看到,我们用了integer数据类型和serial对应起来,默认用nextal(‘employ_id_seq’)给integer类型的id赋值,那是因为integer范围要比serial的范围大,这样做是安全的。对于bigserial,我们应该用bigint类型,对于smallserial应该何smallint对应。
           
    我们建立数据表和sequence的对应关系,常规的就是这两种方法,要么创建表的时候,某字段是serial类型,要么创建先创建sequence,然后创建表的时候,将某字段和sequence 联系起来(DEFAULT nextvl(‘XXXX_seq’)),有没有第三种方法?假如创建表的时候,没有用DEFAULT nextval建立起来联系,还有没有机会在后面建立这种联系?我为什么纠结这个? 
  1. manu_db=# create table employ_copy (like  employ) ;
    CREATE TABLE
    manu_db=# \d employ_copy 
               Table "public.employ_copy"
       Column   |          Type          | Modifiers 
    ------------+------------------------+-----------
     id         | integer                | not null
     name       | character varying(64)  | 
     department | character varying(128) | 


    manu_db=# \d employ
                                       Table "public.employ"
       Column   |          Type          |                      Modifiers                      
    ------------+------------------------+-----------------------------------------------------
     id         | integer                | not null default nextval('employ_id_seq'::regclass)
     name       | character varying(64)  | 
     department | character varying(128) | 


    manu_db=# 

    如果业务需要,创建一个schema和employ一模一样的table,我们只能够用like,但是,我们看到,id这个字段并不完全一样,原因就是没有sequence和新建的table对应。这种情况,我们有没有办法,事后创建sequence,建立table中id字段的联系, I mean  id use nextval as default?
   答案是肯定的:
  1. CREATE SEQUENCE employ_copy_id_seq ;
  2. ALTER TABLE employ_copy ALTER COLUMN id SET DEFAULT nextval('employ_copy_id_seq');
  3. ALTER SEQUENCE employ_copy_id_seq  OWNED by employ_copy.id ;
    看看效果:  
  1. manu_db=# CREATE SEQUENCE employ_copy_id_seq ;
    CREATE SEQUENCE
    manu_db=# ALTER TABLE employ_copy ALTER COLUMN id SET DEFAULT nextval('employ_copy_id_seq');
    ALTER TABLE
    manu_db=# \d employ_copy
                                       Table "public.employ_copy"
       Column   |          Type          |                        Modifiers                         
    ------------+------------------------+----------------------------------------------------------
     id         | integer                | not null default nextval('employ_copy_id_seq'::regclass)
     name       | character varying(64)  | 
     department | character varying(128) | 


     注意,加了alter sequence owned by ,在删除table的时候,会自动的将sequence删掉。如果不建立这种owned by的关系,删除table,不会引发sequence被删除。我就不贴了,可以自己try。

    当我们向Postgres插入一笔记录的时候,我们不需要费力告诉DB id的值,因为DB会自动分配一个数值给id:
  1. manu_db=# insert into  employ (name,department) values('bean', 'DDI');
    INSERT 0 1
    manu_db=# insert into  employ (name,department) values('albert', 'DDI');
    INSERT 0 1
    manu_db=# select * from employ ;
     id |  name  | department 
    ----+--------+------------
      1 | bean   | DDI
      2 | albert | DDI
    (2 rows)

    我们可以用currval 和 nextval取到sequence的当前值和下一个值。注意,currval是不会改变sequence的,但是nextval一旦使用,下个数字就被消耗掉了,哪怕你并没有真正的用于你的table。
  1. manu_db=# select currval('employ_id_seq');
  2.  currval
  3. ---------
  4.        2
  5. (1 row)

  6. manu_db=# select nextval('employ_id_seq');
  7.  nextval
  8. ---------
  9.        3
  10. (1 row)

  11. manu_db=# select nextval('employ_id_seq');
  12.  nextval
  13. ---------
  14.        4
  15. (1 row)
    使用setval可以设置下一个当前值,下一次就会从你设置的这个值开始递增:   
  1. manu_db=# select setval('employ_id_seq',100);
  2.  setval
  3. --------
  4.     100
  5. (1 row)

  6. manu_db=# select currval('employ_id_seq');
  7.  currval
  8. ---------
  9.      100
  10. (1 row)

  11. manu_db=# select nextval('employ_id_seq');
  12.  nextval
  13. ---------
  14.      101
  15. (1 row)

参考文献
1 A Tour of PostgreSQL Data Types
2 Postgres manual 9.1
阅读(9097) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~