用实例验证MySQL对批量提交优化效果

公众号请关注ElseF，ElseF(Else Figure)，是一个专注于「分享」国内外高质量的科技类文章和新闻的自媒体。维护者主要来自国内外大型互联网公司和创业公司，主要专注于与互联网相关的精彩内容。

微信公众号

接上一篇对MySQL内部对批量提交的优化的分析，本文通过实际测试看一下内部执行SQL时是否会做优化处理。MySQL版本：Mac 5.7 .21-log，mysql-connector-java版本：5.1.43，jdbc url开启rewriteBatchedStatements=true

正常的批量提交

如果是合法的SQL语句，则在batchUpdate提交时无论下面哪种方式都会成功执行。 SQL1

private static final String INSERT_SQL1 = "INSERT INTO %s"
            + "(app_id, group_id, user_id, status, member_role, inviter_id, "
            + "group_member_setting, nickname, create_time, update_time, last_join_time) "
            + "VALUES(:app_id, :group_id, :user_id, :status, :member_role, :inviter_id, "
            + ":group_member_setting, :nickname, :create_time, :update_time, :last_join_time) "
            + "ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), "
            + "inviter_id = VALUES(inviter_id), group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), "
            + "update_time = VALUES(update_time), last_join_time = VALUES(last_join_time)";

SQL2

private static final String INSERT_SQL2 = "INSERT INTO %s"
            + "(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`,"
            + " `create_time`,`update_time`,`last_join_time`)"
            + " VALUES(:app_id,:group_id,:user_id,:status,:member_role,:inviter_id,:group_member_setting,:nickname,"
            + " :create_time,:update_time,:last_join_time) "
            + " ON DUPLICATE KEY UPDATE `status`=:status,`member_role` =:member_role,"
            + " `inviter_id`=:inviter_id,`group_member_setting`=:group_member_setting,`nickname`=:nickname,"
            + " `update_time`=:update_time,`last_join_time`=:last_join_time ";

二者的区别在于，一个使用了MySQL内置的VALUES()函数，一个通过占位符的方式，这两种方式spring-jdbc均可以正常处理。我们需要验证在MySQL内部前一种写法在批量提交时是否会被拼成一个SQL执行，而后者由于写法的限制，只能是当做多条SQL执行。

场景

我们模拟batchUpdate更新三条数据的case，其中包括两个已经存在的唯一索引的数据，即其中第一第二条SQL数据的唯一索引冲突，并且和数据库内已存在数据唯一索引冲突，第三条SQL只和数据库内已存在数据唯一索引冲突。

SQL1 BINLOG

#181222 14:17:14 server id 1  end_log_pos 15453 CRC32 0x8934853d    Anonymous_GTID  last_committed=34   sequence_number=35  rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 15453
#181222 14:17:14 server id 1  end_log_pos 15525 CRC32 0x7adedeaa    Query   thread_id=59    exec_time=0 error_code=0
SET TIMESTAMP=1545459434/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=224/*!*/;
BEGIN
/*!*/;
# at 15525
#181222 14:17:14 server id 1  end_log_pos 15596 CRC32 0x822e3744    Table_map: `test`.`group_member_0` mapped to number 108
# at 15596
#181222 14:17:14 server id 1  end_log_pos 15706 CRC32 0xd36b582d    Write_rows: table id 108
# at 15706
#181222 14:17:14 server id 1  end_log_pos 15892 CRC32 0x1bd3d5fb    Update_rows: table id 108
# at 15892
#181222 14:17:14 server id 1  end_log_pos 16002 CRC32 0x53f00f84    Write_rows: table id 108 flags: STMT_END_F

