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
使用JavaScript实现自动登录功能Jun 15, 2023 pm 11:52 PM随着互联网的发展，人们越来越依赖网络，大部分时间都在使用各种各样的网站和应用程序，这也使得我们需要记住很多账号和密码。为了方便用户的使用，很多网站提供了自动登录功能，让用户免除频繁输入账号和密码的烦恼。本文将介绍使用JavaScript实现自动登录功能的方法。一、登录流程分析在开始实现自动登录功能之前，我们需要了解整个登录流程。一般情况下，一个网站的登录流程
如何使用PHP实现天气预报功能Jun 27, 2023 pm 05:54 PMPHP作为一款流行的后端编程语言，在Web开发领域广受欢迎。天气预报功能是一种常见的Web应用场景，基于PHP实现天气预报功能相对简单易懂。本文将介绍如何使用PHP实现天气预报功能。一、获取天气数据API要实现天气预报功能，首先需要获取天气数据。我们可以使用第三方天气API来获取实时、准确的天气数据。目前，国内主流的天气API供应商包括免费的“心知天气”和收
如何使用 Windows Copilot 与剪贴板一起展开、解释、总结或修改复制的文本Jul 29, 2023 am 08:41 AM在Copilot目前在Windows11上拥有的少数功能中，也许最有用的功能是允许您交互和调整已复制到剪贴板的文本的功能。这使得将Copilot用作文本编辑和摘要工具变得容易，您可以直接从桌面使用。以下是您需要了解的有关使用Copilot在Windows上解释、修订、扩展和汇总文本的所有信息。如何在WindowsCopilot中使用复制的文本Copilot的预览版让我们第一次很好地了解了Windows对原生AI支持的集成。修改或扩展从其他地方复制的文本的早期功能之一可以通过内容创建、摘要、修订和
未来功能抢先用 Safari 技术预览 173 版本释出Jul 02, 2023 pm 01:37 PMApple今日释出了Safari技术预览173版本，涵盖部分可能于Safari17推出的功能。该版本适用于macOSSonoma测试版以及macOSVentura系统，有兴趣的用户可于官方网页下载。Safari技术预览173版于设定中新增了功能标志区块，取代原先开发菜单的实验功能。该区块可让开发者快速地搜索特定功能，并以不同形式将「稳定」、「可供测试」、「预览」或「开发人员」等状态标示出来。重新设计的开发菜单可以帮助创作者更容易找到关键工具，以便建立网页、网页应用程序、其他应用程序中的网页内容、
Google Colab将很快使用Codey进行AI编码Jun 09, 2023 am 10:43 AMGoogleColab是一个自2017年以来一直在促进Python编程的平台，它将利用Google的高级代码模型Codey引入AI编码功能。Codey基于PaLM2模型构建，对来自外部来源的大型高质量代码数据集进行了精心微调，以提高其在编码任务方面的性能。Colab即将推出的功能包括代码补全、自然语言到代码生成以及代码辅助聊天机器人。最初的重点将放在代码生成上，该功能旨在使用户能够生成更大的代码块并从注释或提示编写整个函数。这旨在减少编写重复代码的需求，允许用户专注于编程和数据科学的更复杂的方面
如何在iPhone上扫描QR码Jul 20, 2023 am 09:13 AMApple在设备中内置了这个方便的功能，可以从iPhone上的相机轻松访问它，这将允许您自动扫描设备上的QR码。二维码代表快速响应码，本质上是一种二维条形码，可以通过配备内置摄像头的各种智能手机和其他电子设备轻松扫描和解释。扫描二维码后，用户通常会被定向到特定网站或提示激活应用程序中的特定功能。这种令人难以置信的方便功能在现代智能手机（包括Apple的iPhone）中变得越来越普遍，它是用户以最小的努力访问信息，服务或功能的便捷方式。许多公司在实体产品上使用此功能，您可以扫描其产品上的二维码，然
鸿蒙OS3.0的功能有什么？Jun 29, 2023 pm 10:53 PM鸿蒙os3.0目前正在测试阶段，很快用户就将迎来新的系统体验了，那么相较于2.0版本，鸿蒙os3.0有什么功能呢？华为鸿蒙3.0包含了多屏协同、性能共享等功能，用户可以获得更加完善的协同体验，同时也能提升手机运行大型游戏或软件的流畅度。另外，它简化了小窗交互方式，并改进通知栏，带给你更为完美的体验，接下来就让小编给大家分析一下华为鸿蒙3.0新功能介绍，一起来了解一下吧。华为鸿蒙3.0功能介绍1、多屏协同：此前鸿蒙2.0可以在电脑手机之间互相切换使用，提高了用户的工作效率和使用体验，但此次的鸿蒙3
win10电脑系统纯版和专业版有什么区别Jul 19, 2023 am 11:01 AMwin10系统是目前主流的操作系统，也是微软最新的产品，版本很多。其中有网友纠结不知道选择win10纯版还是win10专业版，win10电脑系统纯版和专业版有什么区别。以下小系列将告诉你win10电脑系统纯版和专业版的区别。win10纯版：Win10纯版是网上第三方系统爱好者，在微软原版系统的基础上删除一些自带广告封装的系统。没有多余的软件捆绑，但稳定性可能没有正式版和专业版那么强。最重要的是win10纯版可以在网上下载安装，由民间专家优化，完全免费，功能和专业版没有太大区别。win10专业版：
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
2 周前By尊渡假赌尊渡假赌尊渡假赌
仓库：如何复兴队友
4 周前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险：如何获得巨型种子
3 周前By尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间？
3 周前ByDDD
R.E.P.O.保存文件位置：在哪里以及如何保护它？
3 周前ByDDD
热工具
Dreamweaver Mac版
视觉化网页开发工具
安全考试浏览器
Safe Exam Browser是一个安全的浏览器环境，用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问，并防止学生使用未经授权的资源。
禅工作室 13.0.1
功能强大的PHP集成开发环境
适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。
SublimeText3 英文版
推荐：为Win版本，支持代码提示！
