{"id":2935,"date":"2023-07-14T15:29:16","date_gmt":"2023-07-14T07:29:16","guid":{"rendered":"https:\/\/xinchewhd.com.cn\/?p=2935"},"modified":"2023-07-14T15:29:17","modified_gmt":"2023-07-14T07:29:17","slug":"8%e7%a7%8d%e7%bb%8f%e5%b8%b8%e8%a2%ab%e5%bf%bd%e8%a7%86%e7%9a%84sql%e9%94%99%e8%af%af%e7%94%a8%e6%b3%95%ef%bc%8c%e4%bd%a0%e4%b8%ad%e6%8b%9b%e4%ba%86%e5%90%97%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/xinchewhd.com.cn\/index.php\/mysql\/8%e7%a7%8d%e7%bb%8f%e5%b8%b8%e8%a2%ab%e5%bf%bd%e8%a7%86%e7%9a%84sql%e9%94%99%e8%af%af%e7%94%a8%e6%b3%95%ef%bc%8c%e4%bd%a0%e4%b8%ad%e6%8b%9b%e4%ba%86%e5%90%97%ef%bc%9f\/","title":{"rendered":"8\u79cd\u7ecf\u5e38\u88ab\u5ffd\u89c6\u7684SQL\u9519\u8bef\u7528\u6cd5\uff0c\u4f60\u4e2d\u62db\u4e86\u5417\uff1f"},"content":{"rendered":"
\n

\u4eca\u5929\u7ed9\u5927\u5bb6\u5206\u4eab\u51e0\u4e2aSQL\u5e38\u89c1\u7684\u201c\u574f\u6bdb\u75c5\u201d\u53ca\u4f18\u5316\u6280\u5de7\u3002<\/p>\n

SQL\u8bed\u53e5\u7684\u6267\u884c\u987a\u5e8f\uff1a<\/p>\n

<\/h3>\n

1\u3001LIMIT \u8bed\u53e5<\/span><\/h2>\n

\u5206\u9875\u67e5\u8be2\u662f\u6700\u5e38\u7528\u7684\u573a\u666f\u4e4b\u4e00\uff0c\u4f46\u4e5f\u901a\u5e38\u4e5f\u662f\u6700\u5bb9\u6613\u51fa\u95ee\u9898\u7684\u5730\u65b9\u3002\u6bd4\u5982\u5bf9\u4e8e\u4e0b\u9762\u7b80\u5355\u7684\u8bed\u53e5\uff0c\u4e00\u822c DBA \u60f3\u5230\u7684\u529e\u6cd5\u662f\u5728 type\u3001 name\u3001 create_time \u5b57\u6bb5\u4e0a\u52a0\u7ec4\u5408\u7d22\u5f15\u3002\u8fd9\u6837\u6761\u4ef6\u6392\u5e8f\u90fd\u80fd\u6709\u6548\u7684\u5229\u7528\u5230\u7d22\u5f15\uff0c\u6027\u80fd\u8fc5\u901f\u63d0\u5347\u3002<\/p>\n

SELECT\u00a0*\nFROM\u00a0\u00a0\u00a0operation\nWHERE\u00a0\u00a0type<\/span>\u00a0=\u00a0'SQLStats'<\/span>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0name\u00a0=\u00a0'SlowLog'<\/span>\nORDER\u00a0\u00a0BY\u00a0create_time\nLIMIT\u00a0\u00a01000,\u00a010;\n<\/code><\/pre>\n
\u597d\u5427\uff0c\u53ef\u80fd90%\u4ee5\u4e0a\u7684 DBA \u89e3\u51b3\u8be5\u95ee\u9898\u5c31\u5230\u6b64\u4e3a\u6b62\u3002\u4f46\u5f53 LIMIT \u5b50\u53e5\u53d8\u6210 \u201cLIMIT 1000000,10<\/code>\u201d \u65f6\uff0c\u7a0b\u5e8f\u5458\u4ecd\u7136\u4f1a\u62b1\u6028\uff1a\u6211\u53ea\u53d610\u6761\u8bb0\u5f55\u4e3a\u4ec0\u4e48\u8fd8\u662f\u6162\uff1f<\/p>\n
\u8981\u77e5\u9053\u6570\u636e\u5e93\u4e5f\u5e76\u4e0d\u77e5\u9053\u7b2c1000000\u6761\u8bb0\u5f55\u4ece\u4ec0\u4e48\u5730\u65b9\u5f00\u59cb\uff0c\u5373\u4f7f\u6709\u7d22\u5f15\u4e5f\u9700\u8981\u4ece\u5934\u8ba1\u7b97\u4e00\u6b21\u3002\u51fa\u73b0\u8fd9\u79cd\u6027\u80fd\u95ee\u9898\uff0c\u591a\u6570\u60c5\u5f62\u4e0b\u662f\u7a0b\u5e8f\u5458\u5077\u61d2\u4e86\u3002<\/p>\n
\u5728\u524d\u7aef\u6570\u636e\u6d4f\u89c8\u7ffb\u9875\uff0c\u6216\u8005\u5927\u6570\u636e\u5206\u6279\u5bfc\u51fa\u7b49\u573a\u666f\u4e0b\uff0c\u662f\u53ef\u4ee5\u5c06\u4e0a\u4e00\u9875\u7684\u6700\u5927\u503c\u5f53\u6210\u53c2\u6570\u4f5c\u4e3a\u67e5\u8be2\u6761\u4ef6\u7684\u3002SQL \u91cd\u65b0\u8bbe\u8ba1\u5982\u4e0b\uff1a<\/p>\n
SELECT\u00a0\u00a0\u00a0*\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0operation\nWHERE\u00a0\u00a0\u00a0\u00a0type<\/span>\u00a0=\u00a0'SQLStats'<\/span>\nAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0name\u00a0=\u00a0'SlowLog'<\/span>\nAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0create_time\u00a0>\u00a0'2017-03-16\u00a014:00:00'<\/span>\nORDER\u00a0BY\u00a0create_time\u00a0limit<\/span>\u00a010;\n<\/code><\/pre>\n
\u5728\u65b0\u8bbe\u8ba1\u4e0b\u67e5\u8be2\u65f6\u95f4\u57fa\u672c\u56fa\u5b9a\uff0c\u4e0d\u4f1a\u968f\u7740\u6570\u636e\u91cf\u7684\u589e\u957f\u800c\u53d1\u751f\u53d8\u5316\u3002<\/p>\n
<\/h3>\n
2\u3001\u9690\u5f0f\u8f6c\u6362<\/span><\/h2>\n
SQL\u8bed\u53e5\u4e2d\u67e5\u8be2\u53d8\u91cf\u548c\u5b57\u6bb5\u5b9a\u4e49\u7c7b\u578b\u4e0d\u5339\u914d\u662f\u53e6\u4e00\u4e2a\u5e38\u89c1\u7684\u9519\u8bef\u3002\u6bd4\u5982\u4e0b\u9762\u7684\u8bed\u53e5\uff1a<\/p>\n
mysql>\u00a0explain\u00a0extended\u00a0SELECT\u00a0*\n\u00a0\u00a0\u00a0\u00a0\u00a0>\u00a0FROM\u00a0\u00a0\u00a0my_balance\u00a0b\n\u00a0\u00a0\u00a0\u00a0\u00a0>\u00a0WHERE\u00a0\u00a0b.bpn\u00a0=\u00a014000000123\n\u00a0\u00a0\u00a0\u00a0\u00a0>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0b.isverified\u00a0IS\u00a0NULL\u00a0;\nmysql>\u00a0show\u00a0warnings;\n|\u00a0Warning\u00a0|\u00a01739\u00a0|\u00a0Cannot\u00a0use\u00a0ref\u00a0access\u00a0on\u00a0index\u00a0'bpn'<\/span>\u00a0due\u00a0to\u00a0type<\/span>\u00a0or\u00a0collation\u00a0conversion\u00a0on\u00a0field\u00a0'bpn'<\/span>\n<\/code><\/pre>\n
\u5176\u4e2d\u5b57\u6bb5 bpn \u7684\u5b9a\u4e49\u4e3a varchar(20)<\/code>\uff0cMySQL \u7684\u7b56\u7565\u662f\u5c06\u5b57\u7b26\u4e32\u8f6c\u6362\u4e3a\u6570\u5b57\u4e4b\u540e\u518d\u6bd4\u8f83\u3002\u51fd\u6570\u4f5c\u7528\u4e8e\u8868\u5b57\u6bb5\uff0c\u7d22\u5f15\u5931\u6548\u3002<\/p>\n
\u4e0a\u8ff0\u60c5\u51b5\u53ef\u80fd\u662f\u5e94\u7528\u7a0b\u5e8f\u6846\u67b6\u81ea\u52a8\u586b\u5165\u7684\u53c2\u6570\uff0c\u800c\u4e0d\u662f\u7a0b\u5e8f\u5458\u7684\u539f\u610f\u3002\u73b0\u5728\u5e94\u7528\u6846\u67b6\u5f88\u591a\u5f88\u7e41\u6742\uff0c\u4f7f\u7528\u65b9\u4fbf\u7684\u540c\u65f6\u4e5f\u5c0f\u5fc3\u5b83\u53ef\u80fd\u7ed9\u81ea\u5df1\u6316\u5751\u3002<\/p>\n
<\/h3>\n
3\u3001\u5173\u8054\u66f4\u65b0\u3001\u5220\u9664<\/span><\/h2>\n
\u867d\u7136 MySQL5.6 \u5f15\u5165\u4e86\u7269\u5316\u7279\u6027\uff0c\u4f46\u9700\u8981\u7279\u522b\u6ce8\u610f\u5b83\u76ee\u524d\u4ec5\u4ec5\u9488\u5bf9\u67e5\u8be2\u8bed\u53e5\u7684\u4f18\u5316\u3002\u5bf9\u4e8e\u66f4\u65b0\u6216\u5220\u9664\u9700\u8981\u624b\u5de5\u91cd\u5199\u6210 JOIN\u3002<\/p>\n
\u6bd4\u5982\u4e0b\u9762 UPDATE \u8bed\u53e5\uff0cMySQL \u5b9e\u9645\u6267\u884c\u7684\u662f\u5faa\u73af\/\u5d4c\u5957\u5b50\u67e5\u8be2\uff08DEPENDENT SUBQUERY<\/code>)\uff0c\u5176\u6267\u884c\u65f6\u95f4\u53ef\u60f3\u800c\u77e5\u3002<\/p>\n
UPDATE\u00a0operation\u00a0o\nSET\u00a0\u00a0\u00a0\u00a0status\u00a0=\u00a0'applying'<\/span>\nWHERE\u00a0\u00a0o.id\u00a0IN\u00a0(SELECT\u00a0id\n\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0(SELECT\u00a0o.id,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0o.status\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0operation\u00a0o\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE\u00a0\u00a0o.group\u00a0=\u00a0123\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0o.status\u00a0NOT\u00a0IN\u00a0(\u00a0'done'<\/span>\u00a0)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER\u00a0\u00a0BY\u00a0o.parent,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0o.id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LIMIT\u00a0\u00a01)\u00a0t);\n<\/code><\/pre>\n
\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+\n|\u00a0id\u00a0|\u00a0select_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0table\u00a0|\u00a0type<\/span>\u00a0\u00a0|\u00a0possible_keys\u00a0|\u00a0key\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0key_len\u00a0|\u00a0ref\u00a0\u00a0\u00a0|\u00a0rows\u00a0|\u00a0Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+\n|\u00a01\u00a0\u00a0|\u00a0PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0o\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0index\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0PRIMARY\u00a0|\u00a08\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a024\u00a0\u00a0\u00a0|\u00a0Using\u00a0where<\/span>;\u00a0Using\u00a0temporary\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n|\u00a02\u00a0\u00a0|\u00a0DEPENDENT\u00a0SUBQUERY\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Impossible\u00a0WHERE\u00a0noticed\u00a0after\u00a0reading\u00a0const\u00a0tables\u00a0|\n|\u00a03\u00a0\u00a0|\u00a0DERIVED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0o\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0ref\u00a0\u00a0\u00a0|\u00a0idx_2,idx_5\u00a0\u00a0\u00a0|\u00a0idx_5\u00a0\u00a0\u00a0|\u00a08\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0const\u00a0|\u00a01\u00a0\u00a0\u00a0\u00a0|\u00a0Using\u00a0where<\/span>;\u00a0Using\u00a0filesort\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+\n<\/code><\/pre>\n
\u91cd\u5199\u4e3a JOIN \u4e4b\u540e\uff0c\u5b50\u67e5\u8be2\u7684\u9009\u62e9\u6a21\u5f0f\u4ece DEPENDENT SUBQUERY<\/code> \u53d8\u6210 DERIVED<\/code>\uff0c\u6267\u884c\u901f\u5ea6\u5927\u5927\u52a0\u5feb\uff0c\u4ece7\u79d2\u964d\u4f4e\u52302\u6beb\u79d2\u3002<\/p>\n
UPDATE\u00a0operation\u00a0o\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN\u00a0\u00a0(SELECT\u00a0o.id,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0o.status\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0operation\u00a0o\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE\u00a0\u00a0o.group\u00a0=\u00a0123\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0o.status\u00a0NOT\u00a0IN\u00a0(\u00a0'done'<\/span>\u00a0)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER\u00a0\u00a0BY\u00a0o.parent,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0o.id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LIMIT\u00a0\u00a01)\u00a0t\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0o.id\u00a0=\u00a0t.id\nSET\u00a0\u00a0\u00a0\u00a0status\u00a0=\u00a0'applying'<\/span>\n<\/code><\/pre>\n
\u6267\u884c\u8ba1\u5212\u7b80\u5316\u4e3a\uff1a<\/p>\n
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+\n|\u00a0id\u00a0|\u00a0select_type\u00a0|\u00a0table\u00a0|\u00a0type<\/span>\u00a0|\u00a0possible_keys\u00a0|\u00a0key\u00a0\u00a0\u00a0|\u00a0key_len\u00a0|\u00a0ref\u00a0\u00a0\u00a0|\u00a0rows\u00a0|\u00a0Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+\n|\u00a01\u00a0\u00a0|\u00a0PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Impossible\u00a0WHERE\u00a0noticed\u00a0after\u00a0reading\u00a0const\u00a0tables\u00a0|\n|\u00a02\u00a0\u00a0|\u00a0DERIVED\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0o\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0ref\u00a0\u00a0|\u00a0idx_2,idx_5\u00a0\u00a0\u00a0|\u00a0idx_5\u00a0|\u00a08\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0const\u00a0|\u00a01\u00a0\u00a0\u00a0\u00a0|\u00a0Using\u00a0where<\/span>;\u00a0Using\u00a0filesort\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+\n<\/code><\/pre>\n
<\/h3>\n
4\u3001\u6df7\u5408\u6392\u5e8f<\/span><\/h2>\n
MySQL \u4e0d\u80fd\u5229\u7528\u7d22\u5f15\u8fdb\u884c\u6df7\u5408\u6392\u5e8f\u3002\u4f46\u5728\u67d0\u4e9b\u573a\u666f\uff0c\u8fd8\u662f\u6709\u673a\u4f1a\u4f7f\u7528\u7279\u6b8a\u65b9\u6cd5\u63d0\u5347\u6027\u80fd\u7684\u3002<\/p>\n
SELECT\u00a0*\nFROM\u00a0\u00a0\u00a0my_order\u00a0o\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER\u00a0JOIN\u00a0my_appraise\u00a0a\u00a0ON\u00a0a.orderid\u00a0=\u00a0o.id\nORDER\u00a0\u00a0BY\u00a0a.is_reply\u00a0ASC,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0a.appraise_time\u00a0DESC\nLIMIT\u00a0\u00a00,\u00a020\n<\/code><\/pre>\n
\u6267\u884c\u8ba1\u5212\u663e\u793a\u4e3a\u5168\u8868\u626b\u63cf\uff1a<\/p>\n
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+\n|\u00a0id\u00a0|\u00a0select_type\u00a0|\u00a0table\u00a0|\u00a0type<\/span>\u00a0\u00a0\u00a0|\u00a0possible_keys\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0key\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0key_len\u00a0|\u00a0ref\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0rows\u00a0\u00a0\u00a0\u00a0|\u00a0Extra\n+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+\n|\u00a0\u00a01\u00a0|\u00a0SIMPLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0a\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0ALL\u00a0\u00a0\u00a0\u00a0|\u00a0idx_orderid\u00a0|\u00a0NULL\u00a0\u00a0\u00a0\u00a0|\u00a0NULL\u00a0\u00a0\u00a0\u00a0|\u00a0NULL\u00a0\u00a0\u00a0\u00a0|\u00a01967647\u00a0|\u00a0Using\u00a0filesort\u00a0|\n|\u00a0\u00a01\u00a0|\u00a0SIMPLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0o\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0eq_ref\u00a0|\u00a0PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0PRIMARY\u00a0|\u00a0122\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0a.orderid\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+\n<\/code><\/pre>\n
\u7531\u4e8e is_reply<\/code> \u53ea\u67090\u548c1\u4e24\u79cd\u72b6\u6001\uff0c\u6211\u4eec\u6309\u7167\u4e0b\u9762\u7684\u65b9\u6cd5\u91cd\u5199\u540e\uff0c\u6267\u884c\u65f6\u95f4\u4ece1.58\u79d2\u964d\u4f4e\u52302\u6beb\u79d2\u3002<\/p>\n
SELECT\u00a0*\nFROM\u00a0\u00a0\u00a0((SELECT\u00a0*\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0my_order\u00a0o\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER\u00a0JOIN\u00a0my_appraise\u00a0a\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0a.orderid\u00a0=\u00a0o.id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0is_reply\u00a0=\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER\u00a0\u00a0BY\u00a0appraise_time\u00a0DESC\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LIMIT\u00a0\u00a00,\u00a020)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION\u00a0ALL\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(SELECT\u00a0*\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0my_order\u00a0o\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER\u00a0JOIN\u00a0my_appraise\u00a0a\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0a.orderid\u00a0=\u00a0o.id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0is_reply\u00a0=\u00a01\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER\u00a0\u00a0BY\u00a0appraise_time\u00a0DESC\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LIMIT\u00a0\u00a00,\u00a020))\u00a0t\nORDER\u00a0\u00a0BY\u00a0\u00a0is_reply\u00a0ASC,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0appraisetime\u00a0DESC\nLIMIT\u00a0\u00a020;\n<\/code><\/pre>\n
<\/h3>\n
5\u3001EXISTS\u8bed\u53e5<\/span><\/h2>\n
MySQL \u5bf9\u5f85 EXISTS \u5b50\u53e5\u65f6\uff0c\u4ecd\u7136\u91c7\u7528\u5d4c\u5957\u5b50\u67e5\u8be2\u7684\u6267\u884c\u65b9\u5f0f\u3002\u5982\u4e0b\u9762\u7684 SQL \u8bed\u53e5\uff1a<\/p>\n
SELECT\u00a0*\nFROM\u00a0\u00a0\u00a0my_neighbor\u00a0n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LEFT\u00a0JOIN\u00a0my_neighbor_apply\u00a0sra\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0n.id\u00a0=\u00a0sra.neighbor_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0sra.user_id\u00a0=\u00a0'xxx'<\/span>\nWHERE\u00a0\u00a0n.topic_status\u00a0<\u00a04\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0EXISTS(SELECT\u00a01\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0message_info\u00a0m\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE\u00a0\u00a0n.id\u00a0=\u00a0m.neighbor_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0m.inuser\u00a0=\u00a0'xxx'<\/span>)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0n.topic_type\u00a0<>\u00a05\n<\/code><\/pre>\n
\u6267\u884c\u8ba1\u5212\u4e3a\uff1a<\/p>\n
+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+\u00a0-----+\n|\u00a0id\u00a0|\u00a0select_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0table\u00a0|\u00a0type<\/span>\u00a0|\u00a0possible_keys\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0key\u00a0\u00a0\u00a0|\u00a0key_len\u00a0|\u00a0ref\u00a0\u00a0\u00a0|\u00a0rows\u00a0\u00a0\u00a0\u00a0|\u00a0Extra\u00a0\u00a0\u00a0|\n+----+--------------------+-------+------+\u00a0-----+------------------------------------------+---------+-------+---------+\u00a0-----+\n|\u00a0\u00a01\u00a0|\u00a0PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0n\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0ALL\u00a0\u00a0|\u00a0\u00a0|\u00a0NULL\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0NULL\u00a0\u00a0\u00a0\u00a0|\u00a0NULL\u00a0\u00a0|\u00a01086041\u00a0|\u00a0Using\u00a0where<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n|\u00a0\u00a01\u00a0|\u00a0PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0sra\u00a0\u00a0\u00a0|\u00a0ref\u00a0\u00a0|\u00a0\u00a0|\u00a0idx_user_id\u00a0|\u00a0123\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0const\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0Using\u00a0where<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n|\u00a0\u00a02\u00a0|\u00a0DEPENDENT\u00a0SUBQUERY\u00a0|\u00a0m\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0ref\u00a0\u00a0|\u00a0\u00a0|\u00a0idx_message_info\u00a0\u00a0\u00a0|\u00a0122\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0const\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0Using\u00a0index\u00a0condition;\u00a0Using\u00a0where<\/span>\u00a0|\n+----+--------------------+-------+------+\u00a0-----+------------------------------------------+---------+-------+---------+\u00a0-----+\n<\/code><\/pre>\n
\u53bb\u6389 exists \u66f4\u6539\u4e3a join\uff0c\u80fd\u591f\u907f\u514d\u5d4c\u5957\u5b50\u67e5\u8be2\uff0c\u5c06\u6267\u884c\u65f6\u95f4\u4ece1.93\u79d2\u964d\u4f4e\u4e3a1\u6beb\u79d2\u3002<\/p>\n
SELECT\u00a0*\nFROM\u00a0\u00a0\u00a0my_neighbor\u00a0n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER\u00a0JOIN\u00a0message_info\u00a0m\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0n.id\u00a0=\u00a0m.neighbor_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0m.inuser\u00a0=\u00a0'xxx'<\/span>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LEFT\u00a0JOIN\u00a0my_neighbor_apply\u00a0sra\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON\u00a0n.id\u00a0=\u00a0sra.neighbor_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0sra.user_id\u00a0=\u00a0'xxx'<\/span>\nWHERE\u00a0\u00a0n.topic_status\u00a0<\u00a04\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0n.topic_type\u00a0<>\u00a05\n<\/code><\/pre>\n
\u65b0\u7684\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n
+----+-------------+-------+--------+\u00a0-----+------------------------------------------+---------+\u00a0-----+------+\u00a0-----+\n|\u00a0id\u00a0|\u00a0select_type\u00a0|\u00a0table\u00a0|\u00a0type<\/span>\u00a0\u00a0\u00a0|\u00a0possible_keys\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0key\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0key_len\u00a0|\u00a0ref\u00a0\u00a0\u00a0|\u00a0rows\u00a0|\u00a0Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+-------------+-------+--------+\u00a0-----+------------------------------------------+---------+\u00a0-----+------+\u00a0-----+\n|\u00a0\u00a01\u00a0|\u00a0SIMPLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0m\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0ref\u00a0\u00a0\u00a0\u00a0|\u00a0|\u00a0idx_message_info\u00a0\u00a0\u00a0|\u00a0122\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0const\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0Using\u00a0index\u00a0condition\u00a0|\n|\u00a0\u00a01\u00a0|\u00a0SIMPLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0n\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0eq_ref\u00a0|\u00a0|\u00a0PRIMARY\u00a0\u00a0\u00a0|\u00a0122\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0ighbor_id\u00a0|\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0Using\u00a0where<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n|\u00a0\u00a01\u00a0|\u00a0SIMPLE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0sra\u00a0\u00a0\u00a0|\u00a0ref\u00a0\u00a0\u00a0\u00a0|\u00a0|\u00a0idx_user_id\u00a0|\u00a0123\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0const\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0Using\u00a0where<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+-------------+-------+--------+\u00a0-----+------------------------------------------+---------+\u00a0-----+------+\u00a0-----+\n<\/code><\/pre>\n
<\/h3>\n
6\u3001\u6761\u4ef6\u4e0b\u63a8<\/span><\/h2>\n
\u5916\u90e8\u67e5\u8be2\u6761\u4ef6\u4e0d\u80fd\u591f\u4e0b\u63a8\u5230\u590d\u6742\u7684\u89c6\u56fe\u6216\u5b50\u67e5\u8be2\u7684\u60c5\u51b5\u6709\uff1a<\/p>\n
    \n
  • \n
    \u805a\u5408\u5b50\u67e5\u8be2\uff1b<\/section>\n<\/li>\n
  • \n
    \u542b\u6709 LIMIT \u7684\u5b50\u67e5\u8be2\uff1b<\/section>\n<\/li>\n
  • \n
    UNION \u6216 UNION ALL \u5b50\u67e5\u8be2\uff1b<\/section>\n<\/li>\n
  • \n
    \u8f93\u51fa\u5b57\u6bb5\u4e2d\u7684\u5b50\u67e5\u8be2\uff1b<\/section>\n<\/li>\n
  • \n
    \u66f4\u591a\u8d44\u6599\uff1ahttps:\/\/www.yoodb.com\/<\/p>\n<\/section>\n<\/li>\n<\/ul>\n
    \u5982\u4e0b\u9762\u7684\u8bed\u53e5\uff0c\u4ece\u6267\u884c\u8ba1\u5212\u53ef\u4ee5\u770b\u51fa\u5176\u6761\u4ef6\u4f5c\u7528\u4e8e\u805a\u5408\u5b50\u67e5\u8be2\u4e4b\u540e\uff1a<\/p>\n
    SELECT\u00a0*\nFROM\u00a0\u00a0\u00a0(SELECT\u00a0target,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count(*)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0operation\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP\u00a0\u00a0BY\u00a0target)\u00a0t\nWHERE\u00a0\u00a0target\u00a0=\u00a0'rm-xxxx'<\/span>\n+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+\n|\u00a0id\u00a0|\u00a0select_type\u00a0|\u00a0table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0type<\/span>\u00a0\u00a0|\u00a0possible_keys\u00a0|\u00a0key\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0key_len\u00a0|\u00a0ref\u00a0\u00a0\u00a0|\u00a0rows\u00a0|\u00a0Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+\n|\u00a0\u00a01\u00a0|\u00a0PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0<derived2>\u00a0|\u00a0ref\u00a0\u00a0\u00a0|\u00a0<auto_key0>\u00a0\u00a0\u00a0|\u00a0<auto_key0>\u00a0|\u00a0514\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0const\u00a0|\u00a0\u00a0\u00a0\u00a02\u00a0|\u00a0Using\u00a0where<\/span>\u00a0|\n|\u00a0\u00a02\u00a0|\u00a0DERIVED\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0operation\u00a0\u00a0|\u00a0index\u00a0|\u00a0idx_4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0idx_4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0519\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0NULL\u00a0\u00a0|\u00a0\u00a0\u00a020\u00a0|\u00a0Using\u00a0index\u00a0|\n+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+\n<\/code><\/pre>\n
    \u786e\u5b9a\u4ece\u8bed\u4e49\u4e0a\u67e5\u8be2\u6761\u4ef6\u53ef\u4ee5\u76f4\u63a5\u4e0b\u63a8\u540e\uff0c\u91cd\u5199\u5982\u4e0b\uff1a<\/p>\n
    SELECT\u00a0target,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count(*)\nFROM\u00a0\u00a0\u00a0operation\nWHERE\u00a0\u00a0target\u00a0=\u00a0'rm-xxxx'<\/span>\nGROUP\u00a0\u00a0BY\u00a0target\n<\/code><\/pre>\n
    \u6267\u884c\u8ba1\u5212\u53d8\u4e3a\uff1a<\/p>\n
    +----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+\n|\u00a0id\u00a0|\u00a0select_type\u00a0|\u00a0table\u00a0|\u00a0type<\/span>\u00a0|\u00a0possible_keys\u00a0|\u00a0key\u00a0|\u00a0key_len\u00a0|\u00a0ref\u00a0|\u00a0rows\u00a0|\u00a0Extra\u00a0|\n+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+\n|\u00a01\u00a0|\u00a0SIMPLE\u00a0|\u00a0operation\u00a0|\u00a0ref\u00a0|\u00a0idx_4\u00a0|\u00a0idx_4\u00a0|\u00a0514\u00a0|\u00a0const\u00a0|\u00a01\u00a0|\u00a0Using\u00a0where<\/span>;\u00a0Using\u00a0index\u00a0|\n+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+\n<\/code><\/pre>\n
    \u5173\u4e8e MySQL \u5916\u90e8\u6761\u4ef6\u4e0d\u80fd\u4e0b\u63a8\u7684\u8be6\u7ec6\u89e3\u91ca\u8bf4\u660e\u8bf7\u53c2\u8003\uff1a<\/p>\n
    \n