MySQL、Oracle的时间类型字段自动更新：insert插入、update更新时，自动更新时间戳。设置自增主键id，oracle创建自增id序列和触发器

1. MySQL

• 支持设置自增id的字段类型：int、bigint、double等数值类型，一般用int、bigint
• 支持设置自动更新时间的字段类型：datetime、timestamp
• 下面sql中的now()函数可以用current_timestamp【推荐】、localtimestamp、localtime替代，它们的区别见 https://blog.csdn.net/lishuoboy/article/details/136971932

  1.1. sql

  1.1.1. 不指定秒精度

  drop table if exists demo;create table demo(    id         bigint auto_increment primary key                                  comment '自增id',    name       varchar(8)          comment '姓名',    datetime1  datetime(3)  default current_timestamp                             comment 'insert        时，更新时间',    datetime2  datetime(3)                            on update current_timestamp comment '       update 时，更新时间',    datetime3  datetime(3)  default current_timestamp on update current_timestamp comment 'insert/update 时，更新时间',    timestamp1 timestamp(3) default current_timestamp                             comment 'insert        时，更新时间',    timestamp2 timestamp(3)                           on update current_timestamp comment '       update 时，更新时间',    timestamp3 timestamp(3) default current_timestamp on update current_timestamp comment 'insert/update 时，更新时间') comment = '测试自动更新时间';

  1.1.2. 指定秒精度为3

  drop table if exists demo;create table demo(    id         bigint auto_increment primary key                                        comment '自增id',    name       varchar(8)                comment '姓名',    datetime1  datetime(3)  default current_timestamp(3)                                comment 'insert        时，更新时间',    datetime2  datetime(3)                               on update current_timestamp(3) comment '       update 时，更新时间',    datetime3  datetime(3)  default current_timestamp(3) on update current_timestamp(3) comment 'insert/update 时，更新时间',    timestamp1 timestamp(3) default current_timestamp(3)                                comment 'insert        时，更新时间',    timestamp2 timestamp(3)                              on update current_timestamp(3) comment '       update 时，更新时间',    timestamp3 timestamp(3) default current_timestamp(3) on update current_timestamp(3) comment 'insert/update 时，更新时间') comment = '测试自动更新时间';

  1.2. 测试

  1.3. navicat

  1.3.1. 自动更新时间

  1.3.2. 自增Id

  2. Oracle

  • 支持设置自增id的字段类型：number、varchar等数值类型，一般用number
  • 支持设置自动更新时间的字段类型：date、timestamp、varchar等
  • 下面sql中的current_timestamp函数可以用systimestamp【推荐】、localtimestamp、sysdate、current_date替代，它们的区别见 https://blog.csdn.net/lishuoboy/article/details/136971932
  • 【注意】oracle创建触发器sql的结束符不是分号;，而是换行+斜杠（/n+/），详见下面的sql。
  • 【注意】oracle的否则语句不是elseif，而是elsif
  • 如果是给已有表加更新时间字段，存量数据的更新时间不需要默认值，则不要设置default current_timestamp,而是通过触发器控制

    2.1. sql

    2.1.1. 不指定秒精度

    -- 1.建表drop table demo;create table demo(    id         number(10, 0) primary key,           -- 自增id。需创建 自增序列 + 触发器，详见下文    name       varchar(8 char),    datetime1  date      default current_timestamp, -- insert        时，更新时间。    datetime2  date      default null,              --        update 时，更新时间。需创建更新时间触发器    datetime3  date      default current_timestamp, -- insert/update 时，更新时间。需创建更新时间触发器    timestamp1 timestamp default current_timestamp, -- insert        时，更新时间。    timestamp2 timestamp default null,              --        update 时，更新时间。需创建更新时间触发器    timestamp3 timestamp default current_timestamp  -- insert/update 时，更新时间。需创建更新时间触发器);comment on column demo.id is '自增id。需创建 自增序列 + 触发器，详见下文';comment on column demo.datetime1 is 'insert        时，更新时间';comment on column demo.datetime2 is '       update 时，更新时间。需创建更新时间触发器';comment on column demo.datetime3 is 'insert/update 时，更新时间。需创建更新时间触发器';comment on column demo.timestamp1 is 'insert        时，更新时间';comment on column demo.timestamp2 is '       update 时，更新时间。需创建更新时间触发器';comment on column demo.timestamp3 is 'insert/update 时，更新时间。需创建更新时间触发器';-- 2.创建自增id-- 2.1.创建自增长序列drop sequence sequence_demo_id;create sequence sequence_demo_id    minvalue 1 -- 最小值--  maxvalue 10 -- 最大值    nomaxvalue -- 不设置最大值    start with 1 -- 从1开始计数    increment by 1 -- 每次增加1    nocycle -- 一直累加，不循环    cache 10;-- 2.2.创建触发器，关联自增序列create or replace trigger trigger_demo_id    before insert    on demo    for each rowbegin    select sequence_demo_id.nextval into :new.id from dual;end trigger_demo_id;/-- 3.创建更新时间触发器create or replace trigger trigger_demo_update_time    before /*insert or*/ update -- 字段若设置了“default sysdate”，可以改为 “before update”    on demo    for each rowbegin    if updating then -- update 时        :new.datetime2 := current_timestamp;        :new.datetime3 := current_timestamp;        :new.timestamp2 := current_timestamp;        :new.timestamp3 := current_timestamp;    /*    elsif inserting then -- insert 时（非必需，因为已经指定了default sysdate）        :new.datetime2 := current_timestamp        :new.datetime3 := current_timestamp        :new.timestamp2 := current_timestamp        :new.timestamp3 := current_timestamp    */    end if;end;/

    2.1.2. 指定秒精度为3

    drop table demo;create table demo(    id         number(10, 0) primary key,              -- 自增id。需创建 自增序列 + 触发器，详见下文    name       varchar(8 char),    datetime1  date         default current_timestamp, -- insert        时，更新时间。    datetime2  date         default null,              --        update 时，更新时间。需创建更新时间触发器    datetime3  date         default current_timestamp, -- insert/update 时，更新时间。需创建更新时间触发器    timestamp1 timestamp(3) default current_timestamp, -- insert        时，更新时间。    timestamp2 timestamp(3) default null,              --        update 时，更新时间。需创建更新时间触发器    timestamp3 timestamp(3) default current_timestamp  -- insert/update 时，更新时间。需创建更新时间触发器);

    2.2. 测试

    2.3. navicat

    2.3.1. 自动更新时间

    2.3.2. 自增Id

    9.相关文章

    • MySQL获取当前日期、时间、时间戳的函数sysdate()、now()、current_timestamp()、localtimestamp()、localtime()对比，区别、异同点