postgresql兼容MySQL on update current_timestamp問題
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)文章
postgresql 查看當(dāng)前用戶名的實(shí)現(xiàn)
這篇文章主要介紹了postgresql 查看當(dāng)前用戶名的實(shí)現(xiàn)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL啟動(dòng)停止命令(重啟)的相關(guān)資料,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧2023-11-11
postgresql開啟pg_log日志詳細(xì)步驟及參數(shù)說明
pg_log日志要啟動(dòng)保存的話需要去設(shè)置一下相關(guān)的配置文件參數(shù)就好了,下面這篇文章主要給大家介紹了關(guān)于postgresql開啟pg_log日志詳細(xì)步驟及參數(shù)說明的相關(guān)資料,需要的朋友可以參考下2024-02-02
PostgreSQL時(shí)間線(timeline)和History File的用法
這篇文章主要介紹了PostgreSQL時(shí)間線(timeline)和History File的用法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgresql查詢自動(dòng)將大寫的名稱轉(zhuǎn)換為小寫的案例
這篇文章主要介紹了postgresql查詢自動(dòng)將大寫的名稱轉(zhuǎn)換為小寫的案例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要給大家介紹了關(guān)于pgsql查詢優(yōu)化之模糊查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用pgsql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-07-07
PostgreSQL優(yōu)雅的進(jìn)行遞歸查詢的實(shí)戰(zhàn)指南
在實(shí)際開發(fā)中,我們經(jīng)常會(huì)遇到樹形結(jié)構(gòu)或圖結(jié)構(gòu)的數(shù)據(jù)需求,這些場(chǎng)景的核心問題是：如何高效查詢具有層級(jí)/遞歸關(guān)系的數(shù)據(jù)？所以本文將從基礎(chǔ)到實(shí)戰(zhàn),手把手教你掌握遞歸查詢的精髓,需要的朋友可以參考下2026-01-01
這篇文章主要介紹了phpPgAdmin 常見錯(cuò)誤和問題的解決辦法,如安裝錯(cuò)誤、登陸錯(cuò)誤、轉(zhuǎn)儲(chǔ)功能、其它錯(cuò)誤和問題等,需要的朋友可以參考下2014-03-03
Postgresql 數(shù)據(jù)庫轉(zhuǎn)義字符操作
這篇文章主要介紹了Postgresql 數(shù)據(jù)庫轉(zhuǎn)義字符操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
pg中的長事務(wù)會(huì)影響表中垃圾回收，導(dǎo)致表的年齡增長無法freeze。能消耗事務(wù)的只有當(dāng)執(zhí)行了一些DML或者DDL操作后才能算是我們通常說的長事務(wù)。否則只能算是我們常說的長連接，當(dāng)然長連接也有很多弊端，例如占用內(nèi)存、cpu等資源2022-09-09