BINLOG ' 6tYdXBMBAAAARwAAAOw8AAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB CAEICAgCAAQQAEQ3LoI= 6tYdXB4BAAAAbgAAAFo9AAAAAGwAAAAAAAAAAgAM//8A8MMAAAAAAAAAAgAAAG8AAAAAAAAAeQAA AAAAAAAAAAAAAAAAAAAAAXkAAAAAAAAAAimCj9RnAQAAKYKP1GcBAAApgo/UZwEAAC1Ya9M= 6tYdXB8BAAAAugAAABQ+AAAAAGwAAAAAAAAAAgAM/////wDwwwAAAAAAAAACAAAAbwAAAAAAAAB5 AAAAAAAAAAAAAAAAAAAAAAABeQAAAAAAAAACKYKP1GcBAAApgo/UZwEAACmCj9RnAQAAAPDDAAAA AAAAAAIAAABvAAAAAAAAAHkAAAAAAAAAAAAAAAAAAAAAAAF5AAAAAAAAAAEtgo/UZwEAACmCj9Rn AQAALYKP1GcBAAD71dMb 6tYdXB4BAAAAbgAAAII+AAAAAGwAAAAAAAEAAgAM//8A8MQAAAAAAAAAAgAAAG8AAAAAAAAAegAA AAAAAAAAAAAAAAAAAAAAAXkAAAAAAAAAAS6Cj9RnAQAALoKP1GcBAAAugo/UZwEAAIQP8FM= '/*!*/;
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=195 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=195 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=195 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434029 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434025 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434029 /* LONGINT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=196 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=122 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=121 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545459434030 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545459434030 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545459434030 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 16002
#181222 14:17:14 server id 1  end_log_pos 16033 CRC32 0x55250dca    Xid = 678
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

SQL2 BINLOG

#181222 14:40:21 server id 1  end_log_pos 16098 CRC32 0xca744540    Anonymous_GTID  last_committed=35   sequence_number=36  rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 16098
#181222 14:40:21 server id 1  end_log_pos 16170 CRC32 0xfa9e1fa9    Query   thread_id=60    exec_time=0 error_code=0
SET TIMESTAMP=1545460821/*!*/;
BEGIN
/*!*/;
# at 16170
#181222 14:40:21 server id 1  end_log_pos 16241 CRC32 0x0e8cb1c9    Table_map: `test`.`group_member_0` mapped to number 108
# at 16241
#181222 14:40:21 server id 1  end_log_pos 16351 CRC32 0x3b600ade    Write_rows: table id 108 flags: STMT_END_F

BINLOG ' VdwdXBMBAAAARwAAAHE/AAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB CAEICAgCAAQQAMmxjA4= VdwdXB4BAAAAbgAAAN8/AAAAAGwAAAAAAAEAAgAM//8A8MYAAAAAAAAAAgAAAG8AAAAAAAAAewAA AAAAAAAAAAAAAAAAAAAAAXsAAAAAAAAAAqiqpNRnAQAAqKqk1GcBAACoqqTUZwEAAN4KYDs= '/*!*/;
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=198 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 16351
#181222 14:40:21 server id 1  end_log_pos 16382 CRC32 0xe1e8cf20    Xid = 691
COMMIT/*!*/;
# at 16382
#181222 14:40:21 server id 1  end_log_pos 16447 CRC32 0x686073e1    Anonymous_GTID  last_committed=36   sequence_number=37  rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 16447
#181222 14:40:21 server id 1  end_log_pos 16519 CRC32 0x4ead8cf7    Query   thread_id=60    exec_time=0 error_code=0
SET TIMESTAMP=1545460821/*!*/;
BEGIN
/*!*/;
# at 16519
#181222 14:40:21 server id 1  end_log_pos 16590 CRC32 0x2fb01039    Table_map: `test`.`group_member_0` mapped to number 108
# at 16590
#181222 14:40:21 server id 1  end_log_pos 16776 CRC32 0xfbb986f2    Update_rows: table id 108 flags: STMT_END_F

