问题初见~
当每次我们在在mysql中执行了DML(本文主要关注insert, update, delete, replace)命令后，取得的响应中常常看到有些像affected-rows的东西~
命令行增删改(in mysql 5.7)
比如上面命令行中rows affected..(撕~update的结果貌似有些不同?这个后面详细看看杂怼出来的- -)
对于大家常用的各种MySQL client API中也常常看到他熟悉的身影:
Java:
com.mysql.jdbc.StatementImpl#getUpdateCount
C:
<mysql.h> mysql_affected_rows()
GO:
sql.Result#RowsAffected()
从第一感觉看这个值代表的应该 "DML影响的行数"...
本文的目标就是详细看看这个affected rows到底是什么，并且怎么来的~
服务端&客户端交互~
上面说了的都是客户端的表象，我们知道DML最终会在mysql-server执行，最终影响多少肯定是mysql返回的~所以我们看下客户端和服务端的交互如何传递这个信息
我们向mysql-server发出请求packet，mysql正常情况都会通过返回一个或多个包来对我们的请求进行相应.
对于mysql响应包我们可以宽泛的归类为3类:
- OK_PACKET
- ERROR_PACKET
- EOF_PACKET(deprecated as of MySQL 5.7.5)
因为只有执行成功了我们才会关心影响几行，所以可以猜到affected rows会在OK_PACKET中
OK_Packet
所以从网络来说我们在获取执行结果包后就能拿到`影响行数`，不需要重新发包, 我们可以翻阅上面各语言客户端源码, 看到影响函数就来自OK_PACKET中的这个字段~
服务端怎么得出affected rows~
我们简单看下mysqld如何返回这个ok包(in 5.7)
Insert / Insert..on duplicate key update
sql_insert.cc:
my_ok(thd, info.stats.copied + info.stats.deleted +(thd->get_protocol()->has_client_capability(CLIENT_FOUND_ROWS) ?info.stats.touched : info.stats.updated),id);
my_ok传入的第二个参数就是返回的affacted_rows，我们看到就是对stats根据CLIENT_FOUND_ROWS条件进行组合得到的..所以关键就得看看stats-- COPY_INFO是如何统计的了...- -这里偷懒就看看注释好了~
/**
This class encapsulates a data change operation. There are three such
operations.
-# Insert statements, i.e. INSERT INTO .. VALUES
-# Update statements. UPDATE SET ...
-# Delete statements. Currently this class is not used for delete statements
and thus has not yet been adapted to handle it.
The COPY_INFO structure is used by INSERT/REPLACE code.
The schema of the row counting by the INSERT/INSERT ... ON DUPLICATE KEY
UPDATE code:
If a row is inserted then the `copied` variable is incremented.
If a row is updated by the INSERT ... ON DUPLICATE KEY UPDATE and the
new data differs from the old one then the `copied` and the `updated`
variables are incremented.
The `touched` variable is incremented if a row was touched by the update part
of the INSERT ... ON DUPLICATE KEY UPDATE no matter whether the row
was actually changed or not.
*/
class COPY_INFO :public Sql_alloc
结合注释和上面的代码可以知道
对于`普通Insert`: ok包中affected rows等于实际插入的行数(copied)
对于`Insert on duplicate key update`: 没有重复时等于实际插入行数(copied)，有重复时如果未开启CLIENT_FOUND_ROWS(后面会解释这个), 则返回更新的行+删除的行(deleted+updated), 如果开启CLIENT_FOUND_ROWS，则返回touch到行+删除的行(deleted+touch).....也就是说对于无冲突插入返回插入数, 对于有冲突但值不同返回更新数x2(因为要删除+插入), 对于有冲突且两个值相同且启CLIENT_FOUND_ROWS返回冲突的记录数， 对于未开启时返回0
update
sql_update.cc
my_ok(thd, thd->get_protocol()->has_client_capability(CLIENT_FOUND_ROWS) ?found : updated, id, buff);
对于update且是CLIENT_FOUND_ROWS时affected_rows返回的是found的行数，往上再看下代码found也就是where语句touch的行(对于view with check option不满足视图条件的不算found);
对于update且不是CLIENT_FOUND_ROWS是返回的是实际更新的行数, 如何计算实际更新的行数目就稍微复杂点...- -还在研究 等看懂了新开一篇记录
Delete
sql_delete.cc
delete会返回删除的记录数(in 5.7)
小结~
server在插入和删除时返回操作的行数作为affected_rows, 在更新操作(包括insert on duplicate key update)时基于CLIENT_FOUND_ROWS返回touch到的行 还是 实际更新的行
CLIENT_FOUND_ROWS
上面我们看到取关键作用的东西是CLIENT_FOUND_ROWS，本节说下CLIENT_FOUND_ROWS是什么..
上面我们看到判断这个flag是这样的..
thd->get_protocol()->has_client_capability(CLIENT_FOUND_ROWS)
thd是在每次client向server建立连接设置的~
这里先简单说下客户端和mysql-server建立连接的过程，当我们发起命令连接mysql时
C:
mysql_real_connect(connection,"0.0.0.0","root","","test",3306,NULL,clientflag)
实际客户端和server发生了多次交互...
1) 首先client向server建立了tcp连接, server accept连接后会立刻向client发送InitHankshake包
2) client会根据收到InitHankshake包构造包含用户密码的Auth包给server
3) server各种校验通过后会返回AuthResult给client
回到CLIENT_FOUND_ROWS，在Server回给client的InitHankshake中会包括Server支持的capabilites信息：
Init Handshake packet
通过看源码sql_authentication.cc:541->send_server_handshake_packetsql这个flag是写死为1的(5.7), 不过很多同学并不是直连MySQL，各种数据库中间件Proxy也能改变这个值哦所以注意下(- - 我写过的proxy这个位都是1没黑魔法);
client收到后就会根据用户传入参数配置(比如上面mysql_real_connect的client_flag)参数选择同时server已支持的flag发送Auth包~
server在校验auth包通过后的同时会设置flag到thd，这样就决定了后面能否支持CLIENT_FOUND_ROWS
从high level使用角度看如果server(proxy)没有关闭CLIENT_FOUND_ROWS， 需要在连接时传递CLIENT_FOUND_ROWS才能开启， 各个语言是这样
C:
mysql_real_connect(connection,"0.0.0.0","root","","test",3306,NULL,CLIENT_FOUND_ROWS) 的最后一个参数， 传递了即设置
Java:
jdbc 5.1.x后(注意是jdbc的版本)默认会传递CLIENT_FOUND_ROWS, 也可以通过在jdbc字符串中添加useAffectedRows来强制不传递; 之前版本行为有所不同?
Go:
go-sql-driver默认是不不会传递CLIENT_FOUND_ROWS， 如果想获得的不是更新行而是touch行请在连接DSN中设置clientFoundRows=true
PS: 从上面看到如果server选择支持CLIENT_FOUND_ROWS， 客户端是可以自己选择是否开启的(并且各种语言默认行为不同), 所以对于开发数据库中间件的同学如果想实现多系统间后端连接池的复用是必须根据CLIENT_FOUND_ROWS做连接池分类的。。或者可以选择关闭CLIENT_FOUND_ROWS然后告诉业务不持之, 逃~ - -
总结下
对于客户端使用的同学，请注意建立连接时是否传递CLIENT_FOUND_ROWS，影响是:
传递后, Update/Insert on duplicate key update，只要touch到就算不管有没有更新
没传递, Update/Insert on duplicate key update，必须实际更新才算
另外注意proxy对CLIENT_FOUND_ROWS的支持， 考虑所用老版客户端对是否传递CLIENT_FOUND_ROWS的不同行为...新版里就是本文说的样子- -
PS: 之前写了很多会基于mysql来实现类似compare-and-set的操作
update order set order_state = payed where order_id = 1 and order_state = paying;
update rule set content=x, version = version + 1 where rule_id =1 and version = yy;
因为只要touch就一定会update，没有touch一定不会update所以我理解其实CLIENT_FOUND_ROWS并不关键, 在这个场景不会影响判断是否cas成功(之前在很多地方写了好多类似这样的代码, 有问题就.....)
MISC
前面我们看到mysql命令行客户端连上执行sql后的结果感觉信息量比我们前面说的协议包里头的信息多~多出来信息怎么来的???，这里打算稍微再看下~在看2个case
首先是insert into values然后多个值的时候
insert ignore values
我们看到多了一行Records, Duplicates....我们常常为了性能而使用bulk insert，然后想知道这一组中有几条冲突了?之前我也曾经有过这种想法(记不得是什么需求了)，但抓个包看下
wireshark insert values OK_PACKET
- - 结果发现其实这个是server返回的，跟下sql_insert.cc会发现在有多个values时server会通过OK_PACKET的INFO部分通过文本的方式返回Records:...那行...
而一开始的截图
update matched
中的update返回的Rows matched也同理，是server的sql_update.cc中返回的在ok包中的文本段
而目前C/Go/Java的客户端实现都是不会解析这行文本的- - 所以没法在程序中获取到~
PS: 上面抓包看到Warnings有非文本的计数，所以部分语言
C:
<mysql.h> mysql_warning_count()
Perl-DBI:
https://github.com/perl5-dbi/DBD-mysql/blob/master/t/29warnings.t
是可以获得warning的，但jdbc和go简单看了下没有发现直接获取的方法~- - 不过应该大家都不怎么关心这个吧~
- -： 本文作者还在努力学习mysql中，上面有问题欢迎大家一起讨论~
网友评论
欢迎订阅《摸鱼党的自留地》https://toutiao.io/subject/208382