分区表部分分区导出到其他实例
1.接着用上篇文章中建好的空分区表
在岳麓等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都网站制作、网站设计 网站设计制作按需求定制制作,公司网站建设,企业网站建设,品牌网站制作,营销型网站,成都外贸网站建设,岳麓网站建设费用合理。
2.从未分区表中向分区表中灌数据
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.查看该表的表空间文件
4.计划采取两种办法：
1~直接复制2014-2016的表空间文件到新的实例中--分区表空间传输
1.在新实例中创建相同的库:
创建相同模式的空表:
mysql --login-path=3306 <ar_detail_part_schema.sql
CREATE TABLE `ar_detail_part` (
`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`,`dVouchDate`),
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
partition by range columns (dvouchdate) (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')
);
删除表空间
alter table ar_detail_part discard table space;
2.在源表上：
锁表用于复制表空间
flush tables ar_detail_part for export
复制出来
3.复制到目标库上
更改文件的权限为mysql.mysql用户所有
[root@localhost ufdata_min]# chown mysql.mysql ar*
4.引入表空间
alter table ar_detail_part import tablespace;
5.尝试查看表
mysql> select * from ar_detail_part
-> ;mysql> select count(1) from ar_detail_part;
+----------+
| count(1) |
+----------+
| 103606 |
+----------+
1 row in set (0.82 sec)
2~分区交换（交换后，互相剪切，不推荐）1.在新实例中创建同样结构的一张表
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(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`,`dvouchdate`),
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
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)
);
文章标题：分区表部分分区导出到其他实例
转载注明：/article10/gedjdo.html
成都网站建设公司_创新互联，为您提供服务器托管、电子商务、移动网站建设、ChatGPT、微信公众号、品牌网站制作
声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联
- 微信小程序商城开发需要哪些基本条件？ 2022-05-19
- 小程序开发：应该如何利用微信小程序？ 2022-05-02
- 微信小程序如何避免被封？ 2022-09-13
- 如何设计开发一款好看的微信小程序 2022-11-29
- 商家该如何通过微信小程序开发来增长业绩 2021-02-16
- 微信小程序可以自己做吗？没有编程基础 2021-08-13
- 微信小程序怎么申请？微信怎么申请小程序？ 2022-05-13
- 微信小程序定制开发需要注意的事项 2023-03-07
- 线下实体店微信小程序开发如何实现与线上的连接？ 2022-05-25
- 微信小程序增加任务栏功能和小游戏 2023-03-22
- 微信小程序哪家公司开发好?成都微信小程序开发公司推荐 2022-08-12
- 微信小程序开发多少钱，开发一个小程序需要多少钱？ 2021-09-23