BINLOG ' VdwdXBMBAAAARwAAAM5AAAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB CAEICAgCAAQQADkQsC8= VdwdXB8BAAAAugAAAIhBAAAAAGwAAAAAAAEAAgAM/////wDwxgAAAAAAAAACAAAAbwAAAAAAAAB7 AAAAAAAAAAAAAAAAAAAAAAABewAAAAAAAAACqKqk1GcBAACoqqTUZwEAAKiqpNRnAQAAAPDGAAAA AAAAAAIAAABvAAAAAAAAAHsAAAAAAAAAAAAAAAAAAAAAAAF7AAAAAAAAAAGtqqTUZwEAAKiqpNRn AQAAraqk1GcBAADyhrn7 '/*!*/;
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=198 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=2 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=198 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820648 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 16776
#181222 14:40:21 server id 1  end_log_pos 16807 CRC32 0x226282d0    Xid = 692
COMMIT/*!*/;
# at 16807
#181222 14:40:21 server id 1  end_log_pos 16872 CRC32 0x4991ac7b    Anonymous_GTID  last_committed=37   sequence_number=38  rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 16872
#181222 14:40:21 server id 1  end_log_pos 16944 CRC32 0x75c65e75    Query   thread_id=60    exec_time=0 error_code=0
SET TIMESTAMP=1545460821/*!*/;
BEGIN
/*!*/;
# at 16944
#181222 14:40:21 server id 1  end_log_pos 17015 CRC32 0x22b2e21d    Table_map: `test`.`group_member_0` mapped to number 108
# at 17015
#181222 14:40:21 server id 1  end_log_pos 17125 CRC32 0xec7902b1    Write_rows: table id 108 flags: STMT_END_F

BINLOG ' VdwdXBMBAAAARwAAAHdCAAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB CAEICAgCAAQQAB3isiI= VdwdXB4BAAAAbgAAAOVCAAAAAGwAAAAAAAEAAgAM//8A8MgAAAAAAAAAAgAAAG8AAAAAAAAAfAAA AAAAAAAAAAAAAAAAAAAAAXsAAAAAAAAAAa2qpNRnAQAAraqk1GcBAACtqqTUZwEAALECeew= '/*!*/;
### INSERT INTO `test`.`group_member_0`
### SET
###   @1=200 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=124 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=123 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545460820653 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 17125
#181222 14:40:21 server id 1  end_log_pos 17156 CRC32 0xbce285e4    Xid = 693
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

SQL1 general_log

