{"id":1320,"date":"2026-04-08T14:45:52","date_gmt":"2026-04-08T06:45:52","guid":{"rendered":"https:\/\/yunyanglib.cn\/?p=1320"},"modified":"2026-04-08T14:45:52","modified_gmt":"2026-04-08T06:45:52","slug":"%e3%80%90mysql%e3%80%91mysql%e5%88%86%e5%8c%ba%e5%ae%9e%e6%88%98%e6%8c%87%e5%8d%97%ef%bc%9a%e4%bb%8e%e5%8e%9f%e7%90%86%e5%88%b0%e8%90%bd%e5%9c%b0%e7%9a%84%e5%ae%8c%e6%95%b4%e6%94%bb%e7%95%a5","status":"publish","type":"post","link":"https:\/\/yunyanglib.cn\/?p=1320","title":{"rendered":"\u3010MySQL\u3011MySQL\u5206\u533a\u5b9e\u6218\u6307\u5357\uff1a\u4ece\u539f\u7406\u5230\u843d\u5730\u7684\u5b8c\u6574\u653b\u7565"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">\u524d\u8a00\uff1a\u4e3a\u4ec0\u4e48\u5927\u8868\u5fc5\u987b\u5b66\u4f1a\u5206\u533a\uff1f<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u4f5c\u4e3a\u4e00\u540d\u957f\u671f\u6df1\u8015\u540e\u7aef\u5f00\u53d1\u7684\u5de5\u7a0b\u5e08\uff0c\u76f8\u4fe1\u5f88\u591a\u540c\u5b66\u90fd\u9047\u5230\u8fc7\u8fd9\u6837\u7684\u75db\u70b9\uff1a\u968f\u7740\u4e1a\u52a1\u589e\u957f\uff0c\u5355\u8868\u6570\u636e\u91cf\u7a81\u7834\u5343\u4e07\u751a\u81f3\u4ebf\u7ea7\u540e\uff0c\u5373\u4f7f\u52a0\u4e86\u7d22\u5f15\uff0c\u67e5\u8be2\u4f9d\u7136\u5361\u987f\uff1b\u5b9a\u671f\u6e05\u7406\u5386\u53f2\u6570\u636e\u65f6\uff0cdelete \u8bed\u53e5\u6267\u884c\u51e0\u5c0f\u65f6\u8fd8\u4f1a\u5bfc\u81f4\u4ece\u5e93\u5ef6\u8fdf\u3002\u5176\u5b9e\u8fd9\u4e9b\u95ee\u9898\uff0c\u7528 MySQL \u5206\u533a\u5c31\u80fd\u5b8c\u7f8e\u89e3\u51b3\u3002\u4eca\u5929\u5c31\u4ece\u539f\u7406\u5230\u5b9e\u64cd\uff0c\u5e26\u5927\u5bb6\u5f7b\u5e95\u638c\u63e1\u5206\u533a\u6280\u672f\uff0c\u8ba9\u5927\u8868\u67e5\u8be2\u6548\u7387\u7ffb\u500d\uff01<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"N0W5Z\">\u4e00\u3001MySQL \u5206\u533a\uff1a\u5927\u8868\u4f18\u5316\u7684 &#8220;\u624b\u672f\u5200&#8221;<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"yELiF\">1.1 \u5206\u533a\u7684\u6838\u5fc3\u4f18\u52bf<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u67e5\u8be2\u63d0\u901f<\/strong>\uff1a\u53ea\u626b\u63cf\u76ee\u6807\u5206\u533a\uff0c\u907f\u514d\u5168\u8868\u626b\u63cf\u3002\u6bd4\u5982\u6309\u6708\u5206\u533a\u7684\u8ba2\u5355\u8868\uff0c\u67e5\u8be2\u8fd1 3 \u4e2a\u6708\u6570\u636e\u65f6\uff0c\u4ec5\u9700\u8bbf\u95ee 3 \u4e2a\u5206\u533a\uff0c\u6548\u7387\u63d0\u5347\u663e\u8457\u3002 <\/li>\n\n\n\n<li><strong>\u7ef4\u62a4\u9ad8\u6548<\/strong>\uff1a\u5220\u9664\u5386\u53f2\u6570\u636e\u65e0\u9700 delete\uff0c\u76f4\u63a5 drop \u5206\u533a\uff0c\u79d2\u7ea7\u5b8c\u6210\u4e14\u4e0d\u5f71\u54cd\u4e1a\u52a1\u3002<\/li>\n\n\n\n<li><strong>\u5b58\u50a8\u6269\u5c55<\/strong>\uff1a\u4e0d\u540c\u5206\u533a\u53ef\u90e8\u7f72\u5728\u4e0d\u540c\u78c1\u76d8\uff0c\u51b7\u70ed\u6570\u636e\u5206\u79bb\uff0c\u964d\u4f4e\u5b58\u50a8\u6210\u672c\u7684\u540c\u65f6\u4fdd\u8bc1\u70ed\u6570\u636e\u6027\u80fd\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"p2fhP\">1.2 \u54ea\u4e9b\u573a\u666f\u9002\u5408\u7528\u5206\u533a\uff1f<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u6570\u636e\u91cf\u8d85 1000 \u4e07\u7684\u5927\u8868\uff08\u5982\u8ba2\u5355\u8868\u3001\u65e5\u5fd7\u8868\uff09\uff1b <\/li>\n\n\n\n<li>\u9700\u6309\u65f6\u95f4\u8303\u56f4\u67e5\u8be2\u6570\u636e\uff08\u5982\u62a5\u8868\u7edf\u8ba1\u3001\u5386\u53f2\u8bb0\u5f55\u67e5\u8be2\uff09\uff1b <\/li>\n\n\n\n<li>\u6709\u5b9a\u671f\u5f52\u6863 \/ \u5220\u9664\u5386\u53f2\u6570\u636e\u9700\u6c42\uff08\u5982\u65e5\u5fd7\u4fdd\u7559 6 \u4e2a\u6708\uff09\uff1b<\/li>\n\n\n\n<li>\u51b7\u70ed\u6570\u636e\u8bbf\u95ee\u9891\u7387\u5dee\u5f02\u5927\uff08\u5982\u8fd1 3 \u4e2a\u6708\u6570\u636e\u9ad8\u9891\u8bbf\u95ee\uff0c\u4e00\u5e74\u524d\u6570\u636e\u6781\u5c11\u67e5\u8be2\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"rlak4\">1.3 \u907f\u5751\u6307\u5357\uff1a\u5206\u533a\u524d\u5fc5\u987b\u77e5\u9053\u7684\u9650\u5236<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>MySQL \u5206\u533a\u6709\u9650\u5236<\/strong>\uff1a\u4e0d\u652f\u6301\u5916\u952e\u548c\u5168\u6587\u7d22\u5f15\uff0c\u4e34\u65f6\u8868\u4e0d\u80fd\u5206\u533a\uff0cNULL \u503c\u4f1a\u5f52\u5165\u8303\u56f4\u5206\u533a\u7684\u6700\u5c0f\u5206\u533a\uff1b <\/li>\n\n\n\n<li><strong>\u5206\u533a\u952e\u9009\u4e0d\u5bf9\uff0c\u4e00\u5207\u90fd\u767d\u8d39<\/strong>\uff1a\u4f18\u5148\u9009\u67e5\u8be2\u9891\u7e41\u7684\u8fc7\u6ee4\u5b57\u6bb5\uff08\u5982 create_time\uff09\uff0c\u8fd8\u8981\u4fdd\u8bc1\u6570\u636e\u5747\u5300\u5206\u5e03\uff0c\u907f\u514d\u70ed\u70b9\u5206\u533a\uff1b <\/li>\n\n\n\n<li><strong>\u5206\u533a\u6570\u91cf\u4e0d\u662f\u8d8a\u591a\u8d8a\u597d<\/strong>\uff1a\u5efa\u8bae\u5355\u5206\u533a\u6570\u636e\u91cf\u63a7\u5236\u5728 100 \u4e07 &#8211; 1000 \u4e07\uff0c\u8fc7\u591a\u5206\u533a\u4f1a\u589e\u52a0\u5143\u6570\u636e\u5f00\u9500\uff0c\u53cd\u800c\u62d6\u6162\u6027\u80fd\u3002<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"hKQU4\">\u4e8c\u30015 \u79cd\u6838\u5fc3\u5206\u533a\u7c7b\u578b + \u5b9e\u64cd\u6848\u4f8b<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u414f06ff\">MySQL\u652f\u6301\u591a\u79cd\u5206\u533a\u7c7b\u578b\uff0c\u4e0d\u540c\u7c7b\u578b\u9002\u7528\u4e8e\u4e0d\u540c\u573a\u666f\u3002\u4ee5\u4e0b\u4e3a5\u79cd\u6838\u5fc3\u5206\u533a\u7c7b\u578b\u7684\u5b9a\u4e49\u3001\u9002\u7528\u573a\u666f\u53ca\u5b8c\u6574\u521b\u5efa\u6848\u4f8b\u3002<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>\u5206\u533a\u7c7b\u578b<\/strong><\/td><td><strong>\u89e3\u91ca<\/strong><\/td><td><strong>\u9002\u7528\u573a\u666f\u4e3e\u4f8b<\/strong><\/td><\/tr><tr><td>\u8303\u56f4\u5206\u533a<\/td><td>\u6839\u636e\u67d0\u4e2a\u5b57\u6bb5\u7684\u8303\u56f4\u6761\u4ef6\u5c06\u6570\u636e\u5206\u533a<\/td><td>\u8ba2\u5355\u8868\u6309\u8ba2\u5355\u65e5\u671f\u8fdb\u884c\u8303\u56f4\u5206\u533a<br>\u65e5\u5fd7\u8868\u6309\u7167\u65e5\u5fd7\u8bb0\u5f55\u7684\u65f6\u95f4\u8fdb\u884c\u8303\u56f4\u5206\u533a<\/td><\/tr><tr><td>\u5217\u8868\u5206\u533a<\/td><td>\u57fa\u4e8e\u679a\u4e3e\u51fa\u7684\u503c\u8fdb\u884c\u5206\u533a<\/td><td>\u5546\u54c1\u8868\u6309\u7167\u5546\u54c1\u7c7b\u522b\u8fdb\u884c\u5217\u8868\u5206\u533a<br>\u7528\u6237\u4fe1\u606f\u6839\u636e\u5730\u533a\u8fdb\u884c\u5217\u8868\u5206\u533a<\/td><\/tr><tr><td>\u54c8\u5e0c\u5206\u533a<\/td><td>\u6839\u636e\u67d0\u4e2a\u5217\u7684\u54c8\u5e0c\u503c\u8fdb\u884c\u5206\u533a<\/td><td>\u7528\u6237\u4fe1\u606f\u6309\u7167\u7528\u6237id\u8fdb\u884c\u54c8\u5e0c\u5206\u533a<br>\u8ba2\u5355\u8868\u6309\u7167\u8ba2\u5355ID\u8fdb\u884c\u54c8\u5e0c\u5206\u533a<\/td><\/tr><tr><td>\u6309\u952e\u5206\u533a<\/td><td>\u6839\u636e\u67d0\u4e2a\u5217\u7684\u952e\u503c\u5c06\u8868\u6570\u636e\u5206\u5272\u6210\u4e0d\u540c\u7684\u5206\u533a<\/td><td>\u7528\u6237\u4fe1\u606f\u8868\u6309\u7167\u7528\u6237ID\u8fdb\u884c\u6309\u952e\u5206\u533a<br>\u65e5\u5fd7\u8868\u6309\u7167\u65e5\u5fd7ID\u8fdb\u884c\u6309\u952e\u5206\u533a<\/td><\/tr><tr><td>\u5b50\u5206\u533a<\/td><td>\u5bf9\u5206\u533a\u8868\u4e2d\u7684\u6bcf\u4e2a\u5206\u533a\u518d\u8fdb\u4e00\u6b65\u5212\u5206<\/td><td>\u65e5\u5fd7\u8868\u5bf9\u5e74\u4efd\u8fdb\u884c\u5206\u533a\u4e4b\u540e\uff0c\u518d\u5bf9\u65e5\u671f\u505a\u54c8\u5e0c\u5206\u533a<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"RqO8F\">2.1 \u8303\u56f4\u5206\u533a\uff1a\u6700\u5e38\u7528\u7684\u65f6\u95f4\u5206\u533a\u65b9\u6848<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5b9a\u4e49<\/strong>\uff1a\u6309\u201c\u8fde\u7eed\u7684\u6570\u503c\u8303\u56f4\u201d\u62c6\u5206\u6570\u636e\uff0c\u5982\u6309\u5206\u6570\u6bb5\uff080-60\u300160-80\uff09\u3001\u65e5\u671f\u8303\u56f4\uff082023\u5e74\u30012024\u5e74\uff09\u3002<br><\/li>\n\n\n\n<li><strong>\u9002\u7528\u573a\u666f<\/strong>\uff1a\u6570\u636e\u6709\u660e\u786e\u8303\u56f4\u5212\u5206\uff0c\u4e14\u67e5\u8be2\u591a\u57fa\u4e8e\u8303\u56f4\u8fc7\u6ee4\uff08\u5982\u6309\u65f6\u95f4\u67e5\u8be2\u5386\u53f2\u6570\u636e\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"AskVw\">\u6848\u4f8b1\uff1a\u6309\u6574\u6570\u8303\u56f4\u5206\u533a\uff08\u5b66\u751f\u6210\u7ee9\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u0ab0a0d5\">\u6309\u5206\u6570\u5c06\u5b66\u751f\u6210\u7ee9\u5206\u4e3a\u201c\u4e0d\u53ca\u683c\uff08&lt;60\uff09\u201d\u201c\u826f\u597d\uff0860-80\uff09\u201d\u201c\u4f18\u79c0\uff0880-100\uff09\u201d\u4e09\u4e2a\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"BFx5w\" class=\"wp-block-code\"><code>CREATE TABLE range_student_scores (\n    id INT AUTO_INCREMENT,\n    student_name VARCHAR(10) NOT NULL COMMENT '\u5b66\u751f\u59d3\u540d',\n    score INT NOT NULL COMMENT '\u8003\u8bd5\u5206\u6570',\n    PRIMARY KEY (id,score)\n)\n-- \u6309score\u5b57\u6bb5\u8303\u56f4\u5206\u533a\nPARTITION BY RANGE (score) (\n    PARTITION p_fail VALUES LESS THAN (60) COMMENT '\u4e0d\u53ca\u683c\uff080-59\uff09',\n    PARTITION p_good VALUES LESS THAN (80) COMMENT '\u826f\u597d\uff0860-79\uff09',\n    PARTITION p_excellent VALUES LESS THAN (101) COMMENT '\u4f18\u79c0\uff0880-100\uff09'\n);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u2088605b\">\u67e5\u770b\u4e0b\u6570\u636e\u6587\u4ef6\uff1a<\/p>\n\n\n\n<pre id=\"PTvMu\" class=\"wp-block-code\"><code>cd \/data\/mysql\/data\/partition_db\/ &amp;&amp; ll | grep range_student_scores<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"140\" src=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-12-1024x140.png\" alt=\"\" class=\"wp-image-1325\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-12-1024x140.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-12-300x41.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-12-768x105.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-12.png 1038w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"L8edQ\">\u6848\u4f8b2\uff1a\u6309\u65f6\u95f4\u8303\u56f4\u5206\u533a\uff08\u7cfb\u7edf\u65e5\u5fd7\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u1b53e8c6\">\u6309\u5e74\u4efd\u5206\u533a\uff0c\u5b58\u50a8\u4e0d\u540c\u5e74\u4efd\u7684\u65e5\u5fd7\u6570\u636e\uff1a<\/p>\n\n\n\n<pre id=\"lfRtA\" class=\"wp-block-code\"><code>CREATE TABLE range_system_log (\n    id INT NOT NULL,\n    log_content TEXT NOT NULL COMMENT '\u65e5\u5fd7\u5185\u5bb9',\n    log_date DATE NOT NULL COMMENT '\u65e5\u5fd7\u65e5\u671f'\n)\n-- \u6309log_date\u7684\u5e74\u4efd\u5206\u533a\uff08\u901a\u8fc7YEAR()\u51fd\u6570\u63d0\u53d6\u5e74\u4efd\uff09\nPARTITION BY RANGE (YEAR(log_date)) (\n    PARTITION p2022 VALUES LESS THAN (2023) COMMENT '2022\u5e74\u65e5\u5fd7',\n    PARTITION p2023 VALUES LESS THAN (2024) COMMENT '2023\u5e74\u65e5\u5fd7',\n    PARTITION p2024 VALUES LESS THAN (2025) COMMENT '2024\u5e74\u65e5\u5fd7'\n);<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"tzzXw\">2.2 \u5217\u8868\u5206\u533a\uff1a\u79bb\u6563\u6570\u636e\u7684\u7cbe\u51c6\u62c6\u5206<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5b9a\u4e49<\/strong>\uff1a\u6309\u201c\u79bb\u6563\u7684\u6570\u503c\u5217\u8868\u201d\u62c6\u5206\u6570\u636e\uff0c\u5982\u6309\u65e5\u5fd7\u7c7b\u578b\uff081=info\u30012=warn\u30013=error\uff09\u3001\u5730\u533aID\uff0810=\u5317\u4eac\u300120=\u4e0a\u6d77\uff09\u3002<\/li>\n\n\n\n<li><strong>\u6ce8\u610f<\/strong>\uff1a\u4ec5\u652f\u6301\u6574\u6570\u7c7b\u578b\uff0c\u975e\u6574\u6570\u9700\u901a\u8fc7\u51fd\u6570\u8f6c\u5316\uff08\u5982<code>MONTH(log_date)<\/code>\u5c06\u65e5\u671f\u8f6c\u4e3a\u6708\u4efd\u6574\u6570\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"tmS0H\">\u6848\u4f8b1\uff1a\u6309\u6574\u578b\u5217\u8868\u5206\u533a\uff08\u65e5\u5fd7\u7c7b\u578b\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u87ec6f55\">\u6309\u65e5\u5fd7\u7c7b\u578b\uff081=\u4fe1\u606f\u30012=\u8b66\u544a\u30013=\u9519\u8bef\uff09\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"BTxw7\" class=\"wp-block-code\"><code>CREATE TABLE list_log_type (\n    id INT NOT NULL,\n    log_msg VARCHAR(50) NOT NULL COMMENT '\u65e5\u5fd7\u4fe1\u606f',\n    log_type INT NOT NULL COMMENT '\u65e5\u5fd7\u7c7b\u578b\uff1a1=info\uff0c2=warn\uff0c3=error'\n)\n-- \u6309log_type\u7684\u79bb\u6563\u503c\u5206\u533a\nPARTITION BY LIST (log_type) (\n    PARTITION p_info VALUES IN (1) COMMENT '\u4fe1\u606f\u65e5\u5fd7',\n    PARTITION p_warn VALUES IN (2) COMMENT '\u8b66\u544a\u65e5\u5fd7',\n    PARTITION p_error VALUES IN (3) COMMENT '\u9519\u8bef\u65e5\u5fd7'\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"RDtLr\">\u6848\u4f8b2\uff1a\u6309\u65f6\u95f4\u5217\u8868\u5206\u533a\uff08\u5b63\u5ea6\u65e5\u5fd7\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u7e01d13f\">\u6309\u6708\u4efd\u62c6\u5206\u5b63\u5ea6\uff081-3\u6708=Q1\u30014-6\u6708=Q2\u7b49\uff09\uff1a<\/p>\n\n\n\n<pre id=\"iw2by\" class=\"wp-block-code\"><code>CREATE TABLE list_quarter_log (\n    id INT  AUTO_INCREMENT,\n    log_content TEXT NOT NULL COMMENT '\u65e5\u5fd7\u5185\u5bb9',\n    log_date DATE NOT NULL COMMENT '\u65e5\u5fd7\u65e5\u671f',\n    PRIMARY KEY (id,log_date)\n)\n-- \u6309MONTH(log_date)\u8f6c\u5316\u540e\u7684\u6708\u4efd\u6574\u6570\u5206\u533a\nPARTITION BY LIST (MONTH(log_date)) (\n    PARTITION p_q1 VALUES IN (1,2,3) COMMENT '\u7b2c\u4e00\u5b63\u5ea6\uff081-3\u6708\uff09',\n    PARTITION p_q2 VALUES IN (4,5,6) COMMENT '\u7b2c\u4e8c\u5b63\u5ea6\uff084-6\u6708\uff09',\n    PARTITION p_q3 VALUES IN (7,8,9) COMMENT '\u7b2c\u4e09\u5b63\u5ea6\uff087-9\u6708\uff09',\n    PARTITION p_q4 VALUES IN (10,11,12) COMMENT '\u7b2c\u56db\u5b63\u5ea6\uff0810-12\u6708\uff09'\n);<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"aSVUj\">2.3 \u54c8\u5e0c\u5206\u533a\uff1a\u6570\u636e\u5747\u5300\u5206\u6563\u7684\u5229\u5668<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ucd834d2a\"><strong>\u5b9a\u4e49<\/strong>\uff1a\u901a\u8fc7\u54c8\u5e0c\u51fd\u6570\u5c06\u6570\u636e\u201c\u5747\u5300\u5206\u914d\u201d\u5230\u6307\u5b9a\u6570\u91cf\u7684\u5206\u533a\uff0c\u907f\u514d\u624b\u52a8\u5b9a\u4e49\u8303\u56f4\u6216\u5217\u8868\u3002<br><strong>\u9002\u7528\u573a\u666f<\/strong>\uff1a\u6570\u636e\u65e0\u660e\u663e\u8303\u56f4\/\u5217\u8868\u7279\u5f81\uff0c\u9700\u5747\u5300\u5206\u6563\u6570\u636e\u4ee5\u907f\u514d\u70ed\u70b9\u5206\u533a\uff08\u5982\u7528\u6237\u8868\u6309\u7528\u6237ID\u5206\u533a\uff09\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"cQEA0\">\u6848\u4f8b1\uff1a\u6309\u6574\u6570\u54c8\u5e0c\u5206\u533a\uff08\u5b66\u751f\u4fe1\u606f\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u85e76d5f\">\u6309<code>student_id<\/code>\u54c8\u5e0c\uff0c\u5747\u5300\u5206\u914d\u52304\u4e2a\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"ukqcJ\" class=\"wp-block-code\"><code>CREATE TABLE hash_student_info (\n    id INT  AUTO_INCREMENT,\n    student_id INT NOT NULL COMMENT '\u5b66\u751fID',\n    student_name VARCHAR(50) NOT NULL COMMENT '\u5b66\u751f\u59d3\u540d',\n    PRIMARY KEY (id,student_id)\n)\n-- \u6309student_id\u54c8\u5e0c\uff0c\u5206\u4e3a4\u4e2a\u5206\u533a\nPARTITION BY HASH(student_id)\nPARTITIONS 4;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"hXhM4\">\u6848\u4f8b2\uff1a\u6309\u65f6\u95f4\u54c8\u5e0c\u5206\u533a\uff08\u5458\u5de5\u751f\u65e5\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ucb50139b\">\u6309\u751f\u65e5\u5e74\u4efd\u54c8\u5e0c\uff0c\u5206\u914d\u52304\u4e2a\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"EEC5N\" class=\"wp-block-code\"><code>CREATE TABLE hash_employee_birth (\n    id INT  AUTO_INCREMENT,\n    emp_name VARCHAR(50) NOT NULL COMMENT '\u5458\u5de5\u59d3\u540d',\n    birth_date DATE NOT NULL COMMENT '\u751f\u65e5\u65e5\u671f',\n    PRIMARY KEY (id,birth_date)\n)\n-- \u6309YEAR(birth_date)\u63d0\u53d6\u5e74\u4efd\u540e\u54c8\u5e0c\uff0c\u5206\u4e3a4\u4e2a\u5206\u533a\nPARTITION BY HASH(YEAR(birth_date))\nPARTITIONS 4;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"otEkQ\">2.4 \u6309\u952e\u5206\u533a\uff1a\u5b57\u7b26\u4e32\u5206\u533a\u7684\u6700\u4f18\u89e3<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u2b1575ab\"><strong>\u5b9a\u4e49<\/strong>\uff1a\u7c7b\u4f3c\u54c8\u5e0c\u5206\u533a\uff0c\u4f46\u7531MySQL\u81ea\u52a8\u9009\u62e9\u54c8\u5e0c\u51fd\u6570\uff08\u4f18\u5316\u5b57\u7b26\u4e32\u7b49\u7c7b\u578b\u7684\u54c8\u5e0c\u6548\u679c\uff09\uff0c\u652f\u6301\u975e\u6574\u6570\u5206\u533a\u5b57\u6bb5\u3002<br><strong>\u7279\u70b9<\/strong>\uff1a\u82e5\u4e0d\u6307\u5b9a\u5206\u533a\u952e\uff0c\u9ed8\u8ba4\u4f7f\u7528\u4e3b\u952e\uff1b\u65e0\u4e3b\u952e\u5219\u4f7f\u7528\u975e\u7a7a\u552f\u4e00\u7d22\u5f15\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"vS65C\">\u6848\u4f8b1\uff1a\u9ed8\u8ba4\u4e3b\u952e\u6309\u952e\u5206\u533a\uff08\u7528\u6237\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u5d35ac68\">\u4e0d\u6307\u5b9a\u5206\u533a\u952e\uff0c\u9ed8\u8ba4\u6309\u4e3b\u952e<code>id<\/code>\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"k0Ddf\" class=\"wp-block-code\"><code>CREATE TABLE key_user_info (\n    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '\u7528\u6237ID\uff08\u9ed8\u8ba4\u5206\u533a\u952e\uff09',\n    username VARCHAR(20) NOT NULL COMMENT '\u7528\u6237\u540d'\n)\n-- \u6309\u9ed8\u8ba4\u4e3b\u952e\u5206\u533a\uff0c\u5206\u4e3a4\u4e2a\u5206\u533a\nPARTITION BY KEY()\nPARTITIONS 4;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"XIIhZ\">\u6848\u4f8b2\uff1a\u6309\u5b57\u7b26\u4e32\u5b57\u6bb5\u6309\u952e\u5206\u533a\uff08\u5b66\u751f\u59d3\u540d\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u6c68750a\">\u6309\u5b57\u7b26\u4e32<code>name<\/code>\u4f5c\u4e3a\u5206\u533a\u952e\uff0cMySQL\u81ea\u52a8\u4f18\u5316\u54c8\u5e0c\uff1a<\/p>\n\n\n\n<pre id=\"DZKPZ\" class=\"wp-block-code\"><code>CREATE TABLE key_student_name (\n    id INT  AUTO_INCREMENT,\n    name VARCHAR(20) NOT NULL COMMENT '\u5b66\u751f\u59d3\u540d\uff08\u5206\u533a\u952e\uff09',\n    PRIMARY KEY (id,name)\n)\n-- \u6309name\u5b57\u6bb5\u6309\u952e\u5206\u533a\uff0c\u5206\u4e3a4\u4e2a\u5206\u533a\nPARTITION BY KEY(name)\nPARTITIONS 4;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Lua95\">2.5 \u5b50\u5206\u533a\uff1a\u590d\u6742\u573a\u666f\u7684\u7ec4\u5408\u7b56\u7565<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u6ce33564\"><strong>\u5b9a\u4e49<\/strong>\uff1a\u5728\u201c\u4e3b\u5206\u533a\u201d\u57fa\u7840\u4e0a\u8fdb\u4e00\u6b65\u62c6\u5206\u201c\u5b50\u5206\u533a\u201d\uff0c\u652f\u6301\u7ec4\u5408\u5206\u533a\u7b56\u7565\uff08\u5982\u5148\u6309\u65f6\u95f4\u8303\u56f4\u5206\u533a\uff0c\u518d\u6309\u54c8\u5e0c\u5b50\u5206\u533a\uff09\u3002<br><strong>\u9002\u7528\u573a\u666f<\/strong>\uff1a\u9700\u540c\u65f6\u6ee1\u8db3\u4e24\u79cd\u5206\u533a\u9700\u6c42\uff0c\u5982\u201c\u6309\u5e74\u4efd\u8303\u56f4\u5206\u533a\uff08\u4e3b\u5206\u533a\uff09+ \u6309\u65e5\u671f\u54c8\u5e0c\u5206\u6563\u6570\u636e\uff08\u5b50\u5206\u533a\uff09\u201d\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"JWJJb\">\u6848\u4f8b\uff1a\u8303\u56f4-\u54c8\u5e0c\u5b50\u5206\u533a\uff08\u7528\u6237\u751f\u65e5\u8868\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ud010f0f8\">\u5148\u6309\u751f\u65e5\u5e74\u4efd\u8303\u56f4\u5206\u4e3b\u5206\u533a\uff0c\u518d\u6309\u65e5\u671f\u54c8\u5e0c\u5206\u5b50\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"psr8U\" class=\"wp-block-code\"><code>CREATE TABLE sub_employee_birth (\n    id INT  AUTO_INCREMENT,\n    emp_name VARCHAR(50) NOT NULL,\n    birth_date DATE NOT NULL,\n    PRIMARY KEY (id,birth_date)\n)\n-- \u4e3b\u5206\u533a\uff1a\u6309\u751f\u65e5\u5e74\u4efd\u8303\u56f4\u5206\u533a\nPARTITION BY RANGE (YEAR(birth_date))\n-- \u5b50\u5206\u533a\uff1a\u6309\u751f\u65e5\u65e5\u671f\u54c8\u5e0c\uff0c\u6bcf\u4e2a\u4e3b\u5206\u533a\u4e0b\u52062\u4e2a\u5b50\u5206\u533a\nSUBPARTITION BY HASH(TO_DAYS(birth_date))\nSUBPARTITIONS 2 (\n    PARTITION p2000 VALUES LESS THAN (2010) COMMENT '\u4e3b\u5206\u533a\uff1a2010\u5e74\u524d\u751f\u65e5',\n    PARTITION p2010 VALUES LESS THAN (2020) COMMENT '\u4e3b\u5206\u533a\uff1a2010-2019\u5e74\u751f\u65e5',\n    PARTITION p2020 VALUES LESS THAN (2030) COMMENT '\u4e3b\u5206\u533a\uff1a2020\u5e74\u540e\u751f\u65e5'\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Fx3Mi\">\u6848\u4f8b\uff1a\u5217\u8868-\u6309\u952e\u5b50\u5206\u533a\uff08\u5458\u5de5\u90e8\u95e8\u8868\uff09<\/h3>\n\n\n\n<pre id=\"cM928\" class=\"wp-block-code\"><code>CREATE TABLE sub_employees (\n    id INT,\n    employee_name VARCHAR(50),\n    department_id INT\n)\nPARTITION BY list (department_id)\nSUBPARTITION BY KEY (id)\nSUBPARTITIONS 4 (\n    PARTITION p1 VALUES IN (1, 2) (\n    SUBPARTITION s1,\n    SUBPARTITION s2,\n    SUBPARTITION s3,\n    SUBPARTITION s4\n    ),\n    PARTITION p2 VALUES IN (3, 4) (\n    SUBPARTITION s5,\n    SUBPARTITION s6,\n    SUBPARTITION s7,\n    SUBPARTITION s8\n    )\n);<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"MhBYF\">\u4e09\u3001\u5206\u533a\u8868\u7684\u65e5\u5e38\u7ba1\u7406\u6280\u5de7<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"MhBYF\"><br>\u521b\u5efa\u5206\u533a\u8868\u540e\uff0c\u9700\u5b9a\u671f\u8fdb\u884c\u7ef4\u62a4\u64cd\u4f5c\uff08\u5982\u65b0\u589e\u5206\u533a\u3001\u5220\u9664\u5386\u53f2\u5206\u533a\u3001\u67e5\u770b\u5206\u533a\u6570\u636e\uff09\u3002\u4ee5\u4e0b\u4e3a\u5e38\u7528\u7ba1\u7406\u64cd\u4f5c\u7684\u5b9e\u64cd\u6848\u4f8b\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"s9BsJ\">3.1 \u8303\u56f4\u5206\u533a\u7ba1\u7406\uff08\u6700\u5e38\u7528\uff09<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"B2osm\">1. \u65b0\u589e\u5206\u533a\uff08\u5982\u65b0\u589e2025\u5e74\u65e5\u5fd7\u5206\u533a\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u8c0c6cda\">\u9488\u5bf9<code>range_system_log<\/code>\u8868\uff08\u6309\u5e74\u4efd\u5206\u533a\uff09\uff0c\u65b0\u589e2025\u5e74\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"kvinr\" class=\"wp-block-code\"><code>-- \u65b0\u589e2025\u5e74\u5206\u533a\uff08VALUES LESS THAN (2026)\u8868\u793a2025\u5e74\u53ca\u4e4b\u524d\u6570\u636e\uff09\nALTER TABLE range_system_log\nADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));\n\n-- \u9a8c\u8bc1\u5206\u533a\u662f\u5426\u521b\u5efa\u6210\u529f\nSHOW CREATE TABLE range_system_log;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" width=\"1024\" height=\"521\" src=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-13-1024x521.png\" alt=\"\" class=\"wp-image-1326\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-13-1024x521.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-13-300x153.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-13-768x390.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-13.png 1139w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"O7i0z\">2. \u67e5\u770b\u5206\u533a\u6570\u636e\uff08\u5982\u67e5\u770b2024\u5e74\u65e5\u5fd7\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u59f57d15\">\u5199\u5165\u6d4b\u8bd5\u6570\u636e<\/p>\n\n\n\n<pre id=\"lLmEy\" class=\"wp-block-code\"><code>INSERT INTO range_system_log (id, log_content, log_date) VALUES\n    (1, 'Test message 2020', '2020-01-01'),\n    (2, 'Test message 2020', '2020-02-01'),\n    (3, 'Test message 2020', '2020-03-01'),\n    (4, 'Test message 2021', '2021-01-01'),\n    (5, 'Test message 2021', '2021-02-01'),\n    (6, 'Test message 2021', '2021-03-01'),\n    (7, 'Test message 2022', '2022-01-01'),\n    (8, 'Test message 2022', '2022-02-01'),\n    (9, 'Test message 2022', '2022-03-01'),\n    (10, 'Test message 2023', '2023-01-01'),\n    (11, 'Test message 2023', '2023-02-01'),\n    (12, 'Test message 2023', '2023-03-01');\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ub26fd5c7\">\u67e5\u770b\u5206\u533a\u7684\u6570\u636e<\/p>\n\n\n\n<pre id=\"zOR5S\" class=\"wp-block-code\"><code>-- \u67e5\u770b\u6307\u5b9a\u5206\u533a\uff08p2023\uff09\u7684\u6570\u636e\nSELECT * FROM range_system_log PARTITION (p2023);\n\n-- \u7edf\u8ba1\u6240\u6709\u5206\u533a\u7684\u6570\u636e\u91cf\uff08\u5b9e\u7528\uff01\uff09\nSELECT \n    PARTITION_NAME AS '\u5206\u533a\u540d',\n    TABLE_ROWS AS '\u6570\u636e\u91cf'\nFROM INFORMATION_SCHEMA.PARTITIONS \nWHERE TABLE_NAME = 'range_system_log';<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" width=\"1024\" height=\"341\" src=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-14-1024x341.png\" alt=\"\" class=\"wp-image-1327\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-14-1024x341.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-14-300x100.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-14-768x256.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-14.png 1400w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"w9NGn\">3. \u5220\u9664\u5206\u533a\uff08\u5982\u5220\u96642022\u5e74\u5386\u53f2\u65e5\u5fd7\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ua9401492\"><strong>\u6ce8\u610f<\/strong>\uff1a\u5220\u9664\u5206\u533a\u4f1a\u540c\u65f6\u5220\u9664\u5206\u533a\u5185\u7684\u6240\u6709\u6570\u636e\uff0c\u4e14\u4e0d\u53ef\u6062\u590d\uff01<\/p>\n\n\n\n<pre id=\"i1lnK\" class=\"wp-block-code\"><code>-- \u5220\u96642022\u5e74\u5206\u533a\uff08p2022\uff09\nALTER TABLE range_system_log\nDROP PARTITION p2022;\n\n-- \u9a8c\u8bc1\u5206\u533a\u662f\u5426\u5220\u9664\nSHOW CREATE TABLE range_system_log;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"436\" src=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-15-1024x436.png\" alt=\"\" class=\"wp-image-1328\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-15-1024x436.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-15-300x128.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-15-768x327.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-15.png 1346w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"p9K6x\">4. \u5206\u533a\u4ea4\u6362\uff08\u5c06\u5206\u533a\u6570\u636e\u4e0e\u4e34\u65f6\u8868\u4ea4\u6362\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u0a44c65d\">\u9002\u7528\u4e8e\u6570\u636e\u8fc1\u79fb\u573a\u666f\uff08\u5982\u5c062023\u5e74\u65e5\u5fd7\u8fc1\u79fb\u5230\u4e34\u65f6\u8868\u5f52\u6863\uff09\uff1a<\/p>\n\n\n\n<pre id=\"IsPtP\" class=\"wp-block-code\"><code>-- 1. \u521b\u5efa\u4e0e\u539f\u8868\u7ed3\u6784\u4e00\u81f4\u7684\u4e34\u65f6\u8868\uff08\u4e0d\u542b\u5206\u533a\uff09\nCREATE TABLE range_log_tmp LIKE range_system_log;\nALTER TABLE range_log_tmp REMOVE PARTITIONING; -- \u5220\u9664\u4e34\u65f6\u8868\u7684\u5206\u533a\u914d\u7f6e\n\n-- 2. \u4ea4\u6362p2023\u5206\u533a\u4e0e\u4e34\u65f6\u8868\u6570\u636e\uff08\u4e34\u65f6\u8868\u6570\u636e\u9700\u7b26\u5408p2023\u5206\u533a\u8303\u56f4\uff09\nALTER TABLE range_system_log\nEXCHANGE PARTITION p2023 WITH TABLE range_log_tmp;\n\n-- 3. \u9a8c\u8bc1\u4ea4\u6362\u7ed3\u679c\uff08\u539f\u8868p2023\u6570\u636e\u4e3a\u7a7a\uff0c\u4e34\u65f6\u8868\u5305\u542b\u539fp2023\u6570\u636e\uff09\nSELECT * FROM range_system_log PARTITION (p2023);\nSELECT * FROM range_log_tmp;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"431\" src=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-16-1024x431.png\" alt=\"\" class=\"wp-image-1329\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-16-1024x431.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-16-300x126.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-16-768x323.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-16.png 1162w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"s6qQZ\">5.\u91cd\u5efa\u5206\u533a<\/h4>\n\n\n\n<pre id=\"QPLhy\" class=\"wp-block-code\"><code>ALTER TABLE range_system_log REBUILD PARTITION p2023, p2024, p2025;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"F1ajJ\">3.2 \u5217\u8868\u5206\u533a\u7ba1\u7406<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"gTH54\">1. \u65b0\u589e\u5206\u533a\uff08\u5982\u65b0\u589e\u201c\u8c03\u8bd5\u65e5\u5fd7\u201d\u7c7b\u578b\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ua677ce5e\">\u9488\u5bf9<code>list_log_type<\/code>\u8868\uff08\u6309\u65e5\u5fd7\u7c7b\u578b\u5206\u533a\uff09\uff0c\u65b0\u589e<code>0=\u8c03\u8bd5\u65e5\u5fd7<\/code>\u5206\u533a\uff1a<\/p>\n\n\n\n<pre id=\"cAbn4\" class=\"wp-block-code\"><code>ALTER TABLE list_log_type\nADD PARTITION (PARTITION p_debug VALUES IN (0) COMMENT '\u8c03\u8bd5\u65e5\u5fd7');\n\n-- \u9a8c\u8bc1\u5206\u533a\u662f\u5426\u521b\u5efa\u6210\u529f\nSHOW CREATE TABLE list_log_type;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"504\" src=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-17-1024x504.png\" alt=\"\" class=\"wp-image-1330\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-17-1024x504.png 1024w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-17-300x148.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-17-768x378.png 768w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-17.png 1213w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"Gglqy\">2.\u67e5\u8be2\u5206\u533a \uff08\u5982\u67e5\u8be2\u201c\u9519\u8bef\u65e5\u5fd7\u201d\u5206\u533a\uff09<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ubfc41fb4\">\u5199\u5165\u6d4b\u8bd5\u6570\u636e<\/p>\n\n\n\n<pre id=\"ajnar\" class=\"wp-block-code\"><code>INSERT INTO list_log_type (id, log_msg, log_type) VALUES\n    (1, 'Information message 1',  1),\n    (2, 'Information message 2',  1),\n    (3, 'Information message 3', 1),\n    (4, 'Warning message 1',  2),\n    (5, 'Warning message 2',  2),\n    (6, 'Warning message 3', 2),\n    (7, 'Error message 1',3),\n    (8, 'Error message 2',  3),\n    (9, 'Error message 3', 3);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u049996a9\">\u67e5\u8be2\u5206\u533a\u6570\u636e<\/p>\n\n\n\n<pre id=\"xN4m1\" class=\"wp-block-code\"><code>select * from list_log_type partition (p_error);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"oiKHX\">2. \u5220\u9664\u5206\u533a\uff08\u5982\u5220\u9664\u201c\u8c03\u8bd5\u65e5\u5fd7\u201d\u5206\u533a\uff09<\/h4>\n\n\n\n<pre id=\"ECkU6\" class=\"wp-block-code\"><code>ALTER TABLE list_log_type\nDROP PARTITION p_debug;\n\n-- \u9a8c\u8bc1\u5206\u533a\u662f\u5426\u5220\u9664\nSHOW CREATE TABLE list_log_type;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"974\" height=\"502\" src=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-18.png\" alt=\"\" class=\"wp-image-1331\" srcset=\"https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-18.png 974w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-18-300x155.png 300w, https:\/\/yunyanglib.cn\/wp-content\/uploads\/2026\/04\/image-18-768x396.png 768w\" sizes=\"(max-width: 974px) 100vw, 974px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"RKTHc\">3.3 \u54c8\u5e0c\/\u6309\u952e\u5206\u533a\u7ba1\u7406\uff08\u589e\u51cf\u5206\u533a\u6570\u91cf\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u5ca39cca\">\u54c8\u5e0c\u4e0e\u6309\u952e\u5206\u533a\u4e0d\u652f\u6301\u76f4\u63a5\u65b0\u589e\/\u5220\u9664\u5355\u4e2a\u5206\u533a\uff0c\u9700\u901a\u8fc7<code>COALESCE<\/code>\uff08\u51cf\u5c11\u5206\u533a\uff09\u6216<code>ADD PARTITION<\/code>\uff08\u589e\u52a0\u5206\u533a\uff09\u8c03\u6574\u603b\u6570\uff1a<\/p>\n\n\n\n<pre id=\"s7mpd\" class=\"wp-block-code\"><code>-- \u9488\u5bf9hash_student_info\u8868\uff08\u539f4\u4e2a\u5206\u533a\uff09\n-- 1. \u51cf\u5c111\u4e2a\u5206\u533a\uff08\u603b\u6570\u53d8\u4e3a3\uff09\nALTER TABLE hash_student_info COALESCE PARTITION 1;\n\n-- 2. \u589e\u52a02\u4e2a\u5206\u533a\uff08\u603b\u6570\u53d8\u4e3a5\uff09\nALTER TABLE hash_student_info ADD PARTITION PARTITIONS 2;\n\n\n-- \u9488\u5bf9key_user_info\u8868\uff08\u539f4\u4e2a\u5206\u533a\uff09\n-- 1. \u51cf\u5c111\u4e2a\u5206\u533a\uff08\u603b\u6570\u53d8\u4e3a3\uff09\nALTER TABLE key_user_info COALESCE PARTITION 1;\n\n-- 2. \u589e\u52a02\u4e2a\u5206\u533a\uff08\u603b\u6570\u53d8\u4e3a5\uff09\nALTER TABLE key_user_info ADD PARTITION PARTITIONS 2;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"GuEAm\">3.4 \u5b50\u5206\u533a\u7ba1\u7406<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"QgDzA\">1. \u5220\u9664\u5b50\u5206\u533a\uff08\u5982\u5220\u9664p2000\u4e3b\u5206\u533a\u4e0b\u7684\u5b50\u5206\u533a\uff09<\/h4>\n\n\n\n<pre id=\"PW4uo\" class=\"wp-block-code\"><code>-- \u5220\u9664p2000\u4e3b\u5206\u533a\uff08\u5b50\u5206\u533a\u4f1a\u968f\u4e3b\u5206\u533a\u4e00\u8d77\u5220\u9664\uff09\nALTER TABLE sub_employee_birth\nDROP PARTITION p2000;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"Dt8qP\">2. \u8c03\u6574\u5b50\u5206\u533a\u8303\u56f4\uff08\u5982\u6269\u5c55p2020\u4e3b\u5206\u533a\u8303\u56f4\uff09<\/h4>\n\n\n\n<pre id=\"AYhtK\" class=\"wp-block-code\"><code>-- \u5c06p2020\u4e3b\u5206\u533a\u8303\u56f4\u4ece\u201c&lt;2030\u201d\u8c03\u6574\u4e3a\u201c&lt;MAXVALUE\u201d\nALTER TABLE sub_employee_birth\nREORGANIZE PARTITION p2020 INTO (\n    PARTITION p2020 VALUES LESS THAN MAXVALUE\n);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ub336e907\">\u6ce8\u610f\uff1a\u5728\u4f7f\u7528<code>REORGANIZE PARTITION<\/code>\u91cd\u7ec4\u8303\u56f4\u5206\u533a\u65f6\uff0c\u9664\u4e86\u6700\u540e\u4e00\u4e2a\u5206\u533a\u53ef\u4ee5\u6269\u5c55\u8303\u56f4\u5916\uff0c\u4e0d\u80fd\u6539\u53d8\u6240\u6709\u5206\u533a\u7684\u603b\u8303\u56f4\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"SWFAb\">\u56db\u3001\u5206\u533a vs \u5206\u5e93\u5206\u8868\uff1a\u8be5\u600e\u4e48\u9009\uff1f<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u7f1a75cd\">\u5f88\u591a\u540c\u5b66\u4f1a\u6df7\u6dc6\u8fd9\u4e24\u4e2a\u6982\u5ff5\uff0c\u5176\u5b9e\u6838\u5fc3\u533a\u522b\u5728\u4e8e\u6570\u636e\u5b58\u50a8\u8303\u56f4\u548c\u590d\u6742\u5ea6\uff0c\u4e00\u5f20\u8868\u8bb2\u6e05\u695a\uff1a<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>\u7ef4\u5ea6<\/strong><\/td><td><strong>MySQL\u5206\u533a<\/strong><\/td><td><strong>\u5206\u5e93\u5206\u8868\uff08\u5982ShardingSphere\uff09<\/strong><\/td><\/tr><tr><td>\u6570\u636e\u5b58\u50a8\u8303\u56f4<\/td><td>\u5355\u5e93\u5185\uff0c\u5206\u533a\u6587\u4ef6\u4ecd\u5728\u540c\u4e00\u6570\u636e\u5e93\u5b9e\u4f8b<\/td><td>\u8de8\u5e93\u8de8\u5b9e\u4f8b\uff0c\u6570\u636e\u5206\u6563\u5728\u591a\u4e2a\u5e93\/\u8868<\/td><\/tr><tr><td>\u903b\u8f91\u5f62\u6001<\/td><td>\u903b\u8f91\u4e0a\u662f\u5355\u8868\uff0c\u7528\u6237\u65e0\u9700\u611f\u77e5\u5206\u533a<\/td><td>\u903b\u8f91\u4e0a\u662f\u591a\u8868\uff0c\u9700\u901a\u8fc7\u4e2d\u95f4\u4ef6\u8def\u7531<\/td><\/tr><tr><td>\u9002\u7528\u6570\u636e\u91cf<\/td><td>\u5355\u88681000\u4e07-10\u4ebf\uff08\u5355\u5e93\u6027\u80fd\u4e0a\u9650\u5185\uff09<\/td><td>\u5355\u886810\u4ebf\u4ee5\u4e0a\uff08\u7a81\u7834\u5355\u5e93\u6027\u80fd\u74f6\u9888\uff09<\/td><\/tr><tr><td>\u590d\u6742\u5ea6<\/td><td>\u4f4e\uff08MySQL\u539f\u751f\u652f\u6301\uff0c\u65e0\u9700\u989d\u5916\u7ec4\u4ef6\uff09<\/td><td>\u9ad8\uff08\u9700\u4e2d\u95f4\u4ef6\u3001\u5206\u5e03\u5f0f\u4e8b\u52a1\u7b49\uff09<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u17c39c7f\"><strong>\u9009\u62e9\u5efa\u8bae<\/strong>\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5355\u5e93\u5185\u6570\u636e\u91cf\u672a\u7a81\u783410\u4ebf\uff0c\u4f18\u5148\u7528MySQL\u5206\u533a\uff08\u4f4e\u6210\u672c\u3001\u6613\u7ef4\u62a4\uff09\uff1b<\/li>\n\n\n\n<li>\u6570\u636e\u91cf\u8d8510\u4ebf\u6216\u9700\u8de8\u5b9e\u4f8b\u6269\u5c55\uff0c\u518d\u8003\u8651\u5206\u5e93\u5206\u8868\uff08\u5982\u7535\u5546\u8ba2\u5355\u8868\u3001\u7528\u6237\u8868\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">\u4e94\u3001\u751f\u4ea7\u73af\u5883\u6700\u4f73\u5b9e\u8df5<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u5206\u533a\u952e\u9009\u62e9<\/strong>\uff1a\u4f18\u5148\u9009\u62e9\u67e5\u8be2\u9891\u7387\u6700\u9ad8\u7684\u5b57\u6bb5\uff08\u5982\u8ba2\u5355\u8868\u7684 create_time\uff09\uff0c\u786e\u4fdd\u67e5\u8be2\u80fd\u547d\u4e2d\u5206\u533a\uff1b<\/li>\n\n\n\n<li><strong>\u5206\u533a\u6570\u91cf<\/strong>\uff1a\u5efa\u8bae\u5355\u4e2a\u8868\u5206\u533a\u6570\u4e0d\u8d85\u8fc7 50 \u4e2a\uff0c\u8fc7\u591a\u4f1a\u589e\u52a0\u5143\u6570\u636e\u5f00\u9500\uff1b<\/li>\n\n\n\n<li><strong>\u9884\u7559\u5206\u533a<\/strong>\uff1a\u521b\u5efa\u8868\u65f6\u63d0\u524d\u9884\u7559\u672a\u6765 1-2 \u5e74\u7684\u5206\u533a\uff0c\u907f\u514d\u540e\u7eed\u9891\u7e41\u6269\u5bb9\uff1b<\/li>\n\n\n\n<li><strong>\u5b9a\u671f\u7ef4\u62a4<\/strong>\uff1a\u6bcf\u6708\u68c0\u67e5\u5206\u533a\u6570\u636e\u5206\u5e03\uff0c\u5bf9\u6570\u636e\u503e\u659c\u7684\u5206\u533a\u53ca\u65f6\u8c03\u6574\uff1b<\/li>\n\n\n\n<li><strong>\u5907\u4efd\u7b56\u7565<\/strong>\uff1a\u5bf9\u91cd\u8981\u5206\u533a\u5355\u72ec\u5907\u4efd\uff0c\u964d\u4f4e\u6062\u590d\u98ce\u9669\u3002<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">\u7ed3\u8bed<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">MySQL \u5206\u533a\u662f\u5927\u8868\u4f18\u5316\u7684\u6838\u5fc3\u6280\u672f\u4e4b\u4e00\uff0c\u5408\u7406\u4f7f\u7528\u80fd\u5927\u5e45\u63d0\u5347\u67e5\u8be2\u6548\u7387\u548c\u7ef4\u62a4\u4fbf\u5229\u6027\u3002\u4f46\u5206\u533a\u4e0d\u662f\u94f6\u5f39\uff0c\u9700\u7ed3\u5408\u4e1a\u52a1\u573a\u666f\u9009\u62e9\u5408\u9002\u7684\u5206\u533a\u7c7b\u578b\u548c\u7b56\u7565\u3002\u5982\u679c\u4f60\u7684\u4e1a\u52a1\u4e2d\u4e5f\u6709\u5927\u8868\u6027\u80fd\u74f6\u9888\uff0c\u4e0d\u59a8\u8bd5\u8bd5\u6587\u4e2d\u7684\u65b9\u6848\uff0c\u6b22\u8fce\u5728\u8bc4\u8bba\u533a\u5206\u4eab\u4f60\u7684\u5b9e\u8df5\u7ecf\u9a8c\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u524d\u8a00\uff1a\u4e3a\u4ec0\u4e48\u5927\u8868\u5fc5\u987b\u5b66\u4f1a\u5206\u533a\uff1f \u4f5c\u4e3a\u4e00\u540d\u957f\u671f\u6df1\u8015\u540e\u7aef\u5f00\u53d1\u7684\u5de5\u7a0b\u5e08\uff0c\u76f8\u4fe1\u5f88\u591a\u540c\u5b66\u90fd\u9047\u5230\u8fc7\u8fd9\u6837\u7684\u75db\u70b9\uff1a\u968f\u7740\u4e1a\u52a1\u589e\u957f<\/p>\n<div class=\"more-link\">\n\t\t\t\t <a href=\"https:\/\/yunyanglib.cn\/?p=1320\" class=\"link-btn theme-btn\"><span>Read More <\/span> <i class=\"fa fa-caret-right\"><\/i><\/a>\n\t\t\t<\/div>\n","protected":false},"author":1,"featured_media":1332,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[16],"class_list":["post-1320","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql"],"_links":{"self":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/1320","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1320"}],"version-history":[{"count":6,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/1320\/revisions"}],"predecessor-version":[{"id":1334,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/1320\/revisions\/1334"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/media\/1332"}],"wp:attachment":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1320"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1320"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}