pg>mysql>on update字段
2021-09-24 14:44:30    26    0    0
ivan

1、创建表

CREATE TABLE tab_tri_update_yf (

  tab_id int(11) NOT NULL AUTO_INCREMENT,

  tab_name VARCHAR(10),

  insert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (tab_id)

);

 2、mysql

mysql> insert into tab_tri_update_yf(tab_name) VALUES ('12');

mysql> select * from tab_tri_update_yf;

+--------+----------+---------------------+---------------------+

| tab_id | tab_name | insert_time         | update_time         |

+--------+----------+---------------------+---------------------+

|      1 | 12       | 2019-11-08 13:40:56 | 2019-11-08 13:40:56 |

+--------+----------+---------------------+---------------------+

1 row in set (0.00 sec)

 

mysql> update tab_tri_update_yf set tab_name='34' where tab_id=1 ;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from tab_tri_update_yf;

+--------+----------+---------------------+---------------------+

| tab_id | tab_name | insert_time         | update_time         |

+--------+----------+---------------------+---------------------+

|      1 | 34       | 2019-11-08 13:40:56 | 2019-11-08 13:43:02 |

+--------+----------+---------------------+---------------------+

1 row in set (0.00 sec)


3、pg:

postgres=# CREATE TABLE tab_tri_update_yf (

    tab_id bigserial NOT NULL,

    tab_name varchar(10) NULL,

    insert_time timestamp NULL DEFAULT CURRENT_TIMESTAMP::timestamp(0) without time zone,

    update_time timestamp NULL,

    CONSTRAINT tab_update_default_pkey PRIMARY KEY (tab_id)

);

postgres=# insert into tab_tri_update_yf(tab_name) VALUES ('12');

postgres=# select * from tab_tri_update_yf;

 tab_id | tab_name |      insert_time       | update_time

--------+----------+------------------------+-------------

      1 | 12       | 2019-11-08 14:07:02 |

 创建触发器:

postgres=# CREATE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$

            BEGIN

                NEW.update_time := current_timestamp::timestamp(0) without time zone;

                RETURN NEW;

            END;

          $update_timestamp$ LANGUAGE plpgsql;

postgres=# CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON tab_tri_update_yf

            FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

   

postgres=# insert into tab_tri_update_yf(tab_name) VALUES ('56');

postgres=# select * from tab_tri_update_yf;

 tab_id | tab_name |      insert_time       |      update_time

--------+----------+------------------------+------------------------

      1 | 12       | 2019-11-08 14:07:02 |

      2 | 56       | 2019-11-08 14:08:00 | 2019-11-08 14:08:00

 

postgres=# update tab_tri_update_yf set tab_name='01' where tab_id=2;

postgres=# select * from tab_tri_update_yf;

 tab_id | tab_name |      insert_time       |      update_time

--------+----------+------------------------+------------------------

      1 | 12       | 2019-11-08 14:07:02 |

      2 | 01       | 2019-11-08 14:08:00 | 2019-11-08 14:08:27


Pre: pg>执行sql文件

Next: pg>bytea存储文件

26
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content