postgresql兼容MySQL on update current_timestamp问题怎么解决

这篇文章主要介绍“postgresql兼容MySQL on update current_timestamp问题怎么解决”，在日常操作中，相信很多人在postgresql兼容Mysql on update current_timestamp问题怎么解决问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”postgresql兼容mysql on update current_timestamp问题怎么解决”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

postgresql兼容MySQL on update current_timestamp

问题描述

PostgreSQL执行Insert语句时，自动填入时间的功能可以在创建表时实现，但更新表时时间戳不会自动自动更新。

在mysql中可以在创建表时定义自动更新字段，比如 ：

create table ab (id int,changetimestamp timestampNOT NULLdefault CURRENT_TIMESTAMPon 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 supportedLINE 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 TABLEbill=# insert into tt2 values(222);INSERT 0 1bill=# rollback ;ROLLBACKbill=# select * from t2;ERROR:  relation "t2" does not exist

到此，关于“postgresql兼容MySQL on update current_timestamp问题怎么解决”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注编程网网站，小编会继续努力为大家带来更多实用的文章！