{"id":2895,"date":"2016-09-25T18:23:47","date_gmt":"2016-09-25T10:23:47","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2895"},"modified":"2016-09-25T18:23:47","modified_gmt":"2016-09-25T10:23:47","slug":"mysql%e7%9a%84warning%e4%b9%8b%e3%80%8cbinlog_format-statement%e3%80%8d","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2895.html","title":{"rendered":"MySQL\u7684Warning\u4e4b\u300cBINLOG_FORMAT = STATEMENT\u300d"},"content":{"rendered":"<p>=Start=<\/p>\n<h5>\u7f18\u7531\uff1a<\/h5>\n<blockquote>\n<p style=\"padding-left: 30px;\"><span style=\"color: #ff0000;\">Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.<\/span> Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.<\/p>\n<\/blockquote>\n<h5>\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n<h6>\u65b9\u6cd51\uff1a\u4fee\u6539binlog_format\u683c\u5f0f\u4e3amixed<\/h6>\n<p>\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u8bbe\u5b9a\u4e3b\u4ece\u590d\u5236\u6a21\u5f0f(<span style=\"color: #ff0000;\"><strong>\u9700\u91cd\u542f\u751f\u6548<\/strong><\/span>)\uff1a<\/p>\n<pre class=\"lang:default decode:true\">log-bin=mysql-bin\n#binlog_format=\"STATEMENT\"\n#binlog_format=\"ROW\"\nbinlog_format=\"MIXED\"<\/pre>\n<p>\u4e5f\u53ef\u4ee5\u5728\u8fd0\u884c\u65f6\u52a8\u6001\u4fee\u6539binlog\u7684\u683c\u5f0f\u3002\u4f8b\u5982\uff1a<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; SET SESSION binlog_format = 'STATEMENT';\nmysql&gt; SET SESSION binlog_format = 'ROW';\nmysql&gt; SET SESSION binlog_format = 'MIXED';\nmysql&gt; SET GLOBAL binlog_format = 'STATEMENT';\nmysql&gt; SET GLOBAL binlog_format = 'ROW';\nmysql&gt; SET GLOBAL binlog_format = 'MIXED';<\/pre>\n<h6>\u65b9\u6cd52\uff1a\u4e0d\u8981\u4f7f\u7528\u8fd9\u7c7bSQL<\/h6>\n<hr \/>\n<p>\u5176\u4e2d\uff0c\u4f7f\u7528\u7b2c1\u79cd\u65b9\u6cd5\uff0c\u9700\u8981\u6ce8\u610f\u4e0b\uff1a<\/p>\n<p>\u5982\u679c\u662f master-&gt;slave \u7ed3\u6784\u7684\u6570\u636e\u5e93\u67b6\u6784\u3002\u5e76\u4e14 slave\u4e0a \u5f00\u542f\u4e86 log_slave_updates \u3002\u90a3\u4e48\u5728master\u4e0a\u4fee\u6539\u5b8cbinlog\u683c\u5f0f\uff0c\u5f00\u542f\u4e86log_slave_updates\u7684\u4ece\u5e93\uff0c\u4f1a\u4e2d\u65ad\u540c\u6b65\u3002\u6240\u4ee5\uff0c\u9700\u8981\u5148\u5728 slave \u4e0a\uff0c\u8bbe\u7f6e binlog_format=mixed \uff0c\u4e4b\u540e\u518d\u5728master\u4e0a\u8bbe\u7f6e\u3002<\/p>\n<h5>\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n<ul>\n<li><a href=\"http:\/\/blog.csdn.net\/longxibendi\/article\/details\/41948941\">http:\/\/blog.csdn.net\/longxibendi\/article\/details\/41948941<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/17057593\/warning-unsafe-statement-written-to-the-binary-log-using-statement-format-since\">http:\/\/stackoverflow.com\/questions\/17057593\/warning-unsafe-statement-written-to-the-binary-log-using-statement-format-since<\/a><\/li>\n<li><a href=\"http:\/\/www.cnblogs.com\/xiaoit\/p\/4546622.html\">http:\/\/www.cnblogs.com\/xiaoit\/p\/4546622.html<\/a><\/li>\n<li><a href=\"http:\/\/www.jb51.net\/article\/84363.htm\">http:\/\/www.jb51.net\/article\/84363.htm<\/a><\/li>\n<\/ul>\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a Warning: Unsafe statement written to the bi [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23],"tags":[32,16],"class_list":["post-2895","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","tag-binlog","tag-mysql"],"views":3349,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2895","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2895"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2895\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}