REPLACE INTO 语句会判断行记录是否存在(根据主键索引或唯一索引判断)。如果不存在,则插入记录;如果存在,则删除已有记录,并插入新行记录。目标表建议有主键或者唯一索引,否则容易插入重复的记录。

    示例如下:

    1. obclient> CREATE TABLE t_replace(
    2. id number NOT NULL PRIMARY KEY
    3. , name varchar(10) NOT NULL
    4. , value number
    5. ,gmt_create timestamp NOT NULL DEFAULT current_timestamp
    6. );
    7. Query OK, 0 rows affected (0.06 sec)
    8. obclient> REPLACE INTO t_replace values(1,'CN',2001, current_timestamp ());
    9. Query OK, 1 row affected (0.00 sec)
    10. obclient> REPLACE INTO t_replace
    11. SELECT id,name,value,gmt_create FROM t_insert;
    12. Query OK, 5 rows affected (0.00 sec)
    13. Records: 4 Duplicates: 1 Warnings: 0
    14. obclient> REPLACE INTO t_replace(id, name, value) values(6,'DE',20006);
    15. Query OK, 1 row affected (0.01 sec)
    16. obclient> select * from t_replace;
    17. +----+------+-------+---------------------+
    18. | id | name | value | gmt_create |
    19. +----+------+-------+---------------------+
    20. | 1 | CN | 10001 | 2020-04-03 16:05:45 |
    21. | 2 | US | 10002 | 2020-04-03 16:05:54 |
    22. | 3 | UK | 10003 | 2020-04-03 16:05:54 |
    23. | 4 | JP | 10004 | 2020-04-03 16:06:08 |
    24. | 6 | DE | 20006 | 2020-04-03 16:09:19 |
    25. +----+------+-------+---------------------+
    26. 5 rows in set (0.01 sec)