PostgreSQL
在执行批量插入、更新使用函数now()
或是current_time()
获取当前系统时间一致,导致在根据时间order by
时无法获取理想的结果
user表结构
create table "user"
(
user_id serial not null
constraint user_pk
primary key,
user_name varchar(32) not null,
user_sex integer not null,
user_password varchar(256) not null,
create_time timestamp not null,
create_by integer not null,
update_time timestamp not null,
update_by integer not null,
deleted integer not null
);
批量新增user
-
insert into "user" (user_name, user_sex, user_password, create_time, create_by, update_time, update_by, deleted) values ('张三', 1, '123456', now(), 1, now(), 1, 0), ('宋爱梅', 0, '123456', now(), 1, now(), 1, 0), ('王志芳', 1, '123456', now(), 1, now(), 1, 0), ('于光', 0, '123456', now(), 1, now(), 1, 0), ('贾隽仙', 1, '123456', now(), 1, now(), 1, 0), ('贾燕青', 0, '123456', now(), 1, now(), 1, 0), ('刘振杰', 1, '123456', now(), 1, now(), 1, 0), ('郭卫东', 0, '123456', now(), 1, now(), 1, 0), ('崔红宇', 1, '123456', now(), 1, now(), 1, 0), ('马福平', 0, '123456', now(), 1, now(), 1, 0), ('冯红', 1, '123456', now(), 1, now(), 1, 0), ('崔敬伟', 0, '123456', now(), 1, now(), 1, 0), ('穆增志', 1, '123456', now(), 1, now(), 1, 0), ('谢志威', 0, '123456', now(), 1, now(), 1, 0), ('吕金起', 1, '123456', now(), 1, now(), 1, 0), ('韩云庆', 0, '123456', now(), 1, now(), 1, 0), ('鲁全福', 1, '123456', now(), 1, now(), 1, 0), ('郭建立', 0, '123456', now(), 1, now(), 1, 0), ('郝连水', 1, '123456', now(), 1, now(), 1, 0), ('闫智胜', 0, '123456', now(), 1, now(), 1, 0);
-
查询
create_time
、update_time
-
select create_time,update_time from "user";
-
-
结果**
create_time
,update_time
均为同一时间**
问题解决
-
now()
- 按照当前事务的开始时刻返回结果,所以获取的值在事务运行的整个期间内都不改变
-
clock_timestamp()
- 当前日期和时间(在语句执行期间变化)
- 返回真正的当前时间,因此获取到的值在同一条
SQL
命令中都会变化
-
批量新增用户
-
insert into "user" (user_name, user_sex, user_password, create_time, create_by, update_time, update_by, deleted) values ('张三', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('宋爱梅', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('王志芳', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('于光', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('贾隽仙', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('贾燕青', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('刘振杰', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('郭卫东', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('崔红宇', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('马福平', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('冯红', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('崔敬伟', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('穆增志', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('谢志威', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('吕金起', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('韩云庆', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('鲁全福', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('郭建立', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('郝连水', 1, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0), ('闫智胜', 0, '123456', clock_timestamp(), 1, clock_timestamp(), 1, 0);
-
-
查询
create_time
、update_time
-
select create_time,update_time from "user";
-
-
结果