postgresql相容MySQL on update current_timestamp問題

2023-03-21 06:00:54

postgresql相容MySQL on update current_timestamp

問題描述

PostgreSQL執行Insert語句時，自動填入時間的功能可以在建立表時實現，但更新表時時間戳不會自動自動更新。

在mysql中可以在建立表時定義自動更新欄位，比如：

create table ab (
id int,
changetimestamp timestamp
NOT NULL
default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP
);

那PostgreSQL中怎麼操作呢？

解決方案

通過觸發器實現，具體如下：

create or replace function upd_timestamp() returns trigger as
$$
begin
    new.modified = current_timestamp;
    return new;
end
$$
language plpgsql;

drop table if exists ts;
create table ts (
id bigserial primary key,
tradeid integer ,
email varchar(50),
num integer,
modified timestamp default current_timestamp
);
create trigger t_name before update on ts for each row execute procedure upd_timestamp();

測試程式碼：

insert into ts (tradeid,email,num) values (1223,‘mike_zhang@live.com',1);
update ts set email=‘Mike_Zhang@live' where tradeid = 1223 ;

create unique index ts_tradeid_idx on ts(tradeid);
//insert into ts(tradeid,email,num) values (1223,‘Mike_Zhang@live.com',2) on conflict(tradeid) do update
//set email = excluded.email,num=excluded.num;

select * from ts;
– delete from ts;

postgresql和mysql常用語法比較

1、分割區表

mysql和pg中的分割區表使用基本類似，同樣都支援hash、range、list三種基本的分割區型別。兩者的區別在於：

mysql：不支援指定預設分割區，最多隻支援2級分割區，不支援表示式分割區。且需要注意，mysql當前除InnoDB或NDB之外的任何儲存引擎都不支援分割區表這一功能，如MyISAM。

pg：pg中可以通過default分割區名的方式指定預設分割區，並且支援多級別的分割區，且支援不同種類分割區的任意組。pg還支援表示式分割區，不過必須得是immutable型別表示式。

除此之外主要注意的是，無論是pg還是mysql都必須pk、uk中包含分割區鍵，因為兩者目前都不支援全域性索引。

2、語法

offset/limit：

mysql和pg中都支援offset/limit的分頁語法，但是兩者有一點不同：

–mysql

mysql> select * from t1 limit 2,2;
+------+------+
| id   | ino  |
+------+------+
|    3 | c    |
|    4 | d    |
+------+------+
2 rows in set (0.00 sec)

–pg

pg中不支援上面這種mysql的寫法

bill=# select * from tbl limit 2,2;
ERROR:  LIMIT #,# syntax is not supported
LINE 1: select * from tbl limit 2,2;
                          ^
HINT:  Use separate LIMIT and OFFSET clauses.

bill=# select * from tbl limit 2 offset 2;
 id | c1 |  c2  | c3  |  c4  |   c5    |  c6   | c7 |   c8   |  c9   |  c10  
----+----+------+-----+------+---------+-------+----+--------+-------+-------
  3 | 92 | 8207 | 167 | 3031 |  363025 | 66793 | 31 | 108702 |  3358 | 46284
  4 | 19 | 6982 | 834 | 4278 | 6929072 | 83949 | 80 |   8206 | 25265 | 59691
(2 rows)

型別轉換：

mysql和pg中都支援cast(expression as target_type)的方法去進行型別轉換，但是pg中除此之外還支援value::new_type的方法來進行型別轉換。

bill=# select cast(id as int8) from t1 limit 1;
 id 
----
  1
(1 row)

bill=# select id::int8 from t1 limit 1;
 id 
----
  1
(1 row)

upsert/replace:

pg中的upsert作用是當插入資料時：如果不存在則insert，存在則update。

語法為：

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ ON CONFLICT [ conflict_target ] conflict_action ]

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

mysql中使用replace來實現類似的功能。

語法為：

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ...
      |
      VALUES row_constructor_list
    }

例子：

mysql> CREATE TABLE test (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   data VARCHAR(64) DEFAULT NULL,
    ->   ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
+----+------+---------------------+
1 row in set (0.00 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

load data:

mysql中使用load命令來實現載入資料的功能。

語法為：

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

在pg中我們使用copy命令來實現同樣的功能，copy命令分為伺服器端copy和使用者端的copy協定。

語法為：

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

3、索引

mysql中索引型別：

btree索引；
invert索引，即倒排索引，常用來實現多值型別、json型別、全文檢索等的索引查詢；
表示式索引，mysql中的表示式索引不支援spatial和fulltext型別。
空間索引，mysql中不支援空間索引，其實現空間索引的方式是將空間物件轉換成geohash編碼，然後使用btree索引來實現。

pg中的索引型別：

支援多種索引型別：btree、hash、gin、gist、sp-gist、bloom、rum、brin；
還支援exclude索引、表示式索引、partial索引(分割區索引)；
支援空間索引，是真正的基於rtree的空間索引型別；
且pg開發了多種索引介面，使用者可以自定義新的索引。

4、其它

約束：

mysql和pg一樣都支援主鍵約束、外來鍵約束、唯一約束、not null約束等。兩者在約束方面的區別在於：

mysql：check約束不是強制的，即可以建立check約束，但是違反該約束的資料仍然不會報錯；exclude排它約束mysql中不支援。

pg：pg中的check約束是強制的，如果資料不符合check約束則無法插入。並且pg中還支援exclude約束。

use/desc：

mysql中use database_name和desc table_name的快捷命令在pg中也有很方便的命令支援，pg中可以使用：c database_name和d table_name來代替。

除此之外，pg和mysql雖然都支援四種事務隔離級別，但是在pg中read uncommitted的隔離級別是不可用的，這也確保了在pg中不會出現髒讀的現象。

另外在mysql中是存在隱式提交的，即在事務中的DDL語句會被自動提交，而在pg中不會。

例如：

–mysql

可以發現事務回滾後t2表仍然存在，因為已經自動提交了。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(222);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|  222 |
+------+
1 row in set (0.00 sec)

–pg：

而在pg中卻沒有，可以被rollback

bill=# create table tt2(id int);
CREATE TABLE
bill=# insert into tt2 values(222);
INSERT 0 1
bill=# rollback ;
ROLLBACK
bill=# select * from t2;
ERROR:  relation "t2" does not exist

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

postgresql相容MySQL on update current_timestamp問題

目錄