https://wiki.nonip.net/index.php?action=history&feed=atom&title=Mysql%2FPARTITION_BY_RANGE 2026-04-19T01:42:14Z このウィキのこのページに関する変更履歴 MediaWiki 1.24.2 https://wiki.nonip.net/index.php?title=Mysql/PARTITION_BY_RANGE&diff=19752&oldid=prev 2025-03-04T14:57:29Z

<p>‎<span dir="auto"><span class="autocomment">PARTITION BY RANGEとは</span></span></p> <table class='diff diff-contentalign-left'> <col class='diff-marker' /> <col class='diff-content' /> <col class='diff-marker' /> <col class='diff-content' /> <tr style='vertical-align: top;'> <td colspan='2' style="background-color: white; color:black; text-align: center;">←前の版</td> <td colspan='2' style="background-color: white; color:black; text-align: center;">2025年3月4日 (火) 14:57時点における版</td> </tr><tr><td colspan="2" class="diff-lineno">行1:</td> <td colspan="2" class="diff-lineno">行1:</td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>==PARTITION BY RANGEとは==</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>==PARTITION BY RANGEとは==</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>カラムをPARTITION BY RANGE対応にすると、1つのパーティション内の条件のsqlの速度向上が期待できる。</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>カラムをPARTITION BY RANGE対応にすると、1つのパーティション内の条件のsqlの速度向上が期待できる。</div></td></tr> <tr><td colspan="2">&#160;</td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr> <tr><td colspan="2">&#160;</td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">注意：innodb限定</ins></div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>公式：https://dev.mysql.com/doc/refman/8.0/ja/partitioning-range.html</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>公式：https://dev.mysql.com/doc/refman/8.0/ja/partitioning-range.html</div></td></tr> </table>

Admin https://wiki.nonip.net/index.php?title=Mysql/PARTITION_BY_RANGE&diff=19751&oldid=prev 2025-03-04T14:44:42Z

<p></p> <table class='diff diff-contentalign-left'> <col class='diff-marker' /> <col class='diff-content' /> <col class='diff-marker' /> <col class='diff-content' /> <tr style='vertical-align: top;'> <td colspan='2' style="background-color: white; color:black; text-align: center;">←前の版</td> <td colspan='2' style="background-color: white; color:black; text-align: center;">2025年3月4日 (火) 14:44時点における版</td> </tr><tr><td colspan="2" class="diff-lineno">行32:</td> <td colspan="2" class="diff-lineno">行32:</td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>)</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>)</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <del class="diffchange diffchange-inline">p202503 </del>VALUES LESS THAN ( UNIX_TIMESTAMP('<del class="diffchange diffchange-inline">2025</del>-04-01 00:00:00') ),</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <ins class="diffchange diffchange-inline">p201503 </ins>VALUES LESS THAN ( UNIX_TIMESTAMP('<ins class="diffchange diffchange-inline">2015</ins>-04-01 00:00:00') ),</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <del class="diffchange diffchange-inline">p202504 </del>VALUES LESS THAN ( UNIX_TIMESTAMP('<del class="diffchange diffchange-inline">2025</del>-05-01 00:00:00') ),</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <ins class="diffchange diffchange-inline">p201504 </ins>VALUES LESS THAN ( UNIX_TIMESTAMP('<ins class="diffchange diffchange-inline">2015</ins>-05-01 00:00:00') ),</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <del class="diffchange diffchange-inline">p202505 </del>VALUES LESS THAN ( UNIX_TIMESTAMP('<del class="diffchange diffchange-inline">2025</del>-06-01 00:00:00') ),</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <ins class="diffchange diffchange-inline">p201505 </ins>VALUES LESS THAN ( UNIX_TIMESTAMP('<ins class="diffchange diffchange-inline">2015</ins>-06-01 00:00:00') ),</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <del class="diffchange diffchange-inline">p202506 </del>VALUES LESS THAN ( UNIX_TIMESTAMP('<del class="diffchange diffchange-inline">2025</del>-07-01 00:00:00') ),</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <ins class="diffchange diffchange-inline">p201506 </ins>VALUES LESS THAN ( UNIX_TIMESTAMP('<ins class="diffchange diffchange-inline">2015</ins>-07-01 00:00:00') ),</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <del class="diffchange diffchange-inline">p202507 </del>VALUES LESS THAN ( UNIX_TIMESTAMP('<del class="diffchange diffchange-inline">2025</del>-08-01 00:00:00') ),</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <ins class="diffchange diffchange-inline">p201507 </ins>VALUES LESS THAN ( UNIX_TIMESTAMP('<ins class="diffchange diffchange-inline">2015</ins>-08-01 00:00:00') ),</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION pmax VALUES LESS THAN (MAXVALUE)</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION pmax VALUES LESS THAN (MAXVALUE)</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>);</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>);</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>&lt;/pre&gt;</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>&lt;/pre&gt;</div></td></tr> </table>

