资源描述:
《mysql分区字段》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、MYSQL的分区字段,必须包含在主键字段内MYSQL的分区字段,必须包含在主键字段内在对表进行分区时,如果分区字段没有包含在主键字段内,如表A的主键为ID,分区字段为createtime,按时间范围分区,代码如下:CREATETABLET1(idint(8)NOTNULLAUTO_INCREMENT,createtimedatetimeNOTNULL,PRIMARYKEY(id))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8PARTITIONBYRANGE(TO_DAYS(createtime))(PARTITIONp0VALUESLESS
2、THAN(TO_DAYS('2010-04-15')),PARTITIONp1VALUESLESSTHAN(TO_DAYS('2010-05-01')),PARTITIONp2VALUESLESSTHAN(TO_DAYS('2010-05-15')),PARTITIONp3VALUESLESSTHAN(TO_DAYS('2010-05-31')),PARTITIONp4VALUESLESSTHAN(TO_DAYS('2010-06-15')),PARTITIONp19VALUESLESSThANMAXVALUE);错误提示:#1503APRIMARYKEYMUSTINCLUDEALLCOLU
3、MNSINTHETABLE'SPARTITIONINGFUNCTIONMySQL主键的限制,每一个分区表中的公式中的列,必须在主键/uniquekey中包括在MYSQL的官方文档里是这么说明的18.5.1.PartitioningKeys,PrimaryKeys,andUniqueKeysThissectiondiscussestherelationshipofpartitioningkeyswithprimarykeysanduniquekeys.Therulegoverningthisrelationshipcanbeexpressedasfollows:Allcolumnsusedin
4、thepartitioningexpressionforapartitionedtablemustbepartofeveryuniquekeythatthetablemayhave.Inotherwords,everyuniquekeyonthetablemustuseeverycolumninthetable'spartitioningexpression.(Thisalsoincludesthetable'sprimarykey,sinceitisbydefinitionauniquekey.Thisparticularcaseisdiscussedlaterinthissection.
5、)Forexample,eachofthefollowingtablecreationstatementsisinvalid:分区字段必须包含在主键字段内,至于为什么MYSQL会这样考虑,CSDN的斑竹是这么解释的:为了确保主键的效率。否则同一主键区的东西一个在A分区,一个在B分区,显然会比较麻烦。下面讨论解决办法,毕竟在一张表里,日期做主键的还是不常见。方法1:顺应MYSQL的要求,就把分区字段加入到主键中,组成复合主键CREATETABLET1(idint(8)NOTNULLAUTO_INCREMENT,createtimedatetimeNOTNULL,PRIMARYKEY(id,cr
6、eatetime))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8PARTITIONBYRANGE(TO_DAYS(createtime))(PARTITIONp0VALUESLESSTHAN(TO_DAYS('2010-04-15')),PARTITIONp1VALUESLESSTHAN(TO_DAYS('2010-05-01')),PARTITIONp2VALUESLESSTHAN(TO_DAYS('2010-05-15')),PARTITIONp3VALUESLESSTHAN(TO_DAYS('2010-05-31')),PARTITI
7、ONp4VALUESLESSTHAN(TO_DAYS('2010-06-15')),PARTITIONp19VALUESLESSThANMAXVALUE);测试通过,分区成功。方法2:既然MYSQL要把分区字段包含在主键内才能创建分区,那么在创建表的时候,先不指定主键字段,是否可以呢??测试如下:CREATETABLET1(idint(8)NOTNULL,createtimedatetimeNOTNULL)E