POSTGRESQL和 MYSQL的自增字段比较

lihanchuan125

最近学习PGSQL。来比较一下他和MYSQL自增字段的不同点。　　1、自增序列。MYSQL从最后一个ID自增。
　　测试数据。
　　1, I love this girl.
　　2, I hate this girl.
　　3, She is my girl.
　　4, She is your girl.
　　MYSQL：
　　mysql> create database test;
　　Query OK, 1 row affected (0.10 sec)
　　mysql> use test
　　Database changed
　　mysql> create table t(id int not null auto_increment primary key,
　　-> username char(20) not null);
　　Query OK, 0 rows affected (0.02 sec)
　　mysql> load data infile '/tmp/test.sql' into table t fields terminated by ',';
　　Query OK, 4 rows affected (0.00 sec)
　　Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
　　mysql> select * from t;
　　+----+-------------------+

　　|>　　+----+-------------------+
　　| 1 | I love this girl. |
　　| 2 | I hate this girl. |
　　| 3 | She is my girl. |
　　| 4 | She is your girl. |
　　+----+-------------------+
　　4 rows in set (0.00 sec)
　　mysql> insert into t values (6,'This is inserted');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> insert into t(username) values('This is last');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> select * from t;
　　+----+-------------------+

　　|>　　+----+-------------------+
　　| 1 | I love this girl. |
　　| 2 | I hate this girl. |
　　| 3 | She is my girl. |
　　| 4 | She is your girl. |
　　| 6 | This is inserted |
　　| 7 | This is last |
　　+----+-------------------+
　　mysql> truncate table t;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> insert into t(username) values('This is last');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> insert into t(username) values('This is last');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> insert into t(username) values('This is last');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> select * from t;
　　+----+--------------+

　　|>　　+----+--------------+
　　| 1 | This is last |
　　| 2 | This is last |
　　| 3 | This is last |
　　+----+--------------+
　　3 rows in set (0.00 sec)
　　PGSQL从1开始逐个尝试。
　　[root@localhost ~]# psql -Upostgres -hlocalhost
　　。。。
　　postgres=# create database test;
　　CREATE DATABASE
　　postgres=# \c test
　　You are now connected to database "test".
　　test=# create table t(id serial not null,username char(20) not null);
　　NOTICE: CREATE TABLE will create implicit sequence "t_id_seq" for serial column "t.id"
　　CREATE TABLE
　　test=# \d t;
　　Table "public.t"
　　Column | Type | Modifiers
　　----------+---------------+------------------------------------------------
　　id | integer | not null default nextval('t_id_seq'::regclass)
　　username | character(20) | not null
　　test=# copy t from '/tmp/test.sql' with csv;
　　COPY 4
　　test=# select * from t;
　　id | username
　　----+----------------------
　　1 | I love this girl.
　　2 | I hate this girl.
　　3 | She is my girl.
　　4 | She is your girl.
　　(4 rows)
　　test=# insert into t values (6,'This is inserted');
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　ID1重复
　　ERROR: duplicate key violates unique constraint "t_pkey"
　　test=# insert into t(username) values('This is last');
　　ID2重复
　　ERROR: duplicate key violates unique constraint "t_pkey"
　　test=# insert into t(username) values('This is last');
　　。。。
　　ID5没有。插入
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　ID6又重复
　　ERROR: duplicate key violates unique constraint "t_pkey"
　　test=# insert into t(username) values('This is last');
　　...
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# select * from t;
　　id | username
　　----+----------------------
　　1 | I love this girl.
　　2 | I hate this girl.
　　3 | She is my girl.
　　4 | She is your girl.
　　6 | This is inserted
　　5 | This is last
　　7 | This is last
　　8 | This is last
　　9 | This is last
　　(9 rows)
　　看一下DELETE操作。
　　test=# delete from t;
　　DELETE 9
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# select * from t;
　　id | username
　　----+----------------------
　　10 | This is last
　　11 | This is last
　　12 | This is last
　　(3 rows)
　　这个和MYSQL一样的。
　　TRUNCATE虽然和MYSQL一样可以快速清空表数据。可是ID还是从最后一个开始增加的，如果想从１开始的话，就得用setval函数来设置。
　　test=# truncate table t;
　　TRUNCATE TABLE
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# insert into t(username) values('This is last');
　　INSERT 0 1
　　test=# select * from t;
　　id | username
　　----+----------------------
　　13 | This is last
　　14 | This is last
　　15 | This is last
　　(3 rows)
　　至于怎么从1重新开始。还在学习中。。。
　　2、得到刚刚插入的自增ID。
　　在MYSQL里面：
　　mysql> truncate table t;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> insert into t(username) values('This is last');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> select last_insert_id();
　　+------------------+
　　| last_insert_id() |
　　+------------------+
　　| 1 |
　　+------------------+
　　1 row in set (0.00 sec)
　　在POSTGRESQL里面：
　　test=# drop table t
　　test-# ;
　　DROP TABLE
　　test=# create table t(id serial not null primary key,username char(20) not null);
　　NOTICE: CREATE TABLE will create implicit sequence "t_id_seq" for serial column "t.id"
　　NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
　　CREATE TABLE
　　test=# \d t
　　Table "public.t"
　　Column | Type | Modifiers
　　----------+---------------+------------------------------------------------
　　id | integer | not null default nextval('t_id_seq'::regclass)
　　username | character(20) | not null
　　Indexes:
　　"t_pkey" PRIMARY KEY, btree (id)
　　test=# insert into t(username) values('This is test name');
　　INSERT 0 1
　　test=# select * from t;
　　id | username
　　----+----------------------
　　1 | This is test name
　　(1 row)
　　test=# select currval('t_id_seq');
　　currval
　　---------
　　1
　　(1 row)
　　test=#
　　3、设置自增ID的开始值。
　　MYSQL:

　　mysql>>　　Query OK, 1 row affected (0.01 sec)
　　Records: 1 Duplicates: 0 Warnings: 0
　　mysql> insert into t(username) values('This is last');
　　Query OK, 1 row affected (0.00 sec)
　　mysql> select * from t;
　　+----+--------------+

　　|>　　+----+--------------+
　　| 1 | This is last |
　　| 3 | This is last |
　　+----+--------------+
　　2 rows in set (0.00 sec)
　　POSTGRESQL:
　　t_girl=# select setval('t_id_seq',1,false);
　　setval
　　--------
　　1
　　(1 row)
　　Time: 19.554 ms
　　t_girl=# insert into t(username) values('wangwei'),('meimei');
　　INSERT 0 2
　　Time: 1.882 ms
　　t_girl=# select * from t;
　　id | username
　　----+----------------------
　　1 | wangwei
　　2 | meimei
　　(2 rows)
　　Time: 0.598 ms