首页
登录 | 注册

[原创]POSTGRESQL和 MYSQL的自增字段比较

最近学习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;
+----+-------------------+

| id | username |
+----+-------------------+

| 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;
+----+-------------------+

| 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 |
| 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;
+----+--------------+

| id | username |
+----+--------------+

| 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还是从最后一个开始增加的,如果想从1开始的话,就得用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> alter table t auto_increment = 3;
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;
+----+--------------+

| id | username |
+----+--------------+

| 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


相关文章

  • Oracle数据库 测试平台 - Database 11gR2/12cR2 --数据字典表级信息 SELECT USER,T.TABLE_NAME,TC.COMMENTS,DECODE(C.CONSTRAINT_NAME,NULL,'N', ...
  • sysbench压力测试工具安装和参数介绍   一.sysbench压力测试工具简介:   sysbench是一个开源的.模块化的.跨平台的多线程性能测试工具,可以用来进行CPU.内存.磁盘I/O.线程.数据库的性能测试.目前支持的数据库有 ...
  • MySQL默认的超级管理员是root 连接方法:mysql -u root 默认密码为空 修改密码的方法:mysqladmin -u root password 'new password' PostgreSQL默认的超级管理员密码是pos ...
  •  更多Java培训.Java视频教程学习资料,请登录尚硅谷网站下载:www.atguigu.com  ?1. 背景及目标? 厦门游家公司(4399.com)用于员工培训和分享.? 针对用户群为已经使用过mysql环境,并有一定开发经验的工程 ...
  • 博客文章除注明转载外,均为原创.转载请注明出处. 本文连接地址:http://blog.chinaunix.net/uid-31396856-id-5819025.html DBA很多时间会遇到需要批量处理MySQL服务器的大量连接,比如查 ...
  • 亲爱的各位博主,博客评选活动又开始啦,感谢大家对活动的支持,希望大家的技术水平越来越好,博文也更加出色,获奖的博主还有机会晋级"推荐博客"."专家博客"!     博客评选将邀请技术专家作为点评嘉宾, ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.013 s.
京ICP备10005923号