Admin https://wiki.nonip.net/index.php?title=Mysql/PARTITION_BY_RANGE&diff=19750&oldid=prev 2025-03-04T14:21:23Z

<p>‎<span dir="auto"><span class="autocomment">PARTITION BY RANGEとは</span></span></p> <table class='diff diff-contentalign-left'> <col class='diff-marker' /> <col class='diff-content' /> <col class='diff-marker' /> <col class='diff-content' /> <tr style='vertical-align: top;'> <td colspan='2' style="background-color: white; color:black; text-align: center;">←前の版</td> <td colspan='2' style="background-color: white; color:black; text-align: center;">2025年3月4日 (火) 14:21時点における版</td> </tr><tr><td colspan="2" class="diff-lineno">行37:</td> <td colspan="2" class="diff-lineno">行37:</td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION p202506 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-07-01 00:00:00') ),</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION p202506 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-07-01 00:00:00') ),</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION p202507 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-08-01 00:00:00') ),</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>PARTITION p202507 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-08-01 00:00:00') ),</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <del class="diffchange diffchange-inline">p9 </del>VALUES LESS THAN (MAXVALUE)</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>PARTITION <ins class="diffchange diffchange-inline">pmax </ins>VALUES LESS THAN (MAXVALUE)</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>);</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>);</div></td></tr> <tr><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>&lt;/pre&gt;</div></td><td class='diff-marker'>&#160;</td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>&lt;/pre&gt;</div></td></tr> </table>

Admin https://wiki.nonip.net/index.php?title=Mysql/PARTITION_BY_RANGE&diff=19749&oldid=prev 2025-03-04T14:20:59Z

<p>ページの作成:「==PARTITION BY RANGEとは== カラムをPARTITION BY RANGE対応にすると、1つのパーティション内の条件のsqlの速度向上が期待できる。 公式...」</p> <p><b>新規ページ</b></p><div>==PARTITION BY RANGEとは==<br /> カラムをPARTITION BY RANGE対応にすると、1つのパーティション内の条件のsqlの速度向上が期待できる。<br /> <br /> 公式：https://dev.mysql.com/doc/refman/8.0/ja/partitioning-range.html<br /> 例<br /> &lt;pre&gt;<br /> CREATE TABLE quarterly_report_status (<br /> report_id INT NOT NULL,<br /> report_status VARCHAR(20) NOT NULL,<br /> report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<br /> )<br /> PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (<br /> PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),<br /> PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),<br /> PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),<br /> PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),<br /> PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),<br /> PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),<br /> PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),<br /> PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),<br /> PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),<br /> PARTITION p9 VALUES LESS THAN (MAXVALUE)<br /> );<br /> &lt;/pre&gt;<br /> <br /> 一ヶ月単位だと<br /> &lt;pre&gt;<br /> CREATE TABLE quarterly_report_status (<br /> report_id INT NOT NULL,<br /> report_status VARCHAR(20) NOT NULL,<br /> report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<br /> )<br /> PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (<br /> PARTITION p202503 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-04-01 00:00:00') ),<br /> PARTITION p202504 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-05-01 00:00:00') ),<br /> PARTITION p202505 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-06-01 00:00:00') ),<br /> PARTITION p202506 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-07-01 00:00:00') ),<br /> PARTITION p202507 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-08-01 00:00:00') ),<br /> PARTITION p9 VALUES LESS THAN (MAXVALUE)<br /> );<br /> &lt;/pre&gt;</div>

Admin