title: Why mysqlbinlog displays [replace into] row events as below
body: <p>AS we know from </p>
  
  <blockquote>
    <p>REPLACE works exactly like INSERT, except that if an old row in the
    table has the same value as a new row for a PRIMARY KEY or a UNIQUE
    index, the old row is deleted before the new row is inserted</p>
  </blockquote>
  
  <p>When I use an account which has  no privileges to delete  to execute replace into ,it reminded me of delete privilege.</p>
  
  <p>However when I use mysqlbinlog to see the contents in the binlog,it confused me.</p>
  
  <p>An example is as follows:</p>
  
  <p>binlog_format=row</p>
  
  <pre><code>root:test&gt; show create table t3\G
  *************************** 1. row ***************************
         Table: t3
  Create Table: CREATE TABLE `t3` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `c1` int(11) DEFAULT NULL,
    `c2` varchar(20) DEFAULT NULL,
    `c3` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uidx_c1` (`c1`)
  ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)
  
  root:test&gt; select * from t3;
  +----+------+------+------+
  | id | c1   | c2   | c3   |
  +----+------+------+------+
  |  1 |    1 | a    |    1 |
  |  2 |    2 | a    |    1 |
  |  8 | NULL | NULL |    1 |
  | 14 |    4 | bb   | NULL |
  | 15 |    5 | bb   | NULL |
  +----+------+------+------+
  5 rows in set (0.01 sec)
  </code></pre>
  
  <p><strong>Case 1:</strong></p>
  
  <pre><code>root:test&gt; select * from t3;
  +----+------+------+------+
  | id | c1   | c2   | c3   |
  +----+------+------+------+
  |  1 |    1 | a    |    1 |
  |  2 |    2 | a    |    1 |
  |  8 | NULL | NULL |    1 |
  | 14 |    4 | bb   | NULL |
  | 15 |    5 | bb   | NULL |
  +----+------+------+------+
  5 rows in set (0.01 sec)
  
  root:test&gt; 
  root:test&gt; replace into t3 (c1,c2,c3) values(5,'bb',3);
  Query OK, 2 rows affected (0.01 sec)
  
  root:test&gt; select * from t3;
  +----+------+------+------+
  | id | c1   | c2   | c3   |
  +----+------+------+------+
  |  1 |    1 | a    |    1 |
  |  2 |    2 | a    |    1 |
  |  8 | NULL | NULL |    1 |
  | 14 |    4 | bb   | NULL |
  | 16 |    5 | bb   |    3 |
  +----+------+------+------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p>mysqlbinlog displays as below:</p>
  
  <pre><code># at 15126
  #161019 15:04:14 server id 22100  end_log_pos 15190 CRC32 0x9bab83d9    Update_rows: table id 234 flags: STMT_END_F
  ### UPDATE `test`.`t3`
  ### WHERE
  ###   @1=15
  ###   @2=5
  ###   @3='bb'
  ###   @4=NULL
  ### SET
  ###   @1=16
  ###   @2=5
  ###   @3='bb'
  ###   @4=3
  # at 15190
  #161019 15:04:14 server id 22100  end_log_pos 15221 CRC32 0x70a88be3    Xid = 177
  COMMIT/*!*/;
  </code></pre>
  
  <p>The mysqlbinlog doesn't display delete and insert .</p>
  
  <p><strong>Case 2: auto_increment=17</strong></p>
  
  <pre><code>root:test&gt; select * from t3;
  +----+------+------+------+
  | id | c1   | c2   | c3   |
  +----+------+------+------+
  |  1 |    1 | a    |    1 |
  |  2 |    2 | a    |    1 |
  |  8 | NULL | NULL |    1 |
  | 14 |    4 | bb   | NULL |
  | 16 |    5 | bb   |    3 |
  +----+------+------+------+
  5 rows in set (0.00 sec)
  
  root:test&gt; replace into t3 (id,c1,c2,c3) values(17,5,'bb',3);
  Query OK, 2 rows affected (0.01 sec)
  
  root:test&gt; select * from t3;
  +----+------+------+------+
  | id | c1   | c2   | c3   |
  +----+------+------+------+
  |  1 |    1 | a    |    1 |
  |  2 |    2 | a    |    1 |
  |  8 | NULL | NULL |    1 |
  | 14 |    4 | bb   | NULL |
  | 17 |    5 | bb   |    3 |
  +----+------+------+------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p>mysqlbinlog displays as below:</p>
  
  <pre><code># at 15408
  #161019 15:13:18 server id 22100  end_log_pos 15476 CRC32 0xe200a811    Update_rows: table id 234 flags: STMT_END_F
  ### UPDATE `test`.`t3`
  ### WHERE
  ###   @1=16
  ###   @2=5
  ###   @3='bb'
  ###   @4=3
  ### SET
  ###   @1=17
  ###   @2=5
  ###   @3='bb'
  ###   @4=3
  # at 15476
  #161019 15:13:18 server id 22100  end_log_pos 15507 CRC32 0xe02d7749    Xid = 179
  COMMIT/*!*/;
  </code></pre>
  
  <p>The mysqlbinlog doesn't display delete and insert .</p>
  
  <p><strong>Case 3:auto_increment=18</strong></p>
  
  <pre><code>root:test&gt; select * from t3;
  +----+------+------+------+
  | id | c1   | c2   | c3   |
  +----+------+------+------+
  |  1 |    1 | a    |    1 |
  |  2 |    2 | a    |    1 |
  |  8 | NULL | NULL |    1 |
  | 14 |    4 | bb   | NULL |
  | 17 |    5 | bb   |    3 |
  +----+------+------+------+
  5 rows in set (0.00 sec)
  
  root:test&gt; replace into t3 (id,c1,c2,c3) values(17,5,'cc',4);  
  Query OK, 2 rows affected (0.01 sec)
  
  root:test&gt; select * from t3;
  +----+------+------+------+
  | id | c1   | c2   | c3   |
  +----+------+------+------+
  |  1 |    1 | a    |    1 |
  |  2 |    2 | a    |    1 |
  |  8 | NULL | NULL |    1 |
  | 14 |    4 | bb   | NULL |
  | 17 |    5 | cc   |    4 |
  +----+------+------+------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p>mysqlbinlog displays as below:</p>
  
  <pre><code># at 15745
  #161019 15:30:49 server id 22100  end_log_pos 15796 CRC32 0x258b633a    Write_rows: table id 234 flags: STMT_END_F
  ### DELETE FROM `test`.`t3`
  ### WHERE
  ###   @1=17
  ###   @2=5
  ###   @3='bb'
  ###   @4=3
  ### INSERT INTO `test`.`t3`
  ### SET
  ###   @1=17
  ###   @2=5
  ###   @3='cc'
  ###   @4=4
  # at 15796
  #161019 15:30:49 server id 22100  end_log_pos 15827 CRC32 0xcc4e1740    Xid = 183
  COMMIT/*!*/;
  </code></pre>
  
  <p>The mysqlbinlog displays delete and insert .</p>
  
  <p>Stated thus, it seems when the primary key value is the same ,the binlog use delete+insert .If the primary key value is not the same ,the binlog use update.</p>
  
  <p>Why?</p>