深入了解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 Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
2 周前
By 尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间？
1 个月前
By DDD
R.E.P.O.保存文件位置：在哪里以及如何保护它？
1 个月前
By DDD
R.E.P.O.最佳图形设置
2 周前
By 尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影：贝壳谜语解决方案
1 周前
By DDD
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
豆包app有什么功能
Mar 01, 2024 pm 10:04 PM
豆包app里会有很多ai创作的功能，那么豆包app有什么功能呢？用户们可以通过这个软件来创作绘画，和ai进行聊天，还能够为用户生成文章，帮助大家搜索歌曲等。这篇豆包app功能介绍就能够告诉大家具体的操作方法，下面就是具体内容，赶紧看看吧！豆包app有什么功能答：可以绘画、聊天、写文、找歌。功能介绍：1、问题查询：可以通过ai来更快的找到问题的答案，什么样的问题都是可以询问。2、图片生成：可以有ai来为大家创建不同的图片，只需要告诉大家大概的要求。3、ai聊天：能够为用户们创建一个可以聊天的ai，
vivox100s和x100区别：性能对比及功能解析
Mar 23, 2024 pm 10:27 PM
vivox100s和x100手机都是vivo手机产品线中的代表机型，它们分别代表了vivo在不同时间段内的高端技术水平，因此这两款手机在设计、性能和功能上均有一定区别。本文将从性能对比和功能解析两个方面对这两款手机进行详细比较，帮助消费者更好地选择适合自己的手机。首先，我们来看vivox100s和x100在性能方面的对比。vivox100s搭载了最新的
自媒体到底是什么？它的主要特点和功能有哪些？
Mar 21, 2024 pm 08:21 PM
随着互联网的快速发展，自媒体这个概念已经深入人心。那么，自媒体到底是什么？它有哪些主要特点和功能呢？接下来，我们将一一探讨这些问题。一、自媒体到底是什么？自媒体，顾名思义，就是自己就是媒体。它是指通过互联网平台，个人或者团队可以自主创建、编辑、发布和传播内容的信息载体。不同于传统媒体，如报纸、电视、电台等，自媒体具有更强的互动性和个性化，让每个人都能成为信息的生产者和传播者。二、自媒体的主要特点和功能有哪些？1.低门槛：自媒体的崛起降低了进入媒体行业的门槛，不再需要繁琐的设备和专业的团队，一部手
什么是Discuz？Discuz的定义和功能介绍
Mar 03, 2024 am 10:33 AM
《探索Discuz：定义、功能及代码示例》随着互联网的迅猛发展，社区论坛已经成为人们获取信息、交流观点的重要平台。在众多的社区论坛系统中，Discuz作为国内较为知名的一种开源论坛软件，备受广大网站开发者和管理员的青睐。那么，什么是Discuz？它又有哪些功能，能为我们的网站提供怎样的帮助呢？本文将对Discuz进行详细介绍，并附上具体的代码示例，帮助读者更
PHP技巧：快速实现返回上一页功能
Mar 09, 2024 am 08:21 AM
PHP技巧：快速实现返回上一页功能在网页开发中，经常会遇到需要实现返回上一页的功能。这样的操作可以提高用户体验，让用户更加方便地在网页之间进行导航。在PHP中，我们可以通过一些简单的代码来实现这一功能。本文将介绍如何快速实现返回上一页功能，并提供具体的PHP代码示例。在PHP中，我们可以使用$_SERVER['HTTP_REFERER']来获取上一页的URL
小红书账号管理软件有哪些功能？怎么经营小红书账号？
Mar 21, 2024 pm 04:16 PM
随着小红书在年轻人中的流行，越来越多的人开始利用这一平台分享各方面的经验和生活见解。如何有效管理多个小红书账号成为一个关键问题。在本文中，我们将讨论一些小红书账号管理软件的功能，并探讨如何更好地经营小红书账号。随着社交媒体的发展，许多人发现自己需要管理多个社交账号。对于小红书用户来说，这也是一个挑战。一些小红书账号管理软件可以帮助用户更轻松地管理多个账号，包括自动发布内容、定时发布、数据分析等功能。通过这些工具，用户可以更高效地管理他们的账号，提高账号的曝光率和关注度。另一、小红书账号管理软件有
Linux下GDM的功能和作用详解
Mar 01, 2024 pm 04:18 PM
Linux下GDM的功能和作用详解在Linux操作系统中，GDM（GNOMEDisplayManager）是一种图形化登录管理器，它提供了用户在系统中登录和注销的界面。GDM通常是GNOME桌面环境的一部分，但也可以被其他桌面环境所使用。GDM的作用不仅仅是提供一个登录界面，还包括用户会话管理、屏幕保护、自动登录等功能。GDM的功能主要包括以下几个方面：
PHP是做什么用的？探究PHP的作用与功能
Mar 24, 2024 am 11:39 AM
PHP是一种广泛应用于Web开发的服务器端脚本语言，它的主要作用是生成动态网页内容，与HTML结合使用，可以创建出丰富多彩的网页。PHP的功能强大，它可以执行各种数据库操作、文件操作、表单处理等任务，为网站提供强大的交互性和功能性。在接下来的文章中，我们将进一步探究PHP的作用与功能，并配以详细的代码示例。首先，我们来看一下PHP的常见用途：动态网页生成：P
