深入了解MySQL 5.5分区功能增强_MySQL
배우다
분할
기능
향상시키다
깊이 들어가다
MySQL 5.5的发布带来了许多增强的功能，虽然已经报道了很多增强功能，如半同步复制，但大家却忽略了分区方面的增强，有时甚至还对其真正意义产生了误解，在这篇文章中，我们希望解释一下这些很酷的增强，特别是我们大多数人还没有完全理解的地方。bitscn向您推荐《MySQL数据库入门与精通教程》。
图 1 大家还没注意到我MySQL的分区功能也很强了哦
非整数列分区
任何使用过分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 5.1只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。
MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。我们假设有这样一个表：
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> expenses ( </span></span></li> <li> <span> expense_date </span><span class="keyword"><strong><font color="#006699">DATE</font></strong></span><span> </span><span class="op"><font color="#808080">NOT</font></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span>, </span> </li> <li class="alt"> <span> category </span><span class="keyword"><strong><font color="#006699">VARCHAR</font></strong></span><span>(30), </span> </li> <li> <span> amount </span><span class="keyword"><strong><font color="#006699">DECIMAL</font></strong></span><span> (10,3) </span> </li> <li class="alt"><span>); </span></li> </ol>
로그인 후 복사
如果你想使用MySQL 5.1中的分区类型，那你必须将类型转换成整数，需要使用一个额外的查找表，到了MySQL 5.5中，你可以不用再进行类型转换了，如：
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">ALTER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> expenses </span></span></li> <li> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> LIST COLUMNS (category) </span> </li> <li class="alt"><span>( </span></li> <li> <span> PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> </span><span class="op"><font color="#808080">IN</font></span><span> ( </span><span class="string"><font color="#0000ff">'lodging'</font></span><span>, </span><span class="string"><font color="#0000ff">'food'</font></span><span>), </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> </span><span class="op"><font color="#808080">IN</font></span><span> ( </span><span class="string"><font color="#0000ff">'flights'</font></span><span>, </span><span class="string"><font color="#0000ff">'ground transportation'</font></span><span>), </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> </span><span class="op"><font color="#808080">IN</font></span><span> ( </span><span class="string"><font color="#0000ff">'leisure'</font></span><span>, </span><span class="string"><font color="#0000ff">'customer entertainment'</font></span><span>), </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> </span><span class="op"><font color="#808080">IN</font></span><span> ( </span><span class="string"><font color="#0000ff">'communications'</font></span><span>), </span> </li> <li> <span> PARTITION p05 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> </span><span class="op"><font color="#808080">IN</font></span><span> ( </span><span class="string"><font color="#0000ff">'fees'</font></span><span>) </span> </li> <li class="alt"><span>); </span></li> </ol>
로그인 후 복사
这样的分区语句除了更加易读外，对数据的组织和管理也非常清晰，上面的例子只对category列进行分区。
在MySQL 5.1中使用分区另一个让人头痛的问题是date类型（即日期列），你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，如：
<ol class="dp-sql"> <li class="alt"><span><span>/* 在MySQL 5.1中*/ </span></span></li> <li> <span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> t2 </span> </li> <li class="alt"><span>( </span></li> <li> <span> dt </span><span class="keyword"><strong><font color="#006699">DATE</font></strong></span><span> </span> </li> <li class="alt"><span>) </span></li> <li> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE (TO_DAYS(dt)) </span> </li> <li class="alt"><span>( </span></li> <li> <span> PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (TO_DAYS(</span><span class="string"><font color="#0000ff">'2007-01-01'</font></span><span>)), </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (TO_DAYS(</span><span class="string"><font color="#0000ff">'2008-01-01'</font></span><span>)), </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (TO_DAYS(</span><span class="string"><font color="#0000ff">'2009-01-01'</font></span><span>)), </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE)); </span> </li> <li><span> </span></li> <li class="alt"> <span>SHOW </span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> t2 \G </span> </li> <li><span>*************************** 1. row *************************** </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">Table</font></strong></span><span>: t2 </span> </li> <li> <span class="keyword"><strong><font color="#006699">Create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">Table</font></strong></span><span>: </span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> `t2` ( </span> </li> <li class="alt"> <span> `dt` </span><span class="keyword"><strong><font color="#006699">date</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">DEFAULT</font></strong></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span>) ENGINE=MyISAM </span><span class="keyword"><strong><font color="#006699">DEFAULT</font></strong></span><span> CHARSET=latin1 </span> </li> <li class="alt"> <span>/*!50100 PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE (TO_DAYS(dt)) </span> </li> <li> <span>(PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (733042) ENGINE = MyISAM, </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (733407) ENGINE = MyISAM, </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (733773) ENGINE = MyISAM, </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN MAXVALUE ENGINE = MyISAM) */ </span> </li> </ol>
로그인 후 복사
看上去非常糟糕，当然也有变通办法，但麻烦确实不少。使用YEAR或TO_DAYS定义一个分区的确让人费解，查询时不得不使用赤裸列，因为加了函数的查询不能识别分区。
但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单。
<ol class="dp-sql"> <li class="alt"><span><span>/*在MySQL 5.5中*/ </span></span></li> <li> <span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> t2 </span> </li> <li class="alt"><span>( </span></li> <li> <span> dt </span><span class="keyword"><strong><font color="#006699">DATE</font></strong></span><span> </span> </li> <li class="alt"><span>) </span></li> <li> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE COLUMNS (dt) </span> </li> <li class="alt"><span>( </span></li> <li> <span> PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'2007-01-01'</font></span><span>), </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'2008-01-01'</font></span><span>), </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'2009-01-01'</font></span><span>), </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE)); </span> </li> <li><span> </span></li> <li class="alt"> <span>SHOW </span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> t2 \G </span> </li> <li><span>*************************** 1. row *************************** </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">Table</font></strong></span><span>: t2 </span> </li> <li> <span class="keyword"><strong><font color="#006699">Create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">Table</font></strong></span><span>: </span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> `t2` ( </span> </li> <li class="alt"> <span> `dt` </span><span class="keyword"><strong><font color="#006699">date</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">DEFAULT</font></strong></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span>) ENGINE=MyISAM </span><span class="keyword"><strong><font color="#006699">DEFAULT</font></strong></span><span> CHARSET=latin1 </span> </li> <li class="alt"> <span>/*!50500 PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE COLUMNS(dt) </span> </li> <li> <span>(PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'2007-01-01'</font></span><span>) ENGINE = MyISAM, </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'2008-01-01'</font></span><span>) ENGINE = MyISAM, </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'2009-01-01'</font></span><span>) ENGINE = MyISAM, </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE) ENGINE = MyISAM) */ </span> </li> </ol>
로그인 후 복사
在这里，通过函数定义和通过列查询之间没有冲突，因为是按列定义的，我们在定义中插入的值是保留的。
多列分区
COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区，你可能在官方文档中已经看到了一些例子，如：
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> p1 ( </span></span></li> <li> <span> a </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>, </span> </li> <li class="alt"> <span> b </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>, </span> </li> <li> <span> c </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span> </span> </li> <li class="alt"><span>) </span></li> <li> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE COLUMNS (a,b) </span> </li> <li class="alt"><span>( </span></li> <li> <span> PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (10,20), </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (20,30), </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (30,40), </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (40,MAXVALUE), </span> </li> <li> <span> PARTITION p05 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE,MAXVALUE) </span> </li> <li class="alt"><span>); </span></li> <li><span> </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> p2 ( </span> </li> <li> <span> a </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>, </span> </li> <li class="alt"> <span> b </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>, </span> </li> <li> <span> c </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span> </span> </li> <li class="alt"><span>) </span></li> <li> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE COLUMNS (a,b) </span> </li> <li class="alt"><span>( </span></li> <li> <span> PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (10,10), </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (10,20), </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (10,30), </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (10,MAXVALUE), </span> </li> <li> <span> PARTITION p05 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE,MAXVALUE) </span> </li> <li class="alt"><span>) </span></li> </ol>
로그인 후 복사
同样还有PARTITION BY RANGE COLUMNS (a,b,c)等其它例子。由于我很长时间都在使用MySQL 5.1的分区，我对多列分区的含义不太了解，LESS THAN (10,10)是什么意思？如果下一个分区是LESS THAN (10,20)会发生什么？相反，如果是(20,30)又会如何？
所有这些问题都需要一个答案，在回答之前，他们需要更好地理解我们在做什么。
开始时可能有些混乱，当所有分区有一个不同范围的值时，实际上，它只是在表的一个列上进行了分区，但事实并非如此，在下面的例子中：
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> p1_single ( </span></span></li> <li> <span> a </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>, </span> </li> <li class="alt"> <span> b </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>, </span> </li> <li> <span> c </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span> </span> </li> <li class="alt"><span>) </span></li> <li> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE COLUMNS (a) </span> </li> <li class="alt"><span>( </span></li> <li> <span> PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (10), </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (20), </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (30), </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (40), </span> </li> <li> <span> PARTITION p05 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE) </span> </li> <li class="alt"><span>); </span></li> </ol>
로그인 후 복사
它和前面的表p1不一样，如果你在表p1中插入（10,1,1），它将会进入第一个分区，相反，在表p1_single中，它将会进入第二个分区，其原因是(10,1)小于(10,10)，如果你仅仅关注第一个值，你还没有意识到你在比较一个元组，而不是一个单一的值。
现在我们来分析一下最难懂的地方，当你需要确定某一行应该放在哪里时会发生什么？你是如何确定类似(10,9)
<ol class="dp-sql"> <li class="alt"><span><span>a=10 </span></span></li> <li><span>b=9 </span></li> <li class="alt"><span>(a,b) </span></li> <li><span> </span></li> <li class="alt"> <span># evaluates </span><span class="keyword"><strong><font color="#006699">to</font></strong></span><span>: </span> </li> <li><span> </span></li> <li class="alt"><span>(a </span></li> <li> <span class="op"><font color="#808080">OR</font></span><span> </span> </li> <li class="alt"> <span>((a = 10) </span><span class="op"><font color="#808080">AND</font></span><span> ( b </span> </li> <li><span> </span></li> <li class="alt"> <span># which translates </span><span class="keyword"><strong><font color="#006699">to</font></strong></span><span>: </span> </li> <li><span> </span></li> <li class="alt"><span>(10 </span></li> <li> <span class="op"><font color="#808080">OR</font></span><span> </span> </li> <li class="alt"> <span>((10 = 10) </span><span class="op"><font color="#808080">AND</font></span><span> ( 9 </span> </li> </ol>
로그인 후 복사
如果有三列，表达式会更长，但不会更复杂。你首先在第一个项目上测试小于运算，如果有两个或更多的分区与之匹配，接着就测试第二个项目，如果不止一个候选分区，那还需要测试第三个项目。
下图所显示的内容表示将遍历三条记录插入到使用以下代码定义的分区中：
(10,10),
(10,20),
(10,30),
(10, MAXVALUE)
图 2 元组比较。当第一个值小于分区定义的第一个范围时，那么该行将属于这里了。
图 3 元组比较。当第一个值等于分区定义的第一个范围，我们需要比较第二个项目，如果它小于第二个范围，那么该行将属于这里了。
图 4 元组比较。当第一个值和第二个值等于他们对应的范围时，如果元组不小于定义的范围，那么它就不属于这里，继续下一步。
图 5 元组比较。在下一个范围时，第一个项目是等于，第二个项目是小于，因此元组更小，那么该行就属于这里了。
在这些图的帮助下，我们对插入一条记录到多列分区表的步骤有了更深的了解，这些都是理论上的，为了帮助你更好地掌握新功能，我们再来看一个更高级一点的例子，对于比较务实的读者更有意义，下面是表的定义脚本：
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> employees ( </span></span></li> <li> <span> emp_no </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>(11) </span><span class="op"><font color="#808080">NOT</font></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span>, </span> </li> <li class="alt"> <span> birth_date </span><span class="keyword"><strong><font color="#006699">date</font></strong></span><span> </span><span class="op"><font color="#808080">NOT</font></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span>, </span> </li> <li> <span> first_name </span><span class="keyword"><strong><font color="#006699">varchar</font></strong></span><span>(14) </span><span class="op"><font color="#808080">NOT</font></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span>, </span> </li> <li class="alt"> <span> last_name </span><span class="keyword"><strong><font color="#006699">varchar</font></strong></span><span>(16) </span><span class="op"><font color="#808080">NOT</font></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span>, </span> </li> <li> <span> gender </span><span class="keyword"><strong><font color="#006699">char</font></strong></span><span>(1) </span><span class="keyword"><strong><font color="#006699">DEFAULT</font></strong></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span>, </span> </li> <li class="alt"> <span> hire_date </span><span class="keyword"><strong><font color="#006699">date</font></strong></span><span> </span><span class="op"><font color="#808080">NOT</font></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li><span>) ENGINE=MyISAM </span></li> <li class="alt"> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE COLUMNS(gender,hire_date) </span> </li> <li> <span>(PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'F'</font></span><span>,</span><span class="string"><font color="#0000ff">'1990-01-01'</font></span><span>) , </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'F'</font></span><span>,</span><span class="string"><font color="#0000ff">'2000-01-01'</font></span><span>) , </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'F'</font></span><span>,MAXVALUE) , </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'M'</font></span><span>,</span><span class="string"><font color="#0000ff">'1990-01-01'</font></span><span>) , </span> </li> <li> <span> PARTITION p05 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'M'</font></span><span>,</span><span class="string"><font color="#0000ff">'2000-01-01'</font></span><span>) , </span> </li> <li class="alt"> <span> PARTITION p06 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (</span><span class="string"><font color="#0000ff">'M'</font></span><span>,MAXVALUE) , </span> </li> <li> <span> PARTITION p07 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE,MAXVALUE) </span> </li> </ol>
로그인 후 복사
和上面的例子不同，这个例子更好理解，第一个分区用来存储雇佣于1990年以前的女职员，第二个分区存储股用于1990-2000年之间的女职员，第三个分区存储所有剩下的女职员。对于分区p04到p06，我们策略是一样的，只不过存储的是男职员。最后一个分区是控制情况。
看完后你可能要问，我怎么知道某一行存储在那个分区中的？有两个办法，第一个办法是使用与分区定义相同的条件作为查询条件进行查询。
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> </span></span></li> <li> <span class="func"><font color="#ff1493">CASE</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">WHEN</font></strong></span><span> gender = </span><span class="string"><font color="#0000ff">'F'</font></span><span> </span><span class="op"><font color="#808080">AND</font></span><span> hire_date <span class="string"><font color="#0000ff">'1990-01-01'</font></span><span> </span></span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> </span><span class="string"><font color="#0000ff">'p1'</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">WHEN</font></strong></span><span> gender = </span><span class="string"><font color="#0000ff">'F'</font></span><span> </span><span class="op"><font color="#808080">AND</font></span><span> hire_date <span class="string"><font color="#0000ff">'2000-01-01'</font></span><span> </span></span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> </span><span class="string"><font color="#0000ff">'p2'</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">WHEN</font></strong></span><span> gender = </span><span class="string"><font color="#0000ff">'F'</font></span><span> </span><span class="op"><font color="#808080">AND</font></span><span> hire_date <span class="string"><font color="#0000ff">'2999-01-01'</font></span><span> </span></span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> </span><span class="string"><font color="#0000ff">'p3'</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">WHEN</font></strong></span><span> gender = </span><span class="string"><font color="#0000ff">'M'</font></span><span> </span><span class="op"><font color="#808080">AND</font></span><span> hire_date <span class="string"><font color="#0000ff">'1990-01-01'</font></span><span> </span></span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> </span><span class="string"><font color="#0000ff">'p4'</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">WHEN</font></strong></span><span> gender = </span><span class="string"><font color="#0000ff">'M'</font></span><span> </span><span class="op"><font color="#808080">AND</font></span><span> hire_date <span class="string"><font color="#0000ff">'2000-01-01'</font></span><span> </span></span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> </span><span class="string"><font color="#0000ff">'p5'</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">WHEN</font></strong></span><span> gender = </span><span class="string"><font color="#0000ff">'M'</font></span><span> </span><span class="op"><font color="#808080">AND</font></span><span> hire_date <span class="string"><font color="#0000ff">'2999-01-01'</font></span><span> </span></span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> </span><span class="string"><font color="#0000ff">'p6'</font></span><span> </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">ELSE</font></strong></span><span> </span> </li> <li> <span> </span><span class="string"><font color="#0000ff">'p7'</font></span><span> </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">END</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">as</font></strong></span><span> p, </span> </li> <li> <span class="func"><font color="#ff1493">COUNT</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">AS</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">rows</font></strong></span><span> </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> employees </span> </li> <li> <span class="keyword"><strong><font color="#006699">GROUP</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> p; </span> </li> <li class="alt"><span> </span></li> <li> <span>+</span><span class="comment"><font color="#008200">------+-------+ </font></span><span> </span> </li> <li class="alt"> <span>| p | </span><span class="keyword"><strong><font color="#006699">rows</font></strong></span><span> | </span> </li> <li> <span>+</span><span class="comment"><font color="#008200">------+-------+ </font></span><span> </span> </li> <li class="alt"><span>| p1 | 66212 | </span></li> <li><span>| p2 | 53832 | </span></li> <li class="alt"><span>| p3 | 7 | </span></li> <li><span>| p4 | 98585 | </span></li> <li class="alt"><span>| p5 | 81382 | </span></li> <li><span>| p6 | 6 | </span></li> <li class="alt"> <span>+</span><span class="comment"><font color="#008200">------+-------+</font></span><span> </span> </li> </ol>
로그인 후 복사
如果表是MyISAM或ARCHIVE，你可以信任由INFORMATION_SCHEMA提供的统计信息。
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> </span></span></li> <li><span> partition_name part, </span></li> <li class="alt"><span> partition_expression expr, </span></li> <li><span> partition_description descr, </span></li> <li class="alt"><span> table_rows </span></li> <li> <span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> </span> </li> <li class="alt"><span> INFORMATION_SCHEMA.partitions </span></li> <li> <span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> </span> </li> <li class="alt"> <span> TABLE_SCHEMA = </span><span class="keyword"><strong><font color="#006699">schema</font></strong></span><span>() </span> </li> <li> <span> </span><span class="op"><font color="#808080">AND</font></span><span> TABLE_NAME=</span><span class="string"><font color="#0000ff">'employees'</font></span><span>; </span> </li> <li class="alt"> <span>+</span><span class="comment"><font color="#008200">------+------------------+-------------------+------------+ </font></span><span> </span> </li> <li><span>| part | expr | descr | table_rows | </span></li> <li class="alt"> <span>+</span><span class="comment"><font color="#008200">------+------------------+-------------------+------------+ </font></span><span> </span> </li> <li> <span>| p01 | gender,hire_date | </span><span class="string"><font color="#0000ff">'F'</font></span><span>,</span><span class="string"><font color="#0000ff">'1990-01-01'</font></span><span> | 66212 | </span> </li> <li class="alt"> <span>| p02 | gender,hire_date | </span><span class="string"><font color="#0000ff">'F'</font></span><span>,</span><span class="string"><font color="#0000ff">'2000-01-01'</font></span><span> | 53832 | </span> </li> <li> <span>| p03 | gender,hire_date | </span><span class="string"><font color="#0000ff">'F'</font></span><span>,MAXVALUE | 7 | </span> </li> <li class="alt"> <span>| p04 | gender,hire_date | </span><span class="string"><font color="#0000ff">'M'</font></span><span>,</span><span class="string"><font color="#0000ff">'1990-01-01'</font></span><span> | 98585 | </span> </li> <li> <span>| p05 | gender,hire_date | </span><span class="string"><font color="#0000ff">'M'</font></span><span>,</span><span class="string"><font color="#0000ff">'2000-01-01'</font></span><span> | 81382 | </span> </li> <li class="alt"> <span>| p06 | gender,hire_date | </span><span class="string"><font color="#0000ff">'M'</font></span><span>,MAXVALUE | 6 | </span> </li> <li><span>| p07 | gender,hire_date | MAXVALUE,MAXVALUE | 0 | </span></li> <li class="alt"> <span>+</span><span class="comment"><font color="#008200">------+------------------+-------------------+------------+</font></span><span> </span> </li> </ol>
로그인 후 복사
如果存储引擎是InnoDB，上面的值就是一个近似值，如果你需要确切的值，那你就不能信任它们。
另一个问题是它的性能，这些增强触发了分区修整吗？答案毫不含糊，是的。与MySQL 5.1有所不同，在5.1中日期分区只能与两个函数工作，在MySQL 5.5中，任何使用了COLUMNS关键字定义的分区都可以使用分区修整，下面还是测试一下吧。
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="func"><font color="#ff1493">count</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> employees </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> gender=</span><span class="string"><font color="#0000ff">'F'</font></span><span> </span><span class="op"><font color="#808080">and</font></span><span> hire_date <span class="string"><font color="#0000ff">'1990-01-01'</font></span><span>; </span></span></span></li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"> <span>| </span><span class="func"><font color="#ff1493">count</font></span><span>(*) | </span> </li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"><span>| 66212 | </span></li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"> <span>1 row </span><span class="op"><font color="#808080">in</font></span><span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> (0.05 sec) </span> </li> <li><span> </span></li> <li class="alt"> <span>explain partitions </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="func"><font color="#ff1493">count</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> employees </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> gender=</span><span class="string"><font color="#0000ff">'F'</font></span><span> </span><span class="op"><font color="#808080">and</font></span><span> hire_date <span class="string"><font color="#0000ff">'1990-01-01'</font></span><span>\G </span></span> </li> <li><span>*************************** 1. row *************************** </span></li> <li class="alt"><span> id: 1 </span></li> <li><span> select_type: SIMPLE </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span>: employees </span> </li> <li><span> partitions: p01 </span></li> <li class="alt"> <span> type: </span><span class="op"><font color="#808080">ALL</font></span><span> </span> </li> <li> <span>possible_keys: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">key</font></strong></span><span>: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span> key_len: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li class="alt"> <span> ref: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">rows</font></strong></span><span>: 300024 </span> </li> <li class="alt"> <span> Extra: Using </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> </span> </li> </ol>
로그인 후 복사
使用定义第一个分区的条件，我们获得了一个非常优化的查询，不仅如此，部分条件也将从分区修整中受益。
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="func"><font color="#ff1493">count</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> employees </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> gender=</span><span class="string"><font color="#0000ff">'F'</font></span><span>; </span></span></li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"> <span>| </span><span class="func"><font color="#ff1493">count</font></span><span>(*) | </span> </li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"><span>| 120051 | </span></li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"> <span>1 row </span><span class="op"><font color="#808080">in</font></span><span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> (0.12 sec) </span> </li> <li><span> </span></li> <li class="alt"> <span>explain partitions </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="func"><font color="#ff1493">count</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> employees </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> gender=</span><span class="string"><font color="#0000ff">'F'</font></span><span>\G </span> </li> <li><span>*************************** 1. row *************************** </span></li> <li class="alt"><span> id: 1 </span></li> <li><span> select_type: SIMPLE </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span>: employees </span> </li> <li><span> partitions: p01,p02,p03,p04 </span></li> <li class="alt"> <span> type: </span><span class="op"><font color="#808080">ALL</font></span><span> </span> </li> <li> <span>possible_keys: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">key</font></strong></span><span>: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span> key_len: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li class="alt"> <span> ref: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">rows</font></strong></span><span>: 300024 </span> </li> <li class="alt"> <span> Extra: Using </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> </span> </li> </ol>
로그인 후 복사
它和复合索引的算法一样，如果你的条件指的是索引最左边的部分，MySQL将会使用它。与此类似，如果你的条件指的是分区定义最左边的部分，MySQL将会尽可能修整。它和复合索引一起出现，如果你只使用最右边的条件，分区修整不会工作。
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="func"><font color="#ff1493">count</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> employees </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> hire_date <span class="string"><font color="#0000ff">'1990-01-01'</font></span><span>; </span></span></span></li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"> <span>| </span><span class="func"><font color="#ff1493">count</font></span><span>(*) | </span> </li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"><span>| 164797 | </span></li> <li> <span>+</span><span class="comment"><font color="#008200">----------+ </font></span><span> </span> </li> <li class="alt"> <span>1 row </span><span class="op"><font color="#808080">in</font></span><span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> (0.18 sec) </span> </li> <li><span> </span></li> <li class="alt"> <span>explain partitions </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="func"><font color="#ff1493">count</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> employees </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> hire_date <span class="string"><font color="#0000ff">'1990-01-01'</font></span><span>\G </span></span> </li> <li><span>*************************** 1. row *************************** </span></li> <li class="alt"><span> id: 1 </span></li> <li><span> select_type: SIMPLE </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span>: employees </span> </li> <li><span> partitions: p01,p02,p03,p04,p05,p06,p07 </span></li> <li class="alt"> <span> type: </span><span class="op"><font color="#808080">ALL</font></span><span> </span> </li> <li> <span>possible_keys: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">key</font></strong></span><span>: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span> key_len: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li class="alt"> <span> ref: </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">rows</font></strong></span><span>: 300024 </span> </li> <li class="alt"> <span> Extra: Using </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> </span> </li> </ol>
로그인 후 복사
如果不用分区定义的第一部分，使用分区定义的第二部分，那么将会发生全表扫描，在设计分区和编写查询时要紧记这一条。
可用性增强：truncate分区
分区最吸引人的一个功能是瞬间移除大量记录的能力，DBA都喜欢将历史记录存储到按日期分区的分区表中，这样可以定期删除过时的历史数据，这种方法相当管用，假设第一个分区存储的是最旧的历史记录，那么你可以直接删除第一个分区，然后再在末尾建立一个新分区保存最近的历史记录，这样循环下去就可以实现历史记录的快速清除。
但当你需要移除分区中的部分数据时，事情就不是那么简单了，删除分区没有问题，但如果是清空分区，就很头痛了，要移除分区中的所有数据，但需要保留分区本身，你可以：
使用DELETE语句，但我们知道DELETE语句的性能都很差。
使用DROP PARTITION语句，紧跟着一个EORGANIZE PARTITIONS语句重新创建分区，但这样做比前一个方法的成本要高出许多。
MySQL 5.5引入了TRUNCATE PARTITION，它和DROP PARTITION语句有些类似，但它保留了分区本身，也就是说分区还可以重复利用。TRUNCATE PARTITION应该是DBA工具箱中的必备工具。
更多微调功能：TO_SECONDS
分区增强包有一个新的函数处理DATE和DATETIME列，使用TO_SECONDS函数，你可以将日期/时间列转换成自0年以来的秒数，如果你想使用小于1天的间隔进行分区，那么这个函数就可以帮到你。
TO_SECONDS会触发分区修整，与TO_DAYS不同，它可以反过来使用，就是FROM_DAYS，对于TO_SECONDS就没有这样的反向函数了，但要自己动手DIY一个也不是难事。
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">drop</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">function</font></strong></span><span> if exists from_seconds; </span></span></li> <li><span> </span></li> <li class="alt"><span>delimiter // </span></li> <li> <span class="keyword"><strong><font color="#006699">create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">function</font></strong></span><span> from_seconds (secs </span><span class="keyword"><strong><font color="#006699">bigint</font></strong></span><span>) </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">returns</font></strong></span><span> DATETIME </span> </li> <li> <span class="keyword"><strong><font color="#006699">begin</font></strong></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> days </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>; </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> secs_per_day </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>; </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> ZH </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>; </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> ZM </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>; </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> ZS </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>; </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> secs_per_day = 60 * 60 * 24; </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> days = floor(secs / secs_per_day); </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> secs = secs - (secs_per_day * days); </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> ZH = floor(secs / 3600); </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> ZM = floor(secs / 60) - ZH * 60; </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> ZS = secs - (ZH * 3600 + ZM * 60); </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">return</font></strong></span><span> </span><span class="func"><font color="#ff1493">CAST</font></span><span>(CONCAT(FROM_DAYS(days), </span><span class="string"><font color="#0000ff">' '</font></span><span>, ZH, </span><span class="string"><font color="#0000ff">':'</font></span><span>, ZM, </span><span class="string"><font color="#0000ff">':'</font></span><span>, ZS) </span><span class="keyword"><strong><font color="#006699">as</font></strong></span><span> DATETIME); </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">end</font></strong></span><span> // </span> </li> <li><span> </span></li> <li class="alt"><span>delimiter ; </span></li> </ol>
로그인 후 복사
有了这些新武器，我们可以有把握地创建一个小于1天的临时分区，如：
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> t2 ( </span></span></li> <li><span> dt datetime </span></li> <li class="alt"><span>) </span></li> <li> <span>PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE (to_seconds(dt)) </span> </li> <li class="alt"><span>( </span></li> <li> <span> PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (to_seconds(</span><span class="string"><font color="#0000ff">'2009-11-30 08:00:00'</font></span><span>)) , </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (to_seconds(</span><span class="string"><font color="#0000ff">'2009-11-30 16:00:00'</font></span><span>)) , </span> </li> <li> <span> PARTITION p03 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (to_seconds(</span><span class="string"><font color="#0000ff">'2009-12-01 00:00:00'</font></span><span>)) , </span> </li> <li class="alt"> <span> PARTITION p04 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (to_seconds(</span><span class="string"><font color="#0000ff">'2009-12-01 08:00:00'</font></span><span>)) , </span> </li> <li> <span> PARTITION p05 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (to_seconds(</span><span class="string"><font color="#0000ff">'2009-12-01 16:00:00'</font></span><span>)) , </span> </li> <li class="alt"> <span> PARTITION p06 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (MAXVALUE) </span> </li> <li><span>); </span></li> <li class="alt"> <span>show </span><span class="keyword"><strong><font color="#006699">create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span> t2\G </span> </li> <li><span>*************************** 1. row *************************** </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">Table</font></strong></span><span>: t2 </span> </li> <li> <span class="keyword"><strong><font color="#006699">Create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">Table</font></strong></span><span>: </span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> `t2` ( </span> </li> <li class="alt"> <span> `dt` datetime </span><span class="keyword"><strong><font color="#006699">DEFAULT</font></strong></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span> </span> </li> <li> <span>) ENGINE=MyISAM </span><span class="keyword"><strong><font color="#006699">DEFAULT</font></strong></span><span> CHARSET=latin1 </span> </li> <li class="alt"> <span>/*!50500 PARTITION </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> RANGE (to_seconds(dt)) </span> </li> <li> <span>(PARTITION p01 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (63426787200) ENGINE = MyISAM, </span> </li> <li class="alt"> <span> PARTITION p02 </span><span class="keyword"><strong><font color="#006699">VALUES</font></strong></span><span> LESS THAN (63426816000) ENGINE&</span> </li> </ol>
로그인 후 복사
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
AI Hentai Generator
AI Hentai를 무료로 생성하십시오.
인기 기사
R.E.P.O. 에너지 결정과 그들이하는 일 (노란색 크리스탈)
2 몇 주 전
By 尊渡假赌尊渡假赌尊渡假赌
Repo : 팀원을 부활시키는 방법
1 몇 달 전
By 尊渡假赌尊渡假赌尊渡假赌
헬로 키티 아일랜드 어드벤처 : 거대한 씨앗을 얻는 방법
4 몇 주 전
By 尊渡假赌尊渡假赌尊渡假赌
스플릿 소설을이기는 데 얼마나 걸립니까?
3 몇 주 전
By DDD
R.E.P.O. 파일 저장 위치 : 어디에 있고 그것을 보호하는 방법은 무엇입니까?
3 몇 주 전
By DDD
뜨거운 도구
메모장++7.3.1
사용하기 쉬운 무료 코드 편집기
SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.
스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경
드림위버 CS6
시각적 웹 개발 도구
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
뜨거운 주제
Gmail 이메일의 로그인 입구는 어디에 있나요?
7340
9
7340
9
자바 튜토리얼
1627
14
1627
14
Cakephp 튜토리얼
1352
46
1352
46
라라벨 튜토리얼
1265
25
1265
25
PHP 튜토리얼
1210
29
1210
29
Doubao 앱에는 어떤 기능이 있나요?
Mar 01, 2024 pm 10:04 PM
Doubao 앱에는 많은 AI 생성 기능이 있을 예정인데 Doubao 앱에는 어떤 기능이 있나요? 사용자는 이 소프트웨어를 사용하여 그림을 만들고, AI와 채팅하고, 사용자를 위한 기사를 생성하고, 모든 사람이 노래를 검색하도록 도울 수 있습니다. Doubao 앱의 이 기능 소개는 구체적인 작동 방법을 알려드릴 수 있습니다. 구체적인 내용은 아래와 같으니 한번 살펴보세요! Doubao 앱에는 어떤 기능이 있나요? 답변: 그림 그리기, 채팅, 기사 쓰기, 노래 찾기 등이 가능합니다. 기능 소개: 1. 질문 쿼리: AI를 사용하여 질문에 대한 답변을 더 빠르게 찾을 수 있으며, 모든 종류의 질문을 할 수 있습니다. 2. 이미지 생성: AI를 사용하면 모든 사람에게 일반적인 요구 사항만 알려주면 됩니다. 3. AI 채팅: 사용자와 채팅할 수 있는 AI를 생성할 수 있으며,
vivox100s와 x100의 차이점: 성능 비교 및 기능 분석
Mar 23, 2024 pm 10:27 PM
vivox100s와 x100 휴대폰은 모두 in vivo 휴대폰 제품군의 대표적인 모델입니다. 두 휴대폰은 각각 서로 다른 시대의 vivo 첨단 기술 수준을 대표하므로 디자인, 성능, 기능 면에서 일정한 차이가 있습니다. 이번 글에서는 소비자들이 자신에게 꼭 맞는 휴대폰을 선택할 수 있도록 두 휴대폰을 성능비교와 기능분석 측면에서 자세히 비교해보겠습니다. 먼저 vivox100s와 x100의 성능 비교를 살펴보겠습니다. vivox100s에는 최신 기술이 탑재되어 있습니다.
디스커스란 무엇인가요? Discuz의 정의 및 기능 소개
Mar 03, 2024 am 10:33 AM
"Discovering Discuz: 정의, 기능 및 코드 예제" 인터넷의 급속한 발전과 함께 커뮤니티 포럼은 사람들이 정보를 얻고 의견을 교환하는 중요한 플랫폼이 되었습니다. 많은 커뮤니티 포럼 시스템 중에서 중국의 잘 알려진 오픈 소스 포럼 소프트웨어인 Discuz는 대다수의 웹 사이트 개발자 및 관리자가 선호합니다. 그렇다면 Discuz는 무엇입니까? 어떤 기능이 있으며 웹사이트에 어떻게 도움이 됩니까? 이 기사에서는 Discuz를 자세히 소개하고 독자가 이에 대해 더 자세히 알아볼 수 있도록 구체적인 코드 예제를 첨부합니다.
셀프미디어란 정확히 무엇인가? 주요 특징과 기능은 무엇입니까?
Mar 21, 2024 pm 08:21 PM
인터넷의 급속한 발전으로 셀프미디어라는 개념은 사람들의 마음속에 깊이 뿌리내렸습니다. 그렇다면 셀프미디어란 정확히 무엇인가? 주요 특징과 기능은 무엇입니까? 다음에는 이러한 문제를 하나씩 살펴보겠습니다. 1. 셀프미디어란 정확히 무엇인가? We-media는 이름에서 알 수 있듯이 당신이 미디어라는 뜻입니다. 개인이나 팀이 인터넷 플랫폼을 통해 콘텐츠를 독립적으로 생성, 편집, 출판 및 전파할 수 있는 정보 매체를 말합니다. 신문, 텔레비전, 라디오 등과 같은 전통적인 미디어와 달리 셀프 미디어는 더욱 상호작용적이고 개인화되어 있어 모든 사람이 정보의 생산자이자 전파자가 될 수 있습니다. 2. 셀프미디어의 주요 특징과 기능은 무엇입니까? 1. 낮은 문턱: 셀프미디어의 등장으로 미디어 산업에 진출하기 위한 문턱이 낮아졌습니다. 더 이상 번거로운 장비와 전문팀이 필요하지 않습니다.
PHP 팁: 이전 페이지로 돌아가는 기능을 빠르게 구현
Mar 09, 2024 am 08:21 AM
PHP 팁: 이전 페이지로 돌아가는 기능을 빠르게 구현하세요. 웹 개발을 하다 보면 이전 페이지로 돌아가는 기능을 구현해야 하는 경우가 종종 있습니다. 이러한 작업은 사용자 경험을 향상시키고 사용자가 웹 페이지 간을 더 쉽게 탐색할 수 있게 해줍니다. PHP에서는 몇 가지 간단한 코드를 통해 이 기능을 구현할 수 있습니다. 이번 글에서는 이전 페이지로 돌아가는 기능을 빠르게 구현하는 방법을 소개하고 구체적인 PHP 코드 예제를 제공하겠습니다. PHP에서는 $_SERVER['HTTP_REFERER']를 사용하여 이전 페이지의 URL을 가져올 수 있습니다.
Xiaohongshu 계정 관리 소프트웨어의 기능은 무엇입니까? Xiaohongshu 계정을 운영하는 방법은 무엇입니까?
Mar 21, 2024 pm 04:16 PM
Xiaohongshu가 젊은이들 사이에서 인기를 끌면서 점점 더 많은 사람들이 이 플랫폼을 사용하여 자신의 경험과 인생 통찰력의 다양한 측면을 공유하기 시작했습니다. 여러 Xiaohongshu 계정을 효과적으로 관리하는 방법이 중요한 문제가 되었습니다. 이 글에서는 Xiaohongshu 계정 관리 소프트웨어의 일부 기능에 대해 논의하고 Xiaohongshu 계정을 더 잘 관리하는 방법을 살펴보겠습니다. 소셜 미디어가 성장함에 따라 많은 사람들이 여러 소셜 계정을 관리해야 한다는 사실을 깨닫게 되었습니다. 이는 Xiaohongshu 사용자에게도 어려운 과제입니다. 일부 Xiaohongshu 계정 관리 소프트웨어는 자동 콘텐츠 게시, 예약 게시, 데이터 분석 및 기타 기능을 포함하여 사용자가 여러 계정을 보다 쉽게 관리할 수 있도록 도와줍니다. 이러한 도구를 통해 사용자는 자신의 계정을 보다 효율적으로 관리하고 계정 노출과 관심을 높일 수 있습니다. 또한 Xiaohongshu 계정 관리 소프트웨어에는
Linux에서 GDM의 기능과 기능에 대한 자세한 설명
Mar 01, 2024 pm 04:18 PM
Linux에서 GDM의 기능 및 기능에 대한 자세한 설명 Linux 운영 체제에서 GDM(GNOMEDisplayManager)은 사용자가 시스템에 로그인 및 로그아웃할 수 있는 인터페이스를 제공하는 그래픽 로그인 관리자입니다. GDM은 일반적으로 GNOME 데스크탑 환경의 일부이지만 다른 데스크탑 환경에서도 사용할 수 있습니다. GDM의 역할은 로그인 인터페이스를 제공하는 것뿐만 아니라 사용자 세션 관리, 화면 보호기, 자동 로그인 및 기타 기능도 포함합니다. GDM의 기능은 주로 다음과 같은 측면을 포함합니다:
Linux Opt 파티션 설정 방법에 대한 자세한 설명
Mar 20, 2024 am 11:30 AM
Linux Opt 파티션 및 코드 예제를 설정하는 방법 Linux 시스템에서 Opt 파티션은 일반적으로 선택적 소프트웨어 패키지 및 응용 프로그램 데이터를 저장하는 데 사용됩니다. Opt 파티션을 올바르게 설정하면 시스템 리소스를 효과적으로 관리하고 디스크 공간 부족과 같은 문제를 피할 수 있습니다. 이 기사에서는 LinuxOpt 파티션을 설정하는 방법을 자세히 설명하고 특정 코드 예제를 제공합니다. 1. 파티션 공간 크기 결정 먼저 Opt 파티션에 필요한 공간 크기를 결정해야 합니다. 일반적으로 Opt 파티션의 크기를 전체 시스템 공간의 5%-1로 설정하는 것이 좋습니다.
