INSERT · k8s原理與實踐

[TOC] # INSERT ## 描述該語句用于添加一個或多個記錄到表中。 ## 格式 ~~~ INSERT [IGNORE] [INTO] single_table_insert [ON DUPLICATE KEY UPDATE update_asgn_list] single_table_insert: {dml_table_name values_clause | dml_table_name '(' ')' values_clause | dml_table_name '(' column_list ')' values_clause | dml_table_name SET update_asgn_list} dml_table_name: tbl_name [PARTITION (partition_name,...)] values_clause: {{VALUES | VALUE} ({expr | DEFAULT},...) [, ...] | select_stmt} column_list column_name [, ...] update_asgn_list: column_name = expr [, ...] ~~~ ## 參數解釋 **INSERT…ON DUPLICATE KEY UPDATE…****語句執行，affect row的計算：** * 在沒有設置client\_capabilities中CLIENT\_FOUND\_ROWS的情況下： * 作為新行插入的話，affected\_row= 1； * 存在沖突行的情況下，如果更新前后數據相同的話， affected\_row = 0；否則affectd\_row = 2; * 如果設置了CLIENT\_FOUND\_ROWS： * 作為新行插入的話，affected\_row=1； * 如果更新前后數據相同的話，affectd\_row=1； * 如果更新前后數據不相同的話，affected\_row=2; * CLIENT\_FOUND\_ROWS的影響在于：不設置CLIENT\_FOUND\_ROWS的情況下，計算affected\_row的值，只計算實際更新了行數，而設置了CLIENT\_FOUND\_ROWS的話，會把所有touched行數（滿足沖突條件的行）都記上，而不管其是否發生了真正的數據修改。 <table data-tag="table" id="table-ky4-mlo-1pt" class="table"><colgroup width="360" span="1" data-tag="col" id="col-o55-lxx-smv" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="360" span="1" data-tag="col" id="col-y8r-idv-08n" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-teb-2s2-9qe" class="thead"><tr id="tr-j61-527-n8v"><th id="td-298-4md-db8">參數</th><th id="td-2u3-01q-mn4">描述</th></tr></thead><tbody data-tag="tbody" id="tbody-cia-l5o-aqa" class="tbody"><tr data-tag="tr" id="tr-zn2-u95-yll" class="tr"><td data-tag="td" id="td-ri5-8sp-fdd" class="td">IGNORE</td><td data-tag="td" id="td-52y-bsh-8dh" class="td">在INSERT語句執行過程中發生的錯誤將會被忽略。</td></tr><tr data-tag="tr" id="tr-vz8-15l-4w6" class="tr"><td data-tag="td" id="td-izs-zxk-7g3" class="td">column_list</td><td data-tag="td" id="td-eqm-b59-4k5" class="td">用于指定插入數據的列，同時插入多列時，用“,”隔開。</td></tr><tr data-tag="tr" id="tr-33y-dkt-s12" class="tr"><td data-tag="td" id="td-72k-g4e-bld" class="td">tbl_name</td><td data-tag="td" id="td-t5g-mw2-lvj" class="td">指定要插入的表名。</td></tr><tr data-tag="tr" id="tr-fxn-w5l-tv7" class="tr"><td data-tag="td" id="td-ogs-gyj-daa" class="td">partition_name</td><td data-tag="td" id="td-ska-v0k-xpu" class="td">插入表指定的分區名。</td></tr><tr data-tag="tr" id="tr-7bv-6me-mu4" class="tr"><td data-tag="td" id="td-un2-0qv-4ym" class="td">update_asgn_list</td><td data-tag="td" id="td-hat-kpa-0sv" class="td">賦值語句，例如：“c1 = 2”</td></tr><tr data-tag="tr" id="tr-xga-kyq-s15" class="tr"><td data-tag="td" id="td-0mo-chm-b9g" class="td">ON DUPLICATE KEY UPDATE</td><td data-tag="td" id="td-qav-ipc-ez7" class="td">指定對重復主鍵或唯一鍵的處理。如果指定了ON DUPLICATE KEY UPDATE，當要插入的主鍵或唯一鍵有重復時，會用配置值替換待插入的值；如果不指定ON DUPLICATE KEY UPDATE，當要插入的主鍵或唯一鍵有重復時，插入報錯。</td></tr></tbody></table> ## 示例示例表及數據基于以下定義： ~~~ OceanBase(admin@test)>create table t1(c1 int primary key, c2 int) partition by key(c1) partitions 4; Query OK, 0 rows affected (0.16 sec) OceanBase(admin@test)>create table t2(c1 int primary key, c2 int); Query OK, 0 rows affected (0.16 sec) OceanBase(admin@test)>select * from t2; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.06 sec) ~~~ * 向表 t1 中插入一行數數據。 ~~~ OceanBase(admin@test)>insert into t1 values(1,1); Query OK, 1 row affected (0.01 sec) OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.04 sec) ~~~ * 向表 t1 中插入多行數據。 ~~~ OceanBase(admin@test)>insert t1 values(1,1),(2,default),(2+2,3*4); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | NULL | | 4 | 12 | +----+------+ 3 rows in set (0.02 sec) ~~~ * 向表 t1 指定的 p0 分區插入單行數據。 ~~~ OceanBase(admin@test)>insert into t1 partition(p0) (c1) values(5); Query OK, 1 row affected (0.02 sec) OceanBase(admin@test)>select * from t1 partition(p0); +----+------+ | c1 | c2 | +----+------+ | 5 | NULL | +----+------+ 1 row in set (0.01 sec) ~~~ * 將表 t2 的查詢結果作為數據插入表 t1。 ~~~ OceanBase(admin@test)>insert into t1 select * from t2; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 OceanBase(admin@test)>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.01 sec) ~~~ * 向表 t1 中插入重復主鍵值時利用 ON DUPLICATE KEY UPDATE 功能進行值更新 ~~~ OceanBase(admin@test)>insert into t1 values(1,1),(1,2) ON DUPLICATE KEY UPDATE c1=100; Query OK, 3 rows affected (0.01 sec) Records: 2 Duplicates: 1 Warnings: 0 OceanBase(admin@test)>select * from t1; +-----+------+ | c1 | c2 | +-----+------+ | 100 | 1 | +-----+------+ 1 row in set (0.02 sec) ~~~ * 對可更新視圖v的插入值 ~~~ OceanBase(admin@test)>create view v as select * from t1; Query OK, 0 rows affected (0.07 sec) OceanBase(admin@test)>insert into v values(1,1); Query OK, 1 row affected (0.01 sec) OceanBase(admin@test)>select * from v; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.02 sec) ~~~ ## 注意事項 INSERT語句不支持直接對子查詢進行插入操作，比如： `insert into (select * from t1) values(1, 1)；`