2018-12-22T06:17:14.670209Z        59 Connect   root@localhost on test using TCP/IP
2018-12-22T06:17:14.702121Z        59 Query     /* mysql-connector-java-5.1.43 ( Revision: 1d14b699eff3e6112aaedb1cbe5a151ab81f98f1 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@have_query_cache AS have_query_cache, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2018-12-22T06:17:14.736932Z        59 Query     SHOW WARNINGS
2018-12-22T06:17:14.739439Z        59 Query     SELECT @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type
2018-12-22T06:17:14.739820Z        59 Query     SHOW WARNINGS
2018-12-22T06:17:14.758843Z        59 Query     SET NAMES utf8mb4
2018-12-22T06:17:14.759231Z        59 Query     SET character_set_results = NULL
2018-12-22T06:17:14.759699Z        59 Query     SELECT @@session.autocommit
2018-12-22T06:17:14.787615Z        59 Query     select @@session.tx_read_only
2018-12-22T06:17:14.789080Z        59 Query     INSERT INTO group_member_0(app_id, group_id, user_id, status, member_role, inviter_id, group_member_setting, nickname, create_time, update_time, last_join_time) VALUES(2, 111, 121, 1, 2, 121, 0, '', 1545459434025, 1545459434025, 1545459434025) ,(2, 111, 121, 1, 1, 121, 0, '', 1545459434029, 1545459434029, 1545459434029) ,(2, 111, 122, 1, 1, 121, 0, '', 1545459434030, 1545459434030, 1545459434030)  ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), inviter_id = VALUES(inviter_id), group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), update_time = VALUES(update_time), last_join_time = VALUES(last_join_time)
2018-12-22T06:17:14.820540Z        59 Quit

SQL2 general_log

2018-12-22T06:40:21.263072Z        60 Connect   root@localhost on test using TCP/IP
2018-12-22T06:40:21.275264Z        60 Query     /* mysql-connector-java-5.1.43 ( Revision: 1d14b699eff3e6112aaedb1cbe5a151ab81f98f1 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@have_query_cache AS have_query_cache, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2018-12-22T06:40:21.327249Z        60 Query     SHOW WARNINGS
2018-12-22T06:40:21.329548Z        60 Query     SELECT @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type
2018-12-22T06:40:21.331454Z        60 Query     SHOW WARNINGS
2018-12-22T06:40:21.338302Z        60 Query     SET NAMES utf8mb4
2018-12-22T06:40:21.338637Z        60 Query     SET character_set_results = NULL
2018-12-22T06:40:21.339027Z        60 Query     SELECT @@session.autocommit
2018-12-22T06:40:21.364224Z        60 Query     select @@session.tx_read_only
2018-12-22T06:40:21.364713Z        60 Query     INSERT INTO group_member_0(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`, `create_time`,`update_time`,`last_join_time`) VALUES(2, 111,123,1,2,123,0,'', 1545460820648,1545460820648,1545460820648)  ON DUPLICATE KEY UPDATE `status`=1,`member_role` =2, `inviter_id`=123,`group_member_setting`=0,`nickname`='', `update_time`=1545460820648,`last_join_time`=1545460820648
2018-12-22T06:40:21.404492Z        60 Query     INSERT INTO group_member_0(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`, `create_time`,`update_time`,`last_join_time`) VALUES(2, 111,123,1,1,123,0,'', 1545460820653,1545460820653,1545460820653)  ON DUPLICATE KEY UPDATE `status`=1,`member_role` =1, `inviter_id`=123,`group_member_setting`=0,`nickname`='', `update_time`=1545460820653,`last_join_time`=1545460820653
2018-12-22T06:40:21.421137Z        60 Query     INSERT INTO group_member_0(`app_id`,`group_id`,`user_id`,`status`,`member_role`,`inviter_id`,`group_member_setting`,`nickname`, `create_time`,`update_time`,`last_join_time`) VALUES(2, 111,124,1,1,123,0,'', 1545460820653,1545460820653,1545460820653)  ON DUPLICATE KEY UPDATE `status`=1,`member_role` =1, `inviter_id`=123,`group_member_setting`=0,`nickname`='', `update_time`=1545460820653,`last_join_time`=1545460820653
2018-12-22T06:40:21.438568Z        60 Quit

总结

可以通过上面的BINLOG和general_log看到第一个SQL的提交方式MySQL内部使用了事务来处理，并且合并成了一条SQL。默认情况下SELECT @@session.autocommit返回值为1，即开启了自动提交，而由于我们使用的是writer库，所以select @@session.tx_read_only返回值为0，即不是read_only模式。

原子插入

这里我们通过命令行进行测试，将三条更新操作写成一条SQL的方式，使用了VALUES(), (),..()这样的语法并且在ON-DUPLICATE-KEY-UPDATE中也使用了VALUES()函数。

mysql> INSERT INTO group_member_0(app_id, group_id, user_id, status, member_role, inviter_id, group_member_setting, nickname, create_time, update_time, last_join_time) 
VALUES
(2, 111, 115, 1, 1, 101, 0, '', 1545189948076, 1545189948076, 1545189948076), 
(2, 111, 115, 1, 1, 101, 0, '', 1545189948078, 1545189948076, 1545189948078), 
(2, 111, 116, 1, 1, 101, 0, '', 1545189948079, 1545189948079, 1545189948079) 
ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), inviter_id = VALUES(inviter_id), 
group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), update_time = VALUES(update_time), last_join_time = VALUES(last_join_time);

BINLOG

#181221 11:59:33 server id 1  end_log_pos 12539 CRC32 0x0ea661dd    Anonymous_GTID  last_committed=28   sequence_number=29  rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 12539
#181221 11:59:33 server id 1  end_log_pos 12611 CRC32 0x5e5088d8    Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1545364773/*!*/;
BEGIN
/*!*/;
# at 12611
#181221 11:59:33 server id 1  end_log_pos 12682 CRC32 0xf5fcda04    Table_map: `test`.`group_member_0` mapped to number 108
# at 12682
#181221 11:59:33 server id 1  end_log_pos 13164 CRC32 0x9ca09cf3    Update_rows: table id 108 flags: STMT_END_F

