Shulou(Shulou.com)06/01 Report--

1. Then use the empty partition table built in the previous article

two。 Infuse data into partition table from never partition table

Mysql > insert into ar_detail_part select * from ar_detail

Query OK, 103606 rows affected (8.56 sec)

Records: 103606 Duplicates: 0 Warnings: 0

3. View the tablespace file for the table

4. Two approaches are planned:

1 ~ directly copy 2014-2016 tablespace files to a new instance-partitioned tablespace transfer

1. Create the same library in the new instance:

Create an empty table with the same schema:

Mysql-- login-path=3306 select * from ar_detail_part

->

Mysql > select count (1) from ar_detail_part

+-+

| | count (1) | |

+-+

| | 103606 |

+-+

1 row in set (0.82 sec)

2 ~ partition exchange (after exchange, cut each other, not recommended)

1. Create a table with the same structure in the new instance

CREATE TABLE `ar_detail_part_ year` (

`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 (19penny 4) DEFAULT NULL

`iCAmount` decimal (19pm 4) DEFAULT NULL

`iDAmount_ f` decimal (1951) DEFAULT NULL

`iCAmount_ f` decimal (1951) 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

`iCreditPerson` 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 (19pm 4) DEFAULT NULL

`iAmount_ f` decimal (1951) DEFAULT NULL

`iAmount_ s` double DEFAULT NULL

`iVouchAmount` decimal (19pr 4) DEFAULT NULL

`iVouchAmount_ f` decimal (1951) 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`, `dvouchdate`)

KEY `Ar_Detail_ibvid_ ind` (`iBVid`)

KEY `Ar_Detail_iflag_ ind` (`iFlag`)

KEY `Sys` (`cProcStyle`, `cexch_ name`, `cFlag`)

KEY `Ar_ cPZID` (`cPZid`)

KEY `ClosesID` (`iClosesID`)

KEY `CoClosesID` (`iCoClosesID`)

KEY `idx_Operator_Ar_ Detail` (`cOperator`)

KEY `CoVouchID` (`cCoVouchType`, `cCoVouchID`)

KEY `VouchID` (`cVouchType`, `cVouchID`)

KEY `INDEX_Ar_Detail_ HX` (`cDwCode`, `cDwCode`, `cCoVouchType`)

KEY `INDEX_Ar_Detail_ HXZD` (`cProcStyle`, `cCancelNo`, `cFlag`)

KEY `IX_ar_detail_Mx_ MIX1` (`cFlag`, `iFlag`, `cDwCode`, `dCreditStart`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Partition by range (year (dvouchdate)) (

Partition p2004 values less than (2005)

Partition p2005 values less than (2006)

Partition p2006 values less than (2007)

Partition p2007 values less than (2008)

Partition p2008 values less than (2009)

Partition p2009 values less than (2010)

Partition p2010 values less than (2011)

Partition p2011 values less than (2012)

Partition p2012 values less than (2013)

Partition p2013 values less than (2014)

Partition p2014 values less than (2015)

Partition p2015 values less than (2016)

Partition p2016 values less than (2017)

);

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.