Shulou(Shulou.com)06/03 Report--

Upsert (update or insert), that is, update or write. Implement the operation mode of upsert in MySQL:

Idea: decide whether to insert or update by determining whether there is a primary key index or a unique index conflict in the inserted record. When a primary key index or a unique index conflict occurs, the update operation is performed, otherwise the insert operation is performed.

Implementation: using ON DUPLICATE KEY UPDATE

Let's take a look at the following implementation process.

Prepare data tables CREATE TABLE `demo` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `a` tinyint (1) unsigned NOT NULL DEFAULT '0statistics, `b` tinyint (1) unsigned NOT NULL DEFAULT' 0records, `c`tinyint (1) unsigned NOT NULL DEFAULT '0situations, `d`tinyint (1) unsigned NOT NULL DEFAULT' 0situations, `e`tinyint (1) unsigned NOT NULL DEFAULT '0tables, `f`tinyint (1) unsigned NOT NULL DEFAULT' 0tables, PRIMARY KEY (`id`), UNIQUE KEY `unq_a_b_ c` (`a`, `b`) `c`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Note: there are two indexes in the table, with id as the primary key index and aQuery bPermec as the federated unique index.

2. Write the initial data insert into test.demo (a recital brecinct c relegation e pencil f) values (1 recital 1 recital 1 meme 1)

At this time, there is unique index data composed of abc hashes: 1, 1, 1, 1, 1.

3. To further implement insert into demo (values (1) ON DUPLICATE KEY UPDATE 2), 2) ON DUPLICATE KEY UPDATE 2, 2, 3, 4, 4, 4, 5, 5, 6, because there are already unique index data composed of three columns of abc: 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, Therefore, it will trigger the operation of UPDATE axiom 2 magical baccalaureate 2 recorder 3 recorder 4 pyrrine 5 fuzz 6 behind the ON DUPLICATE KEY.

So far, the upsert function has been implemented. Please remember the usage of ON DUPLICATE KEY UPDATE.

Realize the batch update of mysql insert into statistic_customer (customer_id,current_period,period_number,client_upload_bill,update_time) values (1mem201604) values), (314) 201604), (314) 201604) (period_number), client_upload_bill=values (client_upload_bill) Update_time=values (update_time) public static function test () {$updateData = [['customer_id' = > 1,' current_period' = > 201604, 'period_number' = > 100,' client_upload_bill' = > 100, 'update_time' = > time (),] ['customer_id' = > 314,' current_period' = > 201604,' period_number' = > 100,' client_upload_bill' = > 100,' update_time' = > time (),], ['customer_id' = > 315' 'current_period' = > 201604,' period_number' = > 100,' client_upload_bill' = > 100,' update_time' = > time (),], ['customer_id' = > 316,' current_period' = > 201611,' period_number' = > 100' 'client_upload_bill' = > 100,' update_time' = > time (),] $sql = self::buildSQL ('statistic_customer', $updateData); DB::insert ($sql);} private static function buildSQL (string $tableName, array $updateData): string {$sql = "insert into {$tableName} ("; $keys = array_keys ($updateData [0]); $sql. = implode (',', $keys); $sql. =') values'; $valuesStr ='' Foreach ($updateData as $value) {$valuesStr. Implode (',', array_values ($value)). ),';} $sql. = rtrim ($valuesStr,','); $sql. ='ON DUPLICATE KEY UPDATE'; $updateStr =''; foreach ($keys as $key) {$updateStr. = "{$key} = values ($key),";} $sql. = rtrim ($updateStr,','); return $sql;}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.