{"id":10006149,"date":"2022-08-09T13:09:01","date_gmt":"2022-08-09T05:09:01","guid":{"rendered":"https:\/\/ityet.com\/?p=10006149"},"modified":"2022-08-09T13:09:01","modified_gmt":"2022-08-09T05:09:01","slug":"%e9%9d%a2%e8%af%95%e5%ae%98%ef%bc%9amysql-%e4%b8%ad-update-%e6%9b%b4%e6%96%b0%ef%bc%8c%e6%95%b0%e6%8d%ae%e4%b8%8e%e5%8e%9f%e6%95%b0%e6%8d%ae%e7%9b%b8%e5%90%8c%e6%97%b6%e4%bc%9a%e6%89%a7%e8%a1%8c","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10006149","title":{"rendered":"\u9762\u8bd5\u5b98\uff1aMySQL \u4e2d update \u66f4\u65b0\uff0c\u6570\u636e\u4e0e\u539f\u6570\u636e\u76f8\u540c\u65f6\u4f1a\u6267\u884c\u5417\uff1f\u5927\u90e8\u5206\u4eba\u7b54\u4e0d\u4e0a\u6765\uff01"},"content":{"rendered":"<p>\u6765\u6e90\uff1a<a href=\"https:\/\/link.segmentfault.com\/?enc=cu7xeMtp48uYbJRKZvPYrw%3D%3D.san0IVs8LzmtU%2FLLafbFirkpES2NfOuXgiDOsCpYABA5J4reb0f4ksI8k93DrSux\" rel=\"nofollow\">https:\/\/developer.aliyun.com\/&#8230;<\/a><\/p>\n<h2>\u80cc\u666f<\/h2>\n<p>\u672c\u6587\u4e3b\u8981\u6d4b\u8bd5MySQL\u6267\u884cupdate\u8bed\u53e5\u65f6\uff0c\u9488\u5bf9\u4e0e\u539f\u6570\u636e\uff08\u5373\u672a\u4fee\u6539\uff09\u76f8\u540c\u7684update\u8bed\u53e5\u4f1a\u5728MySQL\u5185\u90e8\u91cd\u65b0\u6267\u884c\u5417\uff1f<\/p>\n<h2>\u6d4b\u8bd5\u73af\u5883<\/h2>\n<ul>\n<li>MySQL5.7.25<\/li>\n<li>Centos 7.4<\/li>\n<\/ul>\n<h2>binlog_format\u4e3aROW<\/h2>\n<h3>\u53c2\u6570<\/h3>\n<pre><code>root@localhost : (none) 04:53:15&gt; show variables like 'binlog_row_image';\n+------------------+-------+\n| Variable_name    | Value |\n+------------------+-------+\n| binlog_row_image | FULL  |\n+------------------+-------+\n1 row in set (0.00 sec)\n\nroot@localhost : (none) 04:53:49&gt; show variables like 'binlog_format'; \n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| binlog_format | ROW   |\n+---------------+-------+\n1 row in set (0.00 sec)\n\nroot@localhost : test 05:15:14&gt; show variables like 'transaction_isolation';\n+-----------------------+-----------------+\n| Variable_name         | Value           |\n+-----------------------+-----------------+\n| transaction_isolation | REPEATABLE-READ |\n+-----------------------+-----------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h3>\u6d4b\u8bd5\u6b65\u9aa4<\/h3>\n<h4>session1<\/h4>\n<pre><code>root@localhost : test 04:49:48&gt; begin;\nQuery OK, 0 rows affected (0.00 sec)\n\nroot@localhost : test 04:49:52&gt; select * from test where id =1;\n+----+------+------+------+\n| id | sid  | mid  | name |\n+----+------+------+------+\n|  1 |  999 |  871 | NW   |\n+----+------+------+------+\n1 row in set (0.00 sec)\n\nroot@localhost : (none) 04:54:03&gt; show engine innodb status\/Gshow master status\/G\n...\n---\nLOG\n---\nLog sequence number 12090390\nLog flushed up to   12090390\nPages flushed up to 12090390\nLast checkpoint at  12090381\n0 pending log flushes, 0 pending chkp writes\n33 log i\/o's done, 0.00 log i\/o's\/second\n\n*************************** 1. row ***************************\n             File: mysql-bin.000001\n         Position: 154\n     Binlog_Do_DB: \n Binlog_Ignore_DB: \nExecuted_Gtid_Set: \n1 row in set (0.00 sec)<\/code><\/pre>\n<h4>session2<\/h4>\n<pre><code>root@localhost : test 04:47:45&gt; update test set sid=55 where id =1;\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1  Changed: 1  Warnings: 0\n\nroot@localhost : (none) 04:54:03&gt; show engine innodb status\/Gshow master status\/G\n...\n---\nLOG\n---\nLog sequence number 12091486\nLog flushed up to   12091486\nPages flushed up to 12091486\nLast checkpoint at  12091477\n0 pending log flushes, 0 pending chkp writes\n39 log i\/o's done, 0.00 log i\/o's\/second\n\n*************************** 1. row ***************************\n             File: mysql-bin.000001\n         Position: 500\n     Binlog_Do_DB: \n Binlog_Ignore_DB: \nExecuted_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1\n1 row in set (0.00 sec)<\/code><\/pre>\n<h4>session1<\/h4>\n<pre><code>root@localhost : test 04:49:57&gt; update test set sid=55 where id =1;   \nQuery OK, 0 rows affected (0.00 sec)\nRows matched: 1  Changed: 0  Warnings: 0\n\nroot@localhost : (none) 04:54:03&gt; show engine innodb status\/Gshow master status\/G\n...\n---\nLOG\n---\nLog sequence number 12091486\nLog flushed up to   12091486\nPages flushed up to 12091486\nLast checkpoint at  12091477\n0 pending log flushes, 0 pending chkp writes\n39 log i\/o's done, 0.00 log i\/o's\/second\n\n*************************** 1. row ***************************\n             File: mysql-bin.000001\n         Position: 500\n     Binlog_Do_DB: \n Binlog_Ignore_DB: \nExecuted_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1\n1 row in set (0.00 sec)\n\nroot@localhost : test 04:52:05&gt; select * from test where id =1;\n+----+------+------+------+\n| id | sid  | mid  | name |\n+----+------+------+------+\n|  1 |  999 |  871 | NW   |\n+----+------+------+------+\n1 row in set (0.00 sec)\n\nroot@localhost : test 04:52:42&gt; commit;\nQuery OK, 0 rows affected (0.00 sec)\n\nroot@localhost : test 04:52:52&gt; select * from test where id =1;\n+----+------+------+------+\n| id | sid  | mid  | name |\n+----+------+------+------+\n|  1 |   55 |  871 | NW   |\n+----+------+------+------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h3>\u603b\u7ed3<\/h3>\n<ul>\n<li>\u5728binlog_format=row\u548cbinlog_row_image=FULL\u65f6\uff0c\u7531\u4e8eMySQL \u9700\u8981\u5728 binlog \u91cc\u9762\u8bb0\u5f55\u6240\u6709\u7684\u5b57\u6bb5\uff0c\u6240\u4ee5\u5728\u8bfb\u6570\u636e\u7684\u65f6\u5019\u5c31\u4f1a\u628a\u6240\u6709\u6570\u636e\u90fd\u8bfb\u51fa\u6765\uff0c\u90a3\u4e48\u91cd\u590d\u6570\u636e\u7684update\u4e0d\u4f1a\u6267\u884c\u3002\u5373MySQL \u8c03\u7528\u4e86 InnoDB \u5f15\u64ce\u63d0\u4f9b\u7684\u201c\u4fee\u6539\u4e3a (1,55)\u201d\u8fd9\u4e2a\u63a5\u53e3\uff0c\u4f46\u662f\u5f15\u64ce\u53d1\u73b0\u503c\u4e0e\u539f\u6765\u76f8\u540c\uff0c\u4e0d\u66f4\u65b0\uff0c\u76f4\u63a5\u8fd4\u56de<\/li>\n<\/ul>\n<h2>binlog_format\u4e3aSTATEMENT<\/h2>\n<h3>\u53c2\u6570<\/h3>\n<pre><code>root@localhost : (none) 04:53:15&gt; show variables like 'binlog_row_image';\n+------------------+-------+\n| Variable_name    | Value |\n+------------------+-------+\n| binlog_row_image | FULL  |\n+------------------+-------+\n1 row in set (0.00 sec)\n\nroot@localhost : (none) 05:16:08&gt;  show variables like 'binlog_format';\n+---------------+-----------+\n| Variable_name | Value     |\n+---------------+-----------+\n| binlog_format | STATEMENT |\n+---------------+-----------+\n1 row in set (0.00 sec)\n\nroot@localhost : test 05:15:14&gt; show variables like 'transaction_isolation';\n+-----------------------+-----------------+\n| Variable_name         | Value           |\n+-----------------------+-----------------+\n| transaction_isolation | REPEATABLE-READ |\n+-----------------------+-----------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h3>\u6d4b\u8bd5\u6b65\u9aa4<\/h3>\n<h4>session1<\/h4>\n<pre><code>root@localhost : test 05:16:42&gt; begin;\nQuery OK, 0 rows affected (0.00 sec)\n\nroot@localhost : test 05:16:44&gt; select * from test where id =1;\n+----+------+------+------+\n| id | sid  | mid  | name |\n+----+------+------+------+\n|  1 |  111 |  871 | NW   |\n+----+------+------+------+\n1 row in set (0.00 sec)\n\nroot@localhost : (none) 05:16:51&gt; show engine innodb status\/Gshow master status\/G\n...\n---\nLOG\n---\nLog sequence number 12092582\nLog flushed up to   12092582\nPages flushed up to 12092582\nLast checkpoint at  12092573\n0 pending log flushes, 0 pending chkp writes\n45 log i\/o's done, 0.00 log i\/o's\/second\n\n*************************** 1. row ***************************\n             File: mysql-bin.000001\n         Position: 154\n     Binlog_Do_DB: \n Binlog_Ignore_DB: \nExecuted_Gtid_Set: \n1 row in set (0.00 sec)<\/code><\/pre>\n<h4>session2<\/h4>\n<pre><code>root@localhost : test 05:18:30&gt; update test set sid=999 where id =1;\nQuery OK, 1 row affected (0.00 sec)\nRows matched: 1  Changed: 1  Warnings: 0\n\nroot@localhost : (none) 05:18:47&gt; show engine innodb status\/Gshow master status\/G\n...\n---\nLOG\n---\nLog sequence number 12093678\nLog flushed up to   12093678\nPages flushed up to 12093678\nLast checkpoint at  12093669\n0 pending log flushes, 0 pending chkp writes\n51 log i\/o's done, 0.14 log i\/o's\/second\n\n*************************** 1. row ***************************\n             File: mysql-bin.000001\n         Position: 438\n     Binlog_Do_DB: \n Binlog_Ignore_DB: \nExecuted_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1\n1 row in set (0.00 sec)<\/code><\/pre>\n<h4>session1<\/h4>\n<pre><code>root@localhost : test 05:16:47&gt; update test set sid=999 where id =1;\nQuery OK, 0 rows affected (0.00 sec)\nRows matched: 1  Changed: 0  Warnings: 0\n\nroot@localhost : (none) 05:20:03&gt; show engine innodb status\/Gshow master status\/G\n...\n---\nLOG\n---\nLog sequence number 12094504\nLog flushed up to   12094504\nPages flushed up to 12094504\nLast checkpoint at  12094495\n0 pending log flushes, 0 pending chkp writes\n56 log i\/o's done, 0.00 log i\/o's\/second\n\n*************************** 1. row ***************************\n             File: mysql-bin.000001\n         Position: 438\n     Binlog_Do_DB: \n Binlog_Ignore_DB: \nExecuted_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1\n1 row in set (0.00 sec)\n\nroot@localhost : test 05:19:33&gt; select * from test where id =1;     \n+----+------+------+------+\n| id | sid  | mid  | name |\n+----+------+------+------+\n|  1 |  999 |  871 | NW   |\n+----+------+------+------+\n1 row in set (0.00 sec)\n\nroot@localhost : test 05:20:44&gt; commit;\nQuery OK, 0 rows affected (0.01 sec)\n\nroot@localhost : test 05:20:57&gt; select * from test where id =1;\n+----+------+------+------+\n| id | sid  | mid  | name |\n+----+------+------+------+\n|  1 |  999 |  871 | NW   |\n+----+------+------+------+\n1 row in set (0.00 sec)<\/code><\/pre>\n<h3>\u603b\u7ed3<\/h3>\n<ul>\n<li>\u5728binlog_format=statement\u548cbinlog_row_image=FULL\u65f6\uff0cInnoDB\u5185\u90e8\u8ba4\u771f\u6267\u884c\u4e86update\u8bed\u53e5\uff0c\u5373\u201c\u628a\u8fd9\u4e2a\u503c\u4fee\u6539\u6210 (1,999)\u201c\u8fd9\u4e2a\u64cd\u4f5c\uff0c\u8be5\u52a0\u9501\u7684\u52a0\u9501\uff0c\u8be5\u66f4\u65b0\u7684\u66f4\u65b0\u3002<\/li>\n<\/ul>\n<p><strong>\u8fd1\u671f\u70ed\u6587\u63a8\u8350\uff1a<\/strong><\/p>\n<p>1.<a href=\"https:\/\/link.segmentfault.com\/?enc=ce1MzB4SxtmJsiAeu2K80Q%3D%3D.NXzbUVohzF9sBKzUOWwULF5nM2KKuXVtEQ04medcdKs%3D\" rel=\"nofollow\">1,000+ \u9053 Java\u9762\u8bd5\u9898\u53ca\u7b54\u6848\u6574\u7406(2022\u6700\u65b0\u7248)<\/a><\/p>\n<p>2.<a href=\"https:\/\/link.segmentfault.com\/?enc=oehZtdfGFayPqhe9P%2FjDhQ%3D%3D.zHPs1eJ6T0UdlMFFh6cTrVAWBt980gXsmtY%2FKlgfU11jNc6jvkTnmuJT%2B5%2BnN4wdPX28D0hnCiNRDqpaA5FqkajEH1SZz%2FeklZeU1P%2FaC%2Bo%3D\" rel=\"nofollow\">\u52b2\u7206\uff01Java \u534f\u7a0b\u8981\u6765\u4e86\u3002\u3002\u3002<\/a><\/p>\n<p>3.<a href=\"https:\/\/link.segmentfault.com\/?enc=3359WjhDoAhq%2Fmb%2FEnngfQ%3D%3D.2XL2Cx30idctKwsHFxsdF9yEtJqRg%2Fu9A%2FG6mNRDSnmlolTVxnpL2xiNHvPb7tIL\" rel=\"nofollow\">Spring Boot 2.x \u6559\u7a0b\uff0c\u592a\u5168\u4e86\uff01<\/a><\/p>\n<p>4.<a href=\"https:\/\/link.segmentfault.com\/?enc=ofb%2F%2F6jDkoYsKTzeSjKxIQ%3D%3D.E3yfbC2lln%2F%2BuLr3EDNakLZJbqUCrFgsF4BqwTL%2Ft9lrECav%2BKP%2FwOl4uHzH29ue8CgvFP1BtS8AXDGGJkpPU6NcvgEDhBILStdXGhsK%2FtY%3D\" rel=\"nofollow\">\u522b\u518d\u5199\u6ee1\u5c4f\u7684\u7206\u7206\u7206\u70b8\u7c7b\u4e86\uff0c\u8bd5\u8bd5\u88c5\u9970\u5668\u6a21\u5f0f\uff0c\u8fd9\u624d\u662f\u4f18\u96c5\u7684\u65b9\u5f0f\uff01\uff01<\/a><\/p>\n<p>5.<a href=\"https:\/\/link.segmentfault.com\/?enc=5bOFk87etgfaerd9ykCJow%3D%3D.d7SQavRbBOifDE9ENeBkVDr8No4ES24TUkKgutfoehrT1Ia4GfYHwip0MwkhrZbG6NW5U7RX%2FM%2F4UTqDLaoxfSBfdpcXXgwazAEAUticH1TYz7dQkZqO0KtWiY05GNFn\" rel=\"nofollow\">\u300aJava\u5f00\u53d1\u624b\u518c\uff08\u5d69\u5c71\u7248\uff09\u300b\u6700\u65b0\u53d1\u5e03\uff0c\u901f\u901f\u4e0b\u8f7d\uff01<\/a><\/p>\n<p>\u89c9\u5f97\u4e0d\u9519\uff0c\u522b\u5fd8\u4e86\u968f\u624b\u70b9\u8d5e+\u8f6c\u53d1\u54e6\uff01<\/p>\n<p><a href=\"https:\/\/segmentfault.com\/a\/1190000042288857\">https:\/\/segmentfault.com\/a\/1190000042288857<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6765\u6e90\uff1ahttps:\/\/developer.aliyun.com\/&#8230; \u80cc\u666f \u672c\u6587\u4e3b\u8981\u6d4b\u8bd5MySQL\u6267 [&hellip;]<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-10006149","post","type-post","status-publish","format-standard","hentry"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10006149"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10006149\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10006149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10006149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10006149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}