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 | 462844 | 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 NOTHINGDO 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

總結

以上是生活随笔為你收集整理的PostgreSQL和mysql常用语法比较的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。