摘要：?jiǎn)螖?shù)據(jù)小量數(shù)據(jù)的更新于插入，操作起來(lái)簡(jiǎn)單，不用太考慮語(yǔ)句怎么書寫。正常來(lái)說(shuō)，按照條件判斷數(shù)據(jù)是否已經(jīng)在表中存在，存在更新，不存在插入。

單數(shù)據(jù)小量數(shù)據(jù)的更新于插入，操作起來(lái)簡(jiǎn)單，不用太考慮SQL語(yǔ)句怎么書寫。正常來(lái)說(shuō)，按照條件判斷數(shù)據(jù)是否已經(jīng)在表中存在，存在更新，不存在插入。如果是N條數(shù)據(jù)的話，執(zhí)行的SQL語(yǔ)句最大值為2N條。當(dāng)N數(shù)值不大的時(shí)候，可以用這種簡(jiǎn)單的方式去更新插入。但是當(dāng)N數(shù)據(jù)量不可預(yù)估，或者很大的時(shí)候，這么做肯定不行，在執(zhí)行這個(gè)操作的時(shí)候，可能會(huì)將MySQL的連接數(shù)占完，造成系統(tǒng)資源被消耗完，影響其他操作。

以MySQL舉例，網(wǎng)絡(luò)上有很多的SQL例子：
一下為兩種批量插入SQL

insert into table （ `column1`,`column2`,`column3` ）values (1,2,3),(2,3,4),(5,6,7)

insert into table （ `column1`,`column2`,`column3` ）select 1,2,3 union all select 2,3,4 union all select 4,5,6

下面是在MySQL5.79中以第二種方式分三次插入2527條數(shù)據(jù)所用耗時(shí)，以供參考

[2018-12-05 10:58:59] 1000 rows affected in 452 ms
[2018-12-05 11:04:54] 1000 rows affected in 389 ms
[2018-12-05 11:06:12] 527 rows affected in 163 ms

批量更新也有幾種方式，
一：

replace into table （ `column1`,`column2`,`column3` ）values (1,2,3),(2,3,4),(5,6,7)

這種相當(dāng)于將數(shù)據(jù)庫(kù)的數(shù)據(jù)與需要插入的數(shù)據(jù)做交集，將交集數(shù)據(jù)刪除再重新插入。表中的自增id會(huì)改變，當(dāng)沒有另外的與數(shù)據(jù)相關(guān)的唯一鍵或表中有其他數(shù)據(jù)需要積累或與其他表有關(guān)聯(lián)關(guān)系的時(shí)候，不適用。
這篇文章有詳細(xì)的介紹

二：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;

這種更新，需要唯一鍵來(lái)限制，同時(shí)更新的字段的值限制比較大，不能自定需要更新字段的數(shù)值。只能按照一定邏輯去更新，或許可以用于簽到計(jì)數(shù)類似的場(chǎng)景。
這篇文章有詳細(xì)的介紹

三：

update table set 
column1 = case  when column2 = 1 then 2 else 3 end ,
column3 = case  when column4 = 1 then 2 else 3 end 
where id in (1,2,3,4)

利用了MySQL的case when 函數(shù)，可以按照不同的條件去為不同的字段更新不同的值,同時(shí)可以給語(yǔ)句限定不同的條件。

下面用PHP實(shí)現(xiàn)以下拼裝SQL語(yǔ)句過(guò)程：

//拼裝按條件批量更新SQL語(yǔ)句
protected function handleUpdate($data, $key)
    {
        if (empty($data) || !is_array($data)) {
            return false;
        }

        $keys_array = array_keys(current($data));

//需要被更新的字段
        $update_column = [
            $keys_array[5],
            $keys_array[6],
            $keys_array[8],
            $keys_array[9],
            $keys_array[16],
            $keys_array[14]
        ];

//更新條件
        $vehicle_id = $keys_array[2];
        $body_color = $keys_array[3];
        $interior_color = $keys_array[4];
        $city_id = $keys_array[11];
        $province_id = $keys_array[10];

        $q = "UPDATE ce SET ";
        foreach ($update_column as $update_colum) {
            $q .= " " . $update_colum . " = CASE ";
            foreach ($data as $value) {
                $value[$update_colum] = !isset($value[$update_colum]) || empty($value[$update_colum])
                    ? 0 : $value[$update_colum];
                $q .= " when " . $vehicle_id . " =" . $value[$vehicle_id]
                    . " and " . $body_color . " = "" . $value[$body_color] . """
                    . " and " . $interior_color . " = "" . $value[$interior_color] . """
                    . " and " . $city_id . " =" . $value[$city_id]
                    . " and " . $province_id . " =" . $value[$province_id] . " then " . $value[$update_colum];
            }
            $q .= " ELSE " . $update_colum . " END, ";
        }
        $q = rtrim($q, ", ");
    }

