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)这种含有参量的函数
