创建分区表

首页 > 代码库 > 创建分区表

2024-09-11 18:57:45 219人阅读

1.使用navicat从sqlserver向MySQL中导入部分数据

select count (1) from ar_detail

技术分享

2.检查导入表的建表语句

show create table ar_detail

PRIMARY KEY (`Auto_ID`),
KEY `Ar_Detail_ibvid_ind` (`iBVid`),
KEY `Ar_Detail_iflag_ind` (`iFlag`),
KEY `Ar_Detail_SY` (`cProcStyle`,`cexch_name`,`cFlag`),
KEY `Ar_cPZID` (`cPZid`),
KEY `Ar_iClosesID` (`iClosesID`),
KEY `Ar_iCoClosesID` (`iCoClosesID`),
KEY `idx_Operator_Ar_Detail` (`cOperator`),
KEY `INDEX_Ar_Detail_cCoVouchID` (`cCoVouchType`,`cCoVouchID`),
KEY `INDEX_Ar_Detail_cVouchID` (`cVouchType`,`cVouchID`),
KEY `INDEX_Ar_Detail_HX` (`cDwCode`,`cexch_name`,`cCoVouchType`),
KEY `INDEX_Ar_Detail_HXZD` (`cProcStyle`,`cCancelNo`,`cFlag`),
KEY `IX_ar_detail_Mx_MIX1` (`cFlag`,`iFlag`,`cDwCode`,`dCreditStart`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

3.将表引擎改为innodb

alter table ar_detail engine=innodb

mysql> alter table ar_detail engine=innodb;
Query OK, 103606 rows affected (1 min 42.65 sec)
Records: 103606 Duplicates: 0 Warnings: 0

检查一下：

show create table ar_detail

技术分享

修改完成

4.创建分区表

因为要测试按日期分表，接下来要对ar_detail进行dvouchdate中日期按年分区

技术分享

1~创建同模式但按年分区的空表

CREATE TABLE `ar_detail` (
`Auto_ID` int(11) NOT NULL,
`iPeriod` tinyint(4) NOT NULL,
`cVouchType` varchar(10) DEFAULT NULL,
`cVouchSType` varchar(2) DEFAULT NULL,
`cVouchID` varchar(30) NOT NULL,
`dVouchDate` datetime NOT NULL,
`dRegDate` datetime NOT NULL,
`cDwCode` varchar(20) NOT NULL,
`cDeptCode` varchar(12) DEFAULT NULL,
`cPerson` varchar(20) DEFAULT NULL,
`cInvCode` varchar(60) DEFAULT NULL,
`iBVid` int(11) DEFAULT NULL,
`cCode` varchar(40) DEFAULT NULL,
`cItem_Class` varchar(2) DEFAULT NULL,
`cItemCode` varchar(60) DEFAULT NULL,
`csign` varchar(2) DEFAULT NULL,
`isignseq` tinyint(4) DEFAULT NULL,
`ino_id` smallint(6) DEFAULT NULL,
`cDigest` varchar(255) DEFAULT NULL,
`iPrice` double DEFAULT NULL,
`cexch_name` varchar(8) NOT NULL,
`iExchRate` double DEFAULT NULL,
`iDAmount` decimal(19,4) DEFAULT NULL,
`iCAmount` decimal(19,4) DEFAULT NULL,
`iDAmount_f` decimal(19,4) DEFAULT NULL,
`iCAmount_f` decimal(19,4) DEFAULT NULL,
`iDAmount_s` double DEFAULT NULL,
`iCAmount_s` double DEFAULT NULL,
`cOrderNo` varchar(30) DEFAULT NULL,
`cSSCode` varchar(3) DEFAULT NULL,
`cPayCode` varchar(3) DEFAULT NULL,
`cProcStyle` varchar(10) DEFAULT NULL,
`cCancelNo` varchar(40) DEFAULT NULL,
`cPZid` varchar(30) DEFAULT NULL,
`bPrePay` tinyint(4) DEFAULT NULL,
`iFlag` tinyint(4) DEFAULT NULL,
`cCoVouchType` varchar(10) DEFAULT NULL,
`cCoVouchID` varchar(30) DEFAULT NULL,
`cFlag` varchar(2) NOT NULL,
`cDefine1` varchar(20) DEFAULT NULL,
`cDefine2` varchar(20) DEFAULT NULL,
`cDefine3` varchar(20) DEFAULT NULL,
`cDefine4` datetime DEFAULT NULL,
`cDefine5` int(11) DEFAULT NULL,
`cDefine6` datetime DEFAULT NULL,
`cDefine7` double DEFAULT NULL,
`cDefine8` varchar(4) DEFAULT NULL,
`cDefine9` varchar(8) DEFAULT NULL,
`cDefine10` varchar(60) DEFAULT NULL,
`iClosesID` int(11) NOT NULL,
`iCoClosesID` int(11) NOT NULL,
`cDefine11` varchar(120) DEFAULT NULL,
`cDefine12` varchar(120) DEFAULT NULL,
`cDefine13` varchar(120) DEFAULT NULL,
`cDefine14` varchar(120) DEFAULT NULL,
`cDefine15` int(11) DEFAULT NULL,
`cDefine16` double DEFAULT NULL,
`cGLSign` varchar(8) DEFAULT NULL,
`iGLno_id` smallint(6) DEFAULT NULL,
`dPZDate` datetime DEFAULT NULL,
`cItemName` varchar(255) DEFAULT NULL,
`cContractType` varchar(10) DEFAULT NULL,
`cContractID` varchar(64) DEFAULT NULL,
`BalancesGuid` char(36) DEFAULT NULL,
`dHideDate` datetime DEFAULT NULL,
`cGatheringPlan` varchar(10) DEFAULT NULL,
`dCreditStart` datetime DEFAULT NULL,
`iCreditPeriod` int(11) DEFAULT NULL,
`dGatheringDate` datetime DEFAULT NULL,
`bCredit` tinyint(4) DEFAULT NULL,
`cOperator` varchar(20) DEFAULT NULL,
`cCheckMan` varchar(20) DEFAULT NULL,
`iOrderType` tinyint(4) DEFAULT NULL,
`cDLCode` varchar(30) DEFAULT NULL,
`idlsid` int(11) DEFAULT NULL,
`copcode` varchar(20) DEFAULT NULL,
`dVouDate` datetime DEFAULT NULL,
`cDefine22` varchar(60) DEFAULT NULL,
`cDefine23` varchar(60) DEFAULT NULL,
`cDefine24` varchar(60) DEFAULT NULL,
`cDefine25` varchar(60) DEFAULT NULL,
`cDefine26` double DEFAULT NULL,
`cDefine27` double DEFAULT NULL,
`cDefine28` varchar(120) DEFAULT NULL,
`cDefine29` varchar(120) DEFAULT NULL,
`cDefine30` varchar(120) DEFAULT NULL,
`cDefine31` varchar(120) DEFAULT NULL,
`cDefine32` varchar(120) DEFAULT NULL,
`cDefine33` varchar(120) DEFAULT NULL,
`cDefine34` int(11) DEFAULT NULL,
`cDefine35` int(11) DEFAULT NULL,
`cDefine36` datetime DEFAULT NULL,
`cDefine37` datetime DEFAULT NULL,
`iAmount` decimal(19,4) DEFAULT NULL,
`iAmount_f` decimal(19,4) DEFAULT NULL,
`iAmount_s` double DEFAULT NULL,
`iVouchAmount` decimal(19,4) DEFAULT NULL,
`iVouchAmount_f` decimal(19,4) DEFAULT NULL,
`iVouchAmount_s` double DEFAULT NULL,
`dtZbjEndDate` datetime DEFAULT NULL,
`cExecID` varchar(30) DEFAULT NULL,
`cBusType` varchar(8) DEFAULT NULL,
~~PRIMARY KEY (`Auto_ID`),~~
KEY `Ar_Detail_ibvid_ind` (`iBVid`),
KEY `Ar_Detail_iflag_ind` (`iFlag`),
KEY `Ar_Detail_SY` (`cProcStyle`,`cexch_name`,`cFlag`),
KEY `Ar_cPZID` (`cPZid`),
KEY `Ar_iClosesID` (`iClosesID`),
KEY `Ar_iCoClosesID` (`iCoClosesID`),
KEY `idx_Operator_Ar_Detail` (`cOperator`),
KEY `INDEX_Ar_Detail_cCoVouchID` (`cCoVouchType`,`cCoVouchID`),
KEY `INDEX_Ar_Detail_cVouchID` (`cVouchType`,`cVouchID`),
KEY `INDEX_Ar_Detail_HX` (`cDwCode`,`cexch_name`,`cCoVouchType`),
KEY `INDEX_Ar_Detail_HXZD` (`cProcStyle`,`cCancelNo`,`cFlag`),
KEY `IX_ar_detail_Mx_MIX1` (`cFlag`,`iFlag`,`cDwCode`,`dCreditStart`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

最后增加(less than 等同于“<”)

partition by range columns (dvouchdate) ( |column分区支持按日期进行分区，普通range分区只能通过证书进行分区

partition p2004 values less than (‘2005-01-01 00:00:00.000‘),

partition p2005 values less than (‘2006-01-01 00:00:00.000‘),

partition p2006 values less than (‘2007-01-01 00:00:00.000‘),

partition p2007 values less than (‘2008-01-01 00:00:00.000‘),

partition p2008 values less than (‘2009-01-01 00:00:00.000‘),

partition p2009 values less than (‘2010-01-01 00:00:00.000‘),

partition p2010 values less than (‘2011-01-01 00:00:00.000‘),

partition p2011 values less than (‘2012-01-01 00:00:00.000‘),

partition p2012 values less than (‘2013-01-01 00:00:00.000‘),

partition p2013 values less than (‘2014-01-01 00:00:00.000‘),

partition p2014 values less than (‘2015-01-01 00:00:00.000‘),

partition p2015 values less than (‘2016-01-01 00:00:00.000‘),

partition p2016 values less than (‘2017-01-01 00:00:00.000‘)

);

2~坑点：

1.分区参考属性必须为主键之一（组合主键），或者整表没有主键

报错：

A PRIMARY KEY must include all columns in the table‘s partitioning function

修复：

PRIMARY KEY (`Auto_ID`,`dvouchdate`),

3~优化点：

1.MySQL支持在partition by range （）中使用函数表达式

可以将分区语句改写成：（部分）

partition by range columns (year(dvouchdate)) (

partition p2004 values less than (2005),

partition p2005 values less than (2006),

partition p2006 values less than (2007),

----------------------------------------------

partition p2015 values less than (2016),

partition p2016 values less than (2017)

);

2.不支持datepart(year,dvouchdate)这种含有参量的函数

创建分区表

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 创建分区表

创建分区表

看完仍有疑问？有类似问题直接问程序猿