{"id":563,"date":"2014-03-18T11:28:46","date_gmt":"2014-03-18T03:28:46","guid":{"rendered":"http:\/\/www.gpfeng.com\/?p=563"},"modified":"2014-03-18T13:49:06","modified_gmt":"2014-03-18T05:49:06","slug":"select-from-update-%e7%89%b9%e6%80%a7%e4%bb%8b%e7%bb%8d%e5%8f%8a%e6%80%a7%e8%83%bd%e6%b5%8b%e8%af%95","status":"publish","type":"post","link":"http:\/\/www.gpfeng.com\/?p=563","title":{"rendered":"SELECT…FROM UPDATE \u7279\u6027\u4ecb\u7ecd\u53ca\u6027\u80fd\u6d4b\u8bd5"},"content":{"rendered":"

\u4f7f\u7528\u80cc\u666f\uff1a<\/h3>\n

\n \u5c06update+select\u4e24\u6761SQL\u5408\u5e76\u6210\u5355\u6761SQL\uff0c\u5411\u5ba2\u6237\u7aef\u76f4\u63a5\u8fd4\u56deupdate\u4e4b\u540e\u7684\u7ed3\u679c\u5217\uff0c\u901a\u8fc7\u51cf\u5c0f\u7f51\u7edc\u5f00\u9500\u53casql\u89e3\u6790\u4ee3\u4ef7\u63d0\u9ad8\u6027\u80fd\n<\/p>\n

\u7279\u6027\u63cf\u8ff0\uff1a<\/h3>\n

\n postgresql\u7684\u8bed\u6cd5\u662freturning … as …\n<\/p>\n

\n http:\/\/www.postgresql.org\/docs\/9.1\/static\/sql-update.html<\/a>\n<\/p>\n

\n oracle\u7684\u8bed\u6cd5\u662freturning…into…\n<\/p>\n

\n http:\/\/docs.oracle.com\/cd\/E11882_01\/appdev.112\/e25519\/returninginto_clause.htm#LNPLS01354<\/a>\n<\/p>\n

\n MySQL\u4e2d\u7531\u4e8e\u9700\u8981\u517c\u5bb9MySQL JDBC\uff0c\u6211\u4eec\u5c06\u8fd9\u4e2a\u7279\u6027\u5b9e\u73b0\u4e3a\uff1aSELECT…FROM UPDATE…\n<\/p>\n

<\/p>\n

\u8bed\u6cd5\u63cf\u8ff0\uff1a<\/h3>\n<\/p>\n
\r\nSELECT\r\n  select_expr [, select_expr ...]\r\nFROM UPDATE\r\n  [LOW_PRIORITY] [IGNORE]\r\n  [COMMIT_ON_SUCCESS] [ROLLBACK_ON_FAIL] [QUEUE_ON_PK] [TARGET_AFFECT_ROW num]\r\ntbl_name\r\nSET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT} ...]\r\n[WHERE where_condition]  [ORDER BY ...]  [LIMIT row_count]\r\n<\/pre>\n
\n    \u8bf4\u660e\uff1a\n<\/p>\n
\n    1\uff09\u4ec5\u652f\u6301\u5355\u4e2a\u8868UPDATE\u8bed\u53e5\n<\/p>\n
\n    2\uff09SELECT\u8fd4\u56de\u7684\u6570\u636e\u91cf\u4e3amatched rows\uff0c\u800c\u975emodified rows\uff0c\u5373\u903b\u8f91\u66f4\u65b0\u800c\u975e\u7269\u7406\u66f4\u65b0\uff0c\u8fd9\u4e2a\u4e0ePostgreSQl\/Oracle\u662f\u4e00\u81f4\u7684\n<\/p>\n
\u6027\u80fd\u6570\u636e<\/h3>\n
\n    1\uff09\u6d4b\u8bd5\u6570\u636e\n<\/p>\n
\r\nmysql>; show create table ttt\\G\r\n*************************** 1. row ***************************\r\n       Table: ttt\r\nCreate Table: CREATE TABLE `ttt` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `num` int(11) DEFAULT NULL,\r\n  `str` varchar(32) DEFAULT 'hello, world!',\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB AUTO_INCREMENT=17366752 DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nmysql>; select count(*) from ttt;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n| 16777216 |\r\n+----------+\r\n1 row in set (3.72 sec)\r\n<\/pre>\n
\n    2\uff09\u5bf9\u6bd4\u6570\u636e\n<\/p>\n
\n    client_statement\uff1aUPDATE+SELECT\u5206\u4e24\u6b21\u53d1\u9001\u5230\u670d\u52a1\u5668\u7aef\u6267\u884c\n<\/p>\n
\n    client_multi_statement\uff1amulti-statement\u6a21\u5f0f\u5c06UPDATE+SELECT\u4e00\u6b21\u53d1\u9001\u5230\u670d\u52a1\u5668\u7aef\u6267\u884c\n<\/p>\n
\n    select_update\uff1a\u53d1\u9001SELECT…FROM UPDATE\u5230\u670d\u52a1\u5668\u7aef\u6267\u884c\n<\/p>\n
\n    3\uff09\u5b9e\u9a8c\u7ed3\u679c\n<\/p>\n
\u901a\u8fc7unix socket\u6d4b\u8bd5\uff08\u670d\u52a1\u5668\u548c\u538b\u6d4b\u7a0b\u5e8f\u4f4d\u4e8e\u540c\u4e00\u53f0\u7269\u7406\u673a\u5668\uff09\uff1a<\/h4>\n<\/p>\n
\n    query cnt: 100000
client_statement: 16 seconds
client_multi_statement: 15 seconds
select_update: 10 seconds\n<\/p>\n
\n    
query cnt: 500000
client_statement: 81 seconds
client_multi_statement: 74 seconds
select_update: 52 seconds\n<\/p>\n
\n    
query cnt: 1000000
client_statement: 162 seconds
client_multi_statement: 146 seconds
select_update: 103 seconds\n<\/p>\n
\u901a\u8fc7TCP\u6d4b\u8bd5\uff08\u670d\u52a1\u5668\u548c\u538b\u6d4b\u7a0b\u5e8f\u4f4d\u4e8e\u540c\u673a\u623f\u4e24\u53f0\u673a\u5668\uff0cRTT\uff1a0.140 ms\uff09<\/h4>\n<\/p>\n
\n    query cnt: 100000
client_statement: 48 seconds
client_multi_statement: 32 seconds
select_update: 27 seconds\n<\/p>\n
\n    
query cnt: 500000
client_statement: 244 seconds
client_multi_statement: 161 seconds
select_update: 135 seconds\n<\/p>\n
\n    query cnt: 500000\n<\/p>\n
\n    client_statement: 481 seconds
client_multi_statement: 320 seconds\n<\/p>\n
\n    select_update: 267 seconds\n<\/p>\n
\u7ed3\u8bba\uff1a<\/h3>\n
\n    \u672c\u5730unix socket\u8fde\u63a5\u4e0b\uff1a\n<\/p>\n
\n    SELECT…FROM UPDATE \u76f8\u5bf9\u4e8e\u5206\u4e24\u6b21\u53d1\u9001SQL\u6267\u884c\u6027\u80fd\u63d0\u534760%\u5de6\u53f3\uff0c\u76f8\u5bf9\u4e8emulti-statement\u6027\u80fd\u63d0\u534740%\u5de6\u53f3\n<\/p>\n
\n    \u540c\u673a\u623fTCP\u8fde\u63a5\u4e0b\uff1a\n<\/p>\n
\n    SELECT…FROM UPDATE \u76f8\u5bf9\u4e8e\u5206\u4e24\u6b21\u53d1\u9001SQL\u6267\u884c\u6027\u80fd\u63d0\u534780%\u5de6\u53f3\uff0c\u76f8\u5bf9\u4e8emulti-statement\u6027\u80fd\u63d0\u534720%\u5de6\u53f3\n<\/p>\n
\n    \u5373\uff1aSELECT…FROM UPDATE\u7279\u6027\u901a\u8fc7\u51cf\u5c0f\u7f51\u7edc\u4ea4\u4e92\u4ee3\u4ef7\uff0c\u53ef\u4ee5\u6781\u5927\u63d0\u9ad8\u6027\u80fd\n<\/p>\n
\u5b9e\u73b0\u539f\u7406\u53ca\u7ec6\u8282\uff1a<\/h3>\n
\n    \u6211\u7684\u535a\u5ba2\uff1ahttp:\/\/www.gpfeng.com\/?p=134<\/a>\n<\/p>\n
\n    patch\u5730\u5740\uff1ahttp:\/\/rb.corp.taobao.com\/r\/62123\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u4f7f\u7528\u80cc\u666f\uff1a \u5c06update+select\u4e24\u6761SQL\u5408\u5e76\u6210\u5355\u6761SQL\uff0c\u5411\u5ba2\u6237\u7aef\u76f4\u63a5\u8fd4\u56deupdate\u4e4b\u540e\u7684\u7ed3\u679c\u5217\uff0c … \u7ee7\u7eed\u9605\u8bfbSELECT…FROM UPDATE \u7279\u6027\u4ecb\u7ecd\u53ca\u6027\u80fd\u6d4b\u8bd5<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[1],"tags":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3tPZp-95","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/563"}],"collection":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=563"}],"version-history":[{"count":3,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/563\/revisions"}],"predecessor-version":[{"id":568,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/563\/revisions\/568"}],"wp:attachment":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=563"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}