欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

postgresql兼容MySQL on update current_timestamp問題

 更新時(shí)間：2023年03月20日 15:16:44   作者：Ruby-PGer  
這篇文章主要介紹了postgresql兼容MySQL on update current_timestamp問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

postgresql兼容MySQL on update current_timestamp

問題描述

PostgreSQL執(zhí)行Insert語句時(shí)，自動(dòng)填入時(shí)間的功能可以在創(chuàng)建表時(shí)實(shí)現(xiàn)，但更新表時(shí)時(shí)間戳不會(huì)自動(dòng)自動(dòng)更新。

在mysql中可以在創(chuàng)建表時(shí)定義自動(dòng)更新字段，比如 ：

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

那PostgreSQL中怎么操作呢？

解決方案

通過觸發(fā)器實(shí)現(xiàn)，具體如下：

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();

測(cè)試代碼：

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、分區(qū)表

mysql和pg中的分區(qū)表使用基本類似，同樣都支持hash、range、list三種基本的分區(qū)類型。兩者的區(qū)別在于：

mysql：不支持指定默認(rèn)分區(qū)，最多只支持2級(jí)分區(qū)，不支持表達(dá)式分區(qū)。且需要注意，mysql當(dāng)前除InnoDB或NDB之外的任何存儲(chǔ)引擎都不支持分區(qū)表這一功能，如MyISAM。

pg：pg中可以通過default分區(qū)名的方式指定默認(rèn)分區(qū)，并且支持多級(jí)別的分區(qū)，且支持不同種類分區(qū)的任意組。pg還支持表達(dá)式分區(qū)，不過必須得是immutable類型表達(dá)式。

除此之外主要注意的是，無論是pg還是mysql都必須pk、uk中包含分區(qū)鍵，因?yàn)閮烧吣壳岸疾恢С秩炙饕?/p>

2、語法

offset/limit：

mysql和pg中都支持offset/limit的分頁語法，但是兩者有一點(diǎn)不同：

–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)

類型轉(zhuǎn)換：

mysql和pg中都支持cast(expression as target_type)的方法去進(jìn)行類型轉(zhuǎn)換，但是pg中除此之外還支持value::new_type的方法來進(jìn)行類型轉(zhuǎn)換。

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作用是當(dāng)插入數(shù)據(jù)時(shí)：如果不存在則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來實(shí)現(xiàn)類似的功能。

語法為：

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命令來實(shí)現(xiàn)加載數(shù)據(jù)的功能。

語法為：

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命令來實(shí)現(xiàn)同樣的功能，copy命令分為服務(wù)端copy和客戶端的copy協(xié)議。

語法為：

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索引，即倒排索引，常用來實(shí)現(xiàn)多值類型、json類型、全文檢索等的索引查詢；
  • 表達(dá)式索引，mysql中的表達(dá)式索引不支持spatial和fulltext類型。
  • 空間索引，mysql中不支持空間索引，其實(shí)現(xiàn)空間索引的方式是將空間對(duì)象轉(zhuǎn)換成geohash編碼，然后使用btree索引來實(shí)現(xiàn)。

pg中的索引類型：

  • 支持多種索引類型：btree、hash、gin、gist、sp-gist、bloom、rum、brin；
  • 還支持exclude索引、表達(dá)式索引、partial索引(分區(qū)索引)；
  • 支持空間索引，是真正的基于rtree的空間索引類型；
  • 且pg開發(fā)了多種索引接口，用戶可以自定義新的索引。

4、其它

約束：

mysql和pg一樣都支持主鍵約束、外鍵約束、唯一約束、not null約束等。兩者在約束方面的區(qū)別在于：

mysql：check約束不是強(qiáng)制的，即可以創(chuàng)建check約束，但是違反該約束的數(shù)據(jù)仍然不會(huì)報(bào)錯(cuò)；exclude排它約束mysql中不支持。

pg：pg中的check約束是強(qiáng)制的，如果數(shù)據(jù)不符合check約束則無法插入。并且pg中還支持exclude約束。

use/desc：

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

除此之外，pg和mysql雖然都支持四種事務(wù)隔離級(jí)別，但是在pg中read uncommitted的隔離級(jí)別是不可用的，這也確保了在pg中不會(huì)出現(xiàn)臟讀的現(xiàn)象。

另外在mysql中是存在隱式提交的，即在事務(wù)中的DDL語句會(huì)被自動(dòng)提交，而在pg中不會(huì)。

例如：

–mysql

可以發(fā)現(xiàn)事務(wù)回滾后t2表仍然存在，因?yàn)橐呀?jīng)自動(dòng)提交了。

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

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

相關(guān)文章

最新評(píng)論