{"id":419069,"date":"2021-03-22T06:27:00","date_gmt":"2021-03-21T22:27:00","guid":{"rendered":"http:\/\/4563.org\/?p=419069"},"modified":"2021-03-22T06:27:00","modified_gmt":"2021-03-21T22:27:00","slug":"mysql-%e4%b8%ad%e5%a4%a7%e6%95%b0%e6%8d%ae%e9%87%8f%e7%9a%84%e6%83%85%e5%86%b5%e4%b8%8b%e6%9b%b4%e6%96%b0%e9%97%ae%e9%a2%98","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=419069","title":{"rendered":"MySQL \u4e2d\u5927\u6570\u636e\u91cf\u7684\u60c5\u51b5\u4e0b\u66f4\u65b0\u95ee\u9898"},"content":{"rendered":"
\n
\n
\n

MySQL \u4e2d\u5927\u6570\u636e\u91cf\u7684\u60c5\u51b5\u4e0b\u66f4\u65b0\u95ee\u9898 <\/h1>\n

<\/p>\n

\n
\u8cc7\u6df1\u5927\u4f6c : situliang <\/span> <\/i> 1<\/span> <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
<\/p>\n

\u5173\u4e8e\u5728 MySQL \u4e2d\u51e0\u5341\u4e07\u6570\u636e\u91cf\u7684\u60c5\u51b5\u4e0b\u66f4\u65b0\u95ee\u9898\uff0c\u60f3\u8bf7\u6559\u4e00\u4e0b\u5404\u4f4d\u5927\u4f6c<\/h2>\n

\u4ee5\u4e0b\u9762\u4e24\u4e2a\u8868\u4e3a\u4f8b<\/h2>\n

server \u8868<\/h3>\n\n\n\n\n\n\n\n\n\n\n
server_id<\/th>\nprice<\/th>\nfood_id<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1600<\/td>\n5<\/td>\n<\/tr>\n
2<\/td>\n12<\/td>\n12<\/td>\n<\/tr>\n
3<\/td>\n4<\/td>\n1<\/td>\n<\/tr>\n
4<\/td>\n2<\/td>\n3<\/td>\n<\/tr>\n
5<\/td>\n6<\/td>\n5<\/td>\n<\/tr>\n
..<\/td>\n..<\/td>\n..<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

food \u8868<\/h3>\n\n\n\n\n\n\n\n\n\n\n\n
food_id<\/th>\ntype<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nFruit<\/td>\n<\/tr>\n
2<\/td>\nVegetable<\/td>\n<\/tr>\n
3<\/td>\ndrink<\/td>\n<\/tr>\n
4<\/td>\nFruit<\/td>\n<\/tr>\n
5<\/td>\nVegetable<\/td>\n<\/tr>\n
6<\/td>\ndrink<\/td>\n<\/tr>\n
..<\/td>\n..<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u76ee\u524d\u7684\u9700\u6c42\u5c31\u662f\u9700\u8981\u5c06 server \u8868\u4e2d food \u7c7b\u578b\u4e3a Fruit \u548c Vegetable \u7684 food_id \u8bb0\u5f55\u66f4\u65b0 price \u4e3a 0 \u5e38\u89c4\u7684 SQL \u53ef\u4ee5\u4e0b\u9762\u8fd9\u6837<\/p>\n

UPDATE server SET price = 0 WHERE food_id IN (SELECT food_id FROM food WHERE type = 'Fruit' OR type = 'Vegetable');  <\/code><\/pre>\n
\u4f46\u662f\u5f53 server \u8868\u6709\u51e0\u5341\u4e07\u6761\u8bb0\u5f55\uff0cfood \u8868\u6709\u51e0\u4e07\u6761\u6570\u636e\u7684\u65f6\u5019\uff0c\u6267\u884c\u4e86\u51e0\u4e2a\u5c0f\u65f6\u8fd8\u6ca1\u6267\u884c\u5b8c\uff0c\u4e0d\u77e5\u9053\u8001\u54e5\u4eec\u6709\u5565\u597d\u7684\u5feb\u901f\u65b9\u6848\u5417\uff0c\u53ef\u4ee5\u52a0\u8868\u4ec0\u4e48\u7684<\/p>\n<\/p><\/div>\n
 \u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (10<\/span>)        <\/div>\n
 <\/div>\n<\/p><\/div>\n<\/p><\/div>\n
    \n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : dongdongkun <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u8fd9\u4e2a                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : bringyou <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u63d0\u4f9b\u51e0\u4e2a\u53ef\u80fd\u7684\u4f18\u5316\u601d\u8def\uff1a
    1. food \u8868\u7684 type \u5217\u6709\u6ca1\u6709\u52a0\u7d22\u5f15
    2. server \u8868\u7684 food_id \u6709\u6ca1\u6709\u7d22\u5f15
    3. \u8bd5\u7740\u628a\u5b50\u67e5\u8be2\u6539\u6210 join \u5f62\u5f0f\uff0cfood \u8868\u8fde\u63a5 server \u8868\uff08\u5c0f\u8868\u9a71\u52a8\u5927\u8868\uff09                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : bringyou <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 #2 \u7b2c\u4e09\u6761\u4fee\u6b63\uff1a\u76f4\u63a5\u7528 inner join                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : brader <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 “`
    UPDATE server a LEFT JOIN food b ON a.food_id = b.food_id SET a.price = 0 WHERE b.type = ‘Fruit’ OR b.type = ‘Vegetable’);
    “`
    food_id \u3001type\uff0c\u90fd\u52a0\u4e0b\u7d22\u5f15                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : xupefei <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u8fd9\u6570\u636e\u91cf\u8fd8\u4e0d\u592a\u9700\u8981\u7d22\u5f15\u3002
    \u5148\u5f97\u770b\u770b query plan \u91cc\u5b50\u67e5\u8be2\u6709\u6ca1\u6709\u505a\u54c8\u5e0c\u4f18\u5316\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : situliang <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @brader 
    @bringyou 
    \u611f\u8c22\u4e24\u4f4d\u8001\u54e5\u6307\u70b9\uff0c\u8bd5\u4e86\u4e0b 4L \u8001\u54e5\u7684\u505a\u6cd5\uff0c0.43s \u5c31\u641e\u5b9a\u4e86\u6211\u4e00\u4e0b\u5348\u90fd\u6ca1\u6267\u884c\u5b8c\u7684 SQL\uff0c\u6cea\u76ee                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : xuanbg <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @brader \u53bb\u6389 or\uff0c\u4fee\u6539\u6761\u4ef6\u6267\u884c\u4e24\u6b21\u901f\u5ea6\u66f4\u5feb\u54e6                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : zhangysh1995 <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u540c\u610f 5 \uff0c\u53ef\u4ee5\u5f3a\u5236 optimizer hints \u8ba9 b.type \u8d70 hash index \u3002\u56e0\u4e3a\u8fd9\u91cc\u662f\u7279\u6b8a\u503c\u67e5\u8be2\uff0c\u54c8\u5e0c\u662f\u6700\u5feb\u7684 https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/index-btree-hash.html @situliang                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : brader <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @xuanbg \u56f4\u7ed5\u89e3\u51b3\u5b9e\u9645\u95ee\u9898\u4e3a\u4e2d\u5fc3\u5c31\u53ef\u4ee5\u4e86\uff0c\u8be5\u65b9\u5f0f\u6267\u884c\u901f\u5ea6\u5df2\u7ecf\u975e\u5e38\u5feb\uff0c\u5b8c\u5168\u80fd\u6ee1\u8db3\u8981\u6c42\u4e86\u3002\u6ca1\u5fc5\u8981\u51ed\u7a7a\u589e\u52a0\u96be\u5ea6\u4ee5\u53ca\u6b65\u9aa4\uff0c\u4f55\u51b5\u6267\u884c\u4e24\u6b21\uff0c\u65f6\u95f4\u76f8\u52a0\u672a\u5fc5\u4f1a\u6bd4\u8f83\u77ed\uff0c\u5c31\u65e0\u9700\u53bb\u8fc7\u5ea6\u8003\u8651\u4e86\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : ch2 <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u52a0\u4e2a\u7d22\u5f15\u7684\u4e8b                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n","protected":false},"excerpt":{"rendered":"
    MySQL \u4e2d\u5927\u6570\u636e\u91cf\u7684\u60c5\u51b5\u4e0b\u66f4\u65b0…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/419069"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=419069"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/419069\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=419069"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=419069"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=419069"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}