5.6分区表更新条数和查询条数不一致,在普通表和5.7没有该问题

一个表与本表关联的更新,查出来是32条,但是更新时条数不对,数据库版本是5.6.38-log,将数据导入不是分区的表,或者5.7环境都没有问题,表结构及具体执行过程如下:
CREATE TABLE `t2` (
`col1` bigint(20) NOT NULL DEFAULT '0' ,
`col2` bigint(20) NOT NULL DEFAULT '0' ,
`col3` bigint(20) NOT NULL DEFAULT '0' ,
`col4` varchar(64) NOT NULL DEFAULT '' ,
`col5` tinyint(4) NOT NULL ,
`col6` bigint(20) NOT NULL DEFAULT '0' ,
`col7` tinyint(4) NOT NULL DEFAULT '0',
`col8` bigint(20) NOT NULL DEFAULT '0' ,
`col9` tinyint(4) NOT NULL DEFAULT '0' ,
`col10` tinyint(4) NOT NULL DEFAULT '0' ,
`col11` tinyint(4) NOT NULL DEFAULT '0',
`col12` tinyint(4) NOT NULL DEFAULT '0' ,
`col13` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`col14` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`col15` bigint(20) DEFAULT '0' ,
`col16` varchar(64) DEFAULT '' ,
`col17` varchar(64) DEFAULT '' ,
`col18` varchar(50) DEFAULT '' ,
`col19` varchar(100) DEFAULT '' ,
`col20` varchar(250) DEFAULT '' ,
`col21` varchar(250) DEFAULT '' ,
`col22` varchar(100) DEFAULT '' ,
`col23` timestamp NULL,
`col24` varchar(20) DEFAULT '' ,
`col25` timestamp NULL ,
`col26` varchar(500) DEFAULT '' ,
`col27` tinyint(4) NOT NULL DEFAULT '0' ,
PRIMARY KEY (`col1`),
KEY `index_col2` (`col2`),
KEY `index_col6` (`col6`),
KEY `index_col7` (`col7`),
KEY `index_col9` (`col9`),
KEY `index_col11` (`col11`),
KEY `index_col14` (`col14`),
KEY `index_col15` (`col15`),
KEY `index_col25` (`col25`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (col1)
PARTITIONS 200 */
;
mysql> SELECT
-> c.col1,b.col1
-> FROM
-> (SELECT
-> col1,
-> t.`col2`,
-> t.`col8`,
-> t.`col20`,
-> t.`col10`,
-> t.`col11`,
-> t.`col21`
-> FROM
-> `t2` t
-> WHERE t.`col9` = 2
-> AND t.`col11` = 3
-> AND t.`col10` = 5) AS b
-> INNER JOIN `t2` c
-> ON b.col2 = c.`col2`
-> AND b.`col8` = c.`col8`
-> AND c.`col10` = b.`col10`
-> AND c.`col11` = b.`col11`
-> AND c.`col9` = 3
-> AND c.`col11` = 3
-> AND c.`col10` = 5
-> AND c.`col20` = 'null' ;
+-------------------+-------------------+
| col1 | col1 |
+-------------------+-------------------+
| 26786320532139698 | 26786320532139598 |
| 26787003532139996 | 26787003522188996 |
| 26787222722189296 | 26787222722189196 |
| 26787255832140812 | 26787255832140712 |
| 26787605432143994 | 26787605422193394 |
| 26787615532144344 | 26787615532144244 |
| 26787651032144797 | 26787651022194097 |
| 26787670332145298 | 26787670332145198 |
| 26787729822194998 | 26787729832146598 |
| 26820951022183597 | 26820951022183497 |
| 26822286432136397 | 26822286422188197 |
| 26822652532140397 | 26822652522191997 |
| 26823866422198397 | 26823866422198297 |
| 26824889432123597 | 26824889432123497 |
| 26825797332125944 | 26825797332125844 |
| 26832711022196596 | 26832711022196496 |
| 26840779122159744 | 26840779122159644 |
| 26840814222160144 | 26840814232108544 |
| 26840904822161744 | 26840904822161644 |
| 26841146732113944 | 26841146732113844 |
| 26841683122161244 | 26841683132113944 |
| 26843695032191844 | 26843695032191744 |
| 26847601432193344 | 26847601422130144 |
| 26848384522139613 | 26848384532102113 |
| 26848479132103413 | 26848479132103313 |
| 26849103122146213 | 26849103122146113 |
| 26849132522146613 | 26849132522146513 |
| 26849651922150513 | 26849651932111913 |
| 26849764132112713 | 26849764122151813 |
| 26849908822153013 | 26849908832114213 |
| 26850188132132713 | 26850188132132613 |
| 26850277532166844 | 26850277522197644 |
+-------------------+-------------------+
32 rows in set (1.26 sec)

mysql> UPDATE
-> `t2` c
-> INNER JOIN
-> (SELECT
-> t.`col2`,
-> t.`col8`,
-> t.`col20`,
-> t.`col10`,
-> t.`col11`,
-> t.`col21`
-> FROM
-> `t2` t
-> WHERE t.`col9` = 2
-> AND t.`col11` = 3
-> AND t.`col10` = 5) AS b
-> ON b.col2 = c.`col2`
-> AND b.`col8` = c.`col8` SET c.`col20` = b.`col20`,
-> c.`col21` = b.`col21`,
-> c.`col25` = NOW()
-> WHERE c.`col9` = 3
-> AND c.`col11` = 3
-> AND c.`col10` = 5
-> AND c.`col20` = 'null' ;
Query OK, 1 row affected (1.65 sec)
Rows matched: 1 Changed: 1 Warnings: 0
已邀请:

要回复问题请先登录注册