文章摘要
这篇文章主要介绍了PostgreSQL和MySQL在时间戳更新方面的差异及其解决方案。PostgreSQL在插入语句中可以自动更新时间戳，但在更新语句中默认不会自动更新。通过使用触发器可以实现类似功能，具体方法是创建一个名为`upd_timestamp`的触发器，覆盖`before update`监听，修改`modified`字段。
此外，文章还比较了PostgreSQL和MySQL在分区表、索引、类型转换、upsert/replace、数据加载、事务隔离等方面的差异。PostgreSQL支持更丰富的分区类型（如默认分区、多级分区、表达式分区），支持空间索引，并且提供更灵活的事务隔离级别。而MySQL则支持更简单的分区类型，且不支持空间索引。
文章还提到了PostgreSQL和MySQL在约束类型上的区别，指出PostgreSQL的`check`约束是强制的，而MySQL的`check`约束是默认的。此外，PostgreSQL的事务隔离级别（如`read uncommitted`）在MySQL中不可用。
总体来看，这篇文章通过具体案例和详细比较，帮助读者理解PostgreSQL和MySQL在时间戳更新、数据操作、索引支持等方面的差异，并提供了相应的解决方案。
目录postgresql兼容MySQL on update current_timestamp问题描述解决方案postgresql和mysql常用语法比较1、分区表2、语法3、索引4、其它总结
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;
mysql和pg中的分区表使用基本类似，同样都支持hash、range、list三种基本的分区类型。两者的区别在于：
mysql：不支持指定默认分区，最多只支持2级分区，不支持表达式分区。且需要注意，mysql当前除InnoDB或NDB之外的任何存储引擎都不支持分区表这一功能，如MyISAM。
pg：pg中可以通过default分区名的方式指定默认分区，并且支持多级别的分区，且支持不同种类分区的任意组。pg还支持表达式分区，不过必须得是immutable类型表达式。
除此之外主要注意的是，无论是pg还是mysql都必须pk、uk中包含分区键，因为两者目前都不支持全局索引。
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 [, …] ) ]
mysql中索引类型：
btree索引；invert索引，即倒排索引，常用来实现多值类型、json类型、全文检索等的索引查询；表达式索引，mysql中的表达式索引不支持spatial和fulltext类型。空间索引，mysql中不支持空间索引，其实现空间索引的方式是将空间对象转换成geohash编码，然后使用btree索引来实现。pg中的索引类型：
支持多种索引类型：btree、hash、gin、gist、sp-gist、bloom、rum、brin；还支持exclude索引、表达式索引、partial索引(分区索引)；支持空间索引，是真正的基于rtree的空间索引类型；且pg开发了多种索引接口，用户可以自定义新的索引。约束：
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数据类型对比兼容PostgreSQL更新表时时间戳不会自动更新的解决方法postgreSQL 使用timestamp转成date格式