REPLACE · k8s原理与实践

[TOC] # REPLACE ## 描述该语句用于替换一个或多个记录到表。如果没有主键或唯一键冲突则插入记录，如果存在冲突则先删除冲突记录再插入新的记录。 ## 格式 ~~~ replace_stmt: REPLACE [INTO] table_factor [PARTITION (partition_name_list)] [(column_name_list)] {VALUES | VALUE} column_value_lists; partition_name_list: partition_name [, partition_name ...] column_name_list: column_name [, column_name ...] column_value_lists: (column_value_list) [, (column_value_list) ...] column_value_list: column_value [, column_value ...] column_value: {expression | DEFAULT} ~~~ ### 参数解释 <table data-tag="table" id="table-hwf-6an-djv" class="table"><colgroup width="360" span="1" data-tag="col" id="col-q0l-4ij-13b" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="360" span="1" data-tag="col" id="col-gz9-yr4-93p" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-xq3-1fs-f3g" class="thead"><tr id="tr-zzm-qw9-zww"><th id="td-c0t-nmr-4rh"><p id="p-g03-ub3-imf"><b>参数</b></p></th><th id="td-nki-odq-hw4"><p id="p-i5n-re3-rpg"><b>描述</b></p></th></tr></thead><tbody data-tag="tbody" id="tbody-i48-632-qp6" class="tbody"><tr data-tag="tr" id="tr-4np-6uu-0km" class="tr"><td data-tag="td" id="td-e4z-1wb-6ti" class="td"><p data-tag="p" id="p-o5j-paj-kfd" class="p">table_factor</p></td><td data-tag="td" id="td-bbv-wcy-ldv" class="td"><p data-tag="p" id="p-fey-40o-06a" class="p">替换的表名</p></td></tr><tr data-tag="tr" id="tr-j3g-5b5-ip5" class="tr"><td data-tag="td" id="td-35e-73m-x7o" class="td"><p data-tag="p" id="p-8ui-wms-llt" class="p">column_name_list</p></td><td data-tag="td" id="td-43e-f5b-ylw" class="td"><p id="p-qfs-qzt-67g">指定替换数据的列</p></td></tr><tr data-tag="tr" id="tr-idi-tgv-03m" class="tr"><td data-tag="td" id="td-lgz-bc1-rrj" class="td"><p data-tag="p" id="p-ikz-sb3-4az" class="p">partition_name_list</p></td><td data-tag="td" id="td-elz-ger-odx" class="td"><p data-tag="p" id="p-a80-76u-0uf" class="p">替换表指定的分区名</p></td></tr></tbody></table> ### 示例本示例基于如下表定义。 ~~~ OceanBase(admin@test)>create table test (c1 int primary key, c2 varchar(40)); Query OK, 0 rows affected (0.23 sec) ~~~ 1. 替换 test 表中的行一和行二的值分别为 'hello alibaba' 和 'hello ob'。 ~~~ OceanBase(admin@test)>REPLACE INTO test VALUES (1, 'hello alibaba'),(2, 'hello ob'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 ~~~ 2. 查看 test 表中的行一和行二内容。 ~~~ OceanBase(admin@test)>SELECT * FROM test; +----+---------------+ | c1 | c2 | +----+---------------+ | 1 | hello alibaba | | 2 | hello ob | +----+---------------+ 2 rows in set (0.00 sec) ~~~ 3. 替换表 test 中的行三和行二的内容分别为 'hello alibaba' 和 'hello oceanbase'。 ~~~ OceanBase(admin@test)>REPLACE INTO test VALUES (3, 'hello alibaba'),(2, 'hello oceanbase'); Query OK, 3 rows affected (0.00 sec) Records: 2 Duplicates: 1 Warnings: 0 ~~~ 4. 查看 test 表中的行一、行二和行三的内容。 ~~~ OceanBase(admin@test)>SELECT * FROM test; +----+-----------------+ | c1 | c2 | +----+-----------------+ | 1 | hello alibaba | | 2 | hello oceanbase | | 3 | hello alibaba | +----+-----------------+ 3 rows in set (0.00 sec) ~~~