表結(jié)構(gòu)如下

-- auto-generated definition
create table cd
(
  id                int auto_increment
    primary key,
  brand_id          int default "0"              not null
  comment "車輛品牌id",
  model_id          int default "0"              not null
  comment "車輛車型id",
  vehicle_id        int default "0"              not null
  comment "車輛型號(hào)id",
  body_color        varchar(50) default ""       not null
  comment "車身外觀顏色",
  interior_color    varchar(50) default ""       not null
  comment "車輛內(nèi)飾顏色",
  guide_price       decimal(8, 2) default "0.00" not null
  comment "指導(dǎo)價(jià)",
  province_id       int default "0"              not null
  comment "省份id",
  province          varchar(50) default ""       not null
  comment "省份名稱",
  city_id           int default "0"              not null
  comment "地級(jí)市id",
  city              varchar(50) default ""       not null
  comment "地級(jí)市名稱",
  report_price      decimal(8, 2) default "0.00" not null
  comment "集團(tuán)報(bào)備價(jià)",
  average_price     decimal(8, 2) default "0.00" not null
  comment "平均價(jià)",
  coefficient       float(5, 2) default "0.00"   not null
  comment "系數(shù)",
  price_increase    decimal(8, 2) default "0.00" not null
  comment "加價(jià)額",
  operator          int default "0"              not null
  comment "操作者id",
  created_at        int default "0"              not null
  comment "創(chuàng)建時(shí)間",
  updated_at        int default "0"              not null
  comment "更新時(shí)間",
  constraint unique__index
  unique (vehicle_id, body_color, interior_color, city_id, province_id)
)
  comment "";

一：保證數(shù)據(jù)唯一，只需要確保一個(gè)值即可
例如，只需要保證身份證唯一，即更新的查詢條件只有身份證一個(gè)字段的。簡(jiǎn)單處理可以拿出數(shù)組中的值，然后查詢數(shù)據(jù)庫(kù)，判斷是否存在。

$where_in = array_column($array,"column");//全部數(shù)據(jù)的搜索條件;

$res = mysql_execute("select column from table where column in (".$where_in.")");//MySQL執(zhí)行

$res_in = array_column($res,"column");//按搜索條件查找已經(jīng)在數(shù)據(jù)庫(kù)中的數(shù)據(jù);

$update_arr = array_diff($where_in,$res_in);//獲得不在數(shù)據(jù)庫(kù)中的數(shù)據(jù)

$update_info = array_filter($array,function($item) use($update_arr){
    return in_array($item["column"],$update_arr) ? true : false;
});//獲取更新數(shù)據(jù)

$insert_info = array_filter($array,function($item) use($res_in){
    return in_array($item["column"],$update_arr) ? true : false;
});//獲取插入數(shù)據(jù)

二：多條件篩選
我的解決思路和上面差不多，只是不能從數(shù)據(jù)庫(kù)用 where colum in 這樣的條件去篩選，主要是考慮到索引的問(wèn)題，在表數(shù)據(jù)很大的時(shí)候，不合適。

所以利用Redis的set來(lái)解決，在set中記錄查詢條件，只需要遍歷數(shù)組，判斷當(dāng)前數(shù)據(jù)中的搜索條件是否在Redis鍵中即可。

主要是利用第三方來(lái)存儲(chǔ)條件，當(dāng)然也可以使用MySQL，保證查詢效率即可。

一：避免加載的數(shù)組太大，占用內(nèi)存，可以使用yield進(jìn)行切片，每次輸出定量的數(shù)據(jù)，然后遍歷執(zhí)行。

二：可以使用隊(duì)列，分批次執(zhí)行任務(wù)。利用Redis或者RabbitMQ等。

三：似乎可以嘗試一下swoole的協(xié)程，好像很厲害的樣子。

以上是我在做項(xiàng)目是遇到的問(wèn)題與我的思路，有不同的思路或者覺得不對(duì)不合適的地方歡迎提出。