经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » PostgreSQL » 查看文章
postgresql兼容MySQL on update current_timestamp问题
来源：jb51　　时间：2023/3/22 9:23:31　　对本文有异议

postgresql兼容MySQL on update current_timestamp

问题描述

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

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

  1. create table ab (
  2. id int,
  3. changetimestamp timestamp
  4. NOT NULL
  5. default CURRENT_TIMESTAMP
  6. on update CURRENT_TIMESTAMP
  7. );

那PostgreSQL中怎么操作呢？

解决方案

通过触发器实现，具体如下：

  1. create or replace function upd_timestamp() returns trigger as
  2. $$
  3. begin
  4.     new.modified = current_timestamp;
  5.     return new;
  6. end
  7. $$
  8. language plpgsql;
  1. drop table if exists ts;
  2. create table ts (
  3. id bigserial primary key,
  4. tradeid integer ,
  5. email varchar(50),
  6. num integer,
  7. modified timestamp default current_timestamp
  8. );
  9. create trigger t_name before update on ts for each row execute procedure upd_timestamp();

测试代码：

  1. insert into ts (tradeid,email,num) values (1223,‘mike_zhang@live.com',1);
  2. update ts set email=‘Mike_Zhang@live' where tradeid = 1223 ;
  3.  
  4. create unique index ts_tradeid_idx on ts(tradeid);
  5. //insert into ts(tradeid,email,num) values (1223,‘Mike_Zhang@live.com',2) on conflict(tradeid) do update
  6. //set email = excluded.email,num=excluded.num;
  7.  
  8. select * from ts;
  9. – 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

  1. mysql> select * from t1 limit 2,2;
  2. +------+------+
  3. | id ? | ino ?|
  4. +------+------+
  5. | ? ?3 | c ? ?|
  6. | ? ?4 | d ? ?|
  7. +------+------+
  8. 2 rows in set (0.00 sec)

–pg

pg中不支持上面这种mysql的写法

  1. bill=# select * from tbl limit 2,2;
  2. ERROR: ?LIMIT #,# syntax is not supported
  3. LINE 1: select * from tbl limit 2,2;
  4. ? ? ? ? ? ? ? ? ? ? ? ? ? ^
  5. HINT: ?Use separate LIMIT and OFFSET clauses.
  6.  
  7. bill=# select * from tbl limit 2 offset 2;
  8. ?id | c1 | ?c2 ?| c3 ?| ?c4 ?| ? c5 ? ?| ?c6 ? | c7 | ? c8 ? | ?c9 ? | ?c10 ?
  9. ----+----+------+-----+------+---------+-------+----+--------+-------+-------
  10. ? 3 | 92 | 8207 | 167 | 3031 | ?363025 | 66793 | 31 | 108702 | ?3358 | 46284
  11. ? 4 | 19 | 6982 | 834 | 4278 | 6929072 | 83949 | 80 | ? 8206 | 25265 | 59691
  12. (2 rows)

类型转换：

mysql和pg中都支持cast(expression as target_type)的方法去进行类型转换，但是pg中除此之外还支持value::new_type的方法来进行类型转换。

  1. bill=# select cast(id as int8) from t1 limit 1;
  2. ?id?
  3. ----
  4. ? 1
  5. (1 row)
  6.  
  7. bill=# select id::int8 from t1 limit 1;
  8. ?id?
  9. ----
  10. ? 1
  11. (1 row)

upsert/replace:

pg中的upsert作用是当插入数据时：如果不存在则insert，存在则update。

语法为：

  1. INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
  2. ? ? [ ON CONFLICT [ conflict_target ] conflict_action ]
  3.  
  4. and conflict_action is one of:
  5.  
  6. ? ? DO NOTHING
  7. ? ? DO UPDATE SET { column_name = { expression | DEFAULT } |
  8. ? ? ? ? ? ? ? ? ? ? ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
  9. ? ? ? ? ? ? ? ? ? ? ( column_name [, ...] ) = ( sub-SELECT )
  10. ? ? ? ? ? ? ? ? ? } [, ...]
  11. ? ? ? ? ? ? ? [ WHERE condition ]

mysql中使用replace来实现类似的功能。

语法为：

  1. REPLACE [LOW_PRIORITY | DELAYED]
  2. ? ? [INTO] tbl_name
  3. ? ? [PARTITION (partition_name [, partition_name] ...)]
  4. ? ? [(col_name [, col_name] ...)]
  5. ? ? { {VALUES | VALUE} (value_list) [, (value_list)] ...
  6. ? ? ? |
  7. ? ? ? VALUES row_constructor_list
  8. ? ? }

例子：

  1. mysql> CREATE TABLE test (
  2. ? ? -> ? id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3. ? ? -> ? data VARCHAR(64) DEFAULT NULL,
  4. ? ? -> ? ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  5. ? ? -> ? PRIMARY KEY (id)
  6. ? ? -> );
  7. Query OK, 0 rows affected (0.02 sec)
  8.  
  9. mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
  10. Query OK, 1 row affected (0.00 sec)
  11.  
  12. mysql> SELECT * FROM test;
  13. +----+------+---------------------+
  14. | id | data | ts ? ? ? ? ? ? ? ? ?|
  15. +----+------+---------------------+
  16. | ?1 | Old ?| 2014-08-20 18:47:00 |
  17. +----+------+---------------------+
  18. 1 row in set (0.00 sec)
  19.  
  20. mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
  21. Query OK, 2 rows affected (0.00 sec)
  22.  
  23. mysql> SELECT * FROM test;
  24. +----+------+---------------------+
  25. | id | data | ts ? ? ? ? ? ? ? ? ?|
  26. +----+------+---------------------+
  27. | ?1 | New ?| 2014-08-20 18:47:42 |
  28. +----+------+---------------------+
  29. 1 row in set (0.00 sec)

load data:

mysql中使用load命令来实现加载数据的功能。

语法为：

  1. LOAD DATA
  2. ? ? [LOW_PRIORITY | CONCURRENT] [LOCAL]
  3. ? ? INFILE 'file_name'
  4. ? ? [REPLACE | IGNORE]
  5. ? ? INTO TABLE tbl_name
  6. ? ? [PARTITION (partition_name [, partition_name] ...)]
  7. ? ? [CHARACTER SET charset_name]
  8. ? ? [{FIELDS | COLUMNS}
  9. ? ? ? ? [TERMINATED BY 'string']
  10. ? ? ? ? [[OPTIONALLY] ENCLOSED BY 'char']
  11. ? ? ? ? [ESCAPED BY 'char']
  12. ? ? ]
  13. ? ? [LINES
  14. ? ? ? ? [STARTING BY 'string']
  15. ? ? ? ? [TERMINATED BY 'string']
  16. ? ? ]
  17. ? ? [IGNORE number {LINES | ROWS}]
  18. ? ? [(col_name_or_user_var
  19. ? ? ? ? [, col_name_or_user_var] ...)]
  20. ? ? [SET col_name={expr | DEFAULT},
  21. ? ? ? ? [, col_name={expr | DEFAULT}] ...]

在pg中我们使用copy命令来实现同样的功能，copy命令分为服务端copy和客户端的copy协议。

语法为：

  1. COPY table_name [ ( column_name [, ...] ) ]
  2. ? ? FROM { 'filename' | PROGRAM 'command' | STDIN }
  3. ? ? [ [ WITH ] ( option [, ...] ) ]
  4. ? ? [ WHERE condition ]
  5.  
  6. COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
  7. ? ? TO { 'filename' | PROGRAM 'command' | STDOUT }
  8. ? ? [ [ 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表仍然存在，因为已经自动提交了。

  1. mysql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> create table t2(id int);
  5. Query OK, 0 rows affected (0.00 sec)
  6.  
  7. mysql> insert into t2 values(222);
  8. Query OK, 1 row affected (0.00 sec)
  9.  
  10. mysql> rollback;
  11. Query OK, 0 rows affected (0.00 sec)
  12.  
  13. mysql> select * from t2;
  14. +------+
  15. | id ? |
  16. +------+
  17. | ?222 |
  18. +------+
  19. 1 row in set (0.00 sec)

–pg：

而在pg中却没有，可以被rollback

  1. bill=# create table tt2(id int);
  2. CREATE TABLE
  3. bill=# insert into tt2 values(222);
  4. INSERT 0 1
  5. bill=# rollback ;
  6. ROLLBACK
  7. bill=# select * from t2;
  8. ERROR: ?relation "t2" does not exist

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持w3xue。

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

PostgreSQL热门文章

  • PostgreSQL报错 解决操作符不存在的问题
  • PostgreSQL 自定义自动类型转换操作(CAST)
  • postgresql如何兼容MySQL if函数
  • Postgresql在mybatis中报错:操作符不存在:character varying == unknown的问题
  • PostgreSQL数据库事务插入删除及更新操作示例
  • PostgreSQL 中的单引号与双引号用法说明
  • PostgreSQL将日期转为当前年、月、日的函数date_trunc
  • PostgreSQL是世界最先进的开源数据库系统吗？
  • postgresql 实现启动、状态查看、关闭
  • PostgreSQL聚合函数的分组排序使用示例

    • PostgreSQL推荐文章

  • EF Core连接PostgreSQL数据库
  • PostgreSQL、KingBase 数据库 ORDER BY LIMIT 查询缓慢案例
  • ClickHouse(20)ClickHouse集成PostgreSQL表引擎详细解析
  • 分享一次公司晋级考试的SQL题目，非常有趣的案例（postgresql 标量子查询 where like 谓词过滤条件）
  • 纯分享：将MySql的建表DDL转为PostgreSql的DDL
  • 群晖DS218+部署PostgreSQL(docker)
  • rockyLinux 初体验（教程）PostgreSQL15
  • postgresql兼容MySQL on update current_timestamp问题
  • PostgreSQL聚合函数的分组排序使用示例
  • PostgreSQL数据库视图及子查询使用操作

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号