BINLOG ' JWUcXBMBAAAARwAAAIoxAAAAAGwAAAAAAAEABHRlc3QADmdyb3VwX21lbWJlcl8wAAwIAwgIDwgB CAEICAgCAAQQAATa/PU= JWUcXB8BAAAA4gEAAGwzAAAAAGwAAAAAAAEAAgAM/////wDwogAAAAAAAAACAAAAbwAAAAAAAABz AAAAAAAAAAAAAAAAAAAAAAABcwAAAAAAAAABXyzpzmcBAAAWT53OZwEAAF8s6c5nAQAAAPCiAAAA AAAAAAIAAABvAAAAAAAAAHMAAAAAAAAAAAAAAAAAAAAAAAFlAAAAAAAAAAGsen/EZwEAABZPnc5n AQAArHp/xGcBAAAA8KIAAAAAAAAAAgAAAG8AAAAAAAAAcwAAAAAAAAAAAAAAAAAAAAAAAWUAAAAA AAAAAax6f8RnAQAAFk+dzmcBAACsen/EZwEAAADwogAAAAAAAAACAAAAbwAAAAAAAABzAAAAAAAA AAAAAAAAAAAAAAABZQAAAAAAAAABrnp/xGcBAAAWT53OZwEAAKx6f8RnAQAAAPCkAAAAAAAAAAIA AABvAAAAAAAAAHQAAAAAAAAAAAAAAAAAAAAAAAFzAAAAAAAAAAHCg+jOZwEAABlPnc5nAQAAwoPo zmcBAAAA8KQAAAAAAAAAAgAAAG8AAAAAAAAAdAAAAAAAAAAAAAAAAAAAAAAAAWUAAAAAAAAAAa96 f8RnAQAAGU+dzmcBAACven/EZwEAAPOcoJw= '/*!*/;
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545364647007 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545364647007 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=162 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948078 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675158 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948076 /* LONGINT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`group_member_0`
### WHERE
###   @1=164 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=116 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=115 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545364603842 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675161 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545364603842 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=164 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
###   @3=111 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @4=116 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(1024) meta=1024 nullable=1 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @8=101 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @9=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @10=1545189948079 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @11=1545359675161 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @12=1545189948079 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 13164
#181221 11:59:33 server id 1  end_log_pos 13195 CRC32 0xea60a142    Xid = 620
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到默认如果拼成了一条SQL则内部会用事务机制来保证原子性，但是底部仍然是通过三条来执行。

general_log

2018-12-21T03:59:33.365923Z         2 Query     INSERT INTO group_member_0(app_id, group_id, user_id, status, member_role, inviter_id, group_member_setting, nickname, create_time, update_time, last_join_time) 
VALUES(2, 111, 115, 1, 1, 101, 0, '', 1545189948076, 1545189948076, 1545189948076), (2, 111, 115, 1, 1, 101, 0, '', 1545189948078, 1545189948076, 1545189948078), (2, 111, 116, 1, 1, 101, 0, '', 1545189948079, 1545189948079, 1545189948079) 
ON DUPLICATE KEY UPDATE status = VALUES(status), member_role = VALUES(member_role), inviter_id = VALUES(inviter_id), group_member_setting = VALUES(group_member_setting), nickname = VALUES(nickname), update_time = VALUES(update_time), last_join_time = VALUES(last_join_time)

这里和BINGLOG里有一点出入是因为general_log并不是真正执行时候的样子，而是收到SQL的样子，所以可能感觉是执行了一条，其实仍然是三条只不过通过事务保证了原子性。

本文首次发布于 ElseF’s Blog, 作者 @stuartlau ,转载请保留原文链接.

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,907评论 6赞 506
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,987评论 3赞 395
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,298评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,586评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,633评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,488评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,275评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,176评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,619评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,819评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,932评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,655评论 5赞 346
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,265评论 3赞 329
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,871评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,994评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,095评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,884评论 2赞 354