esfpicture 分表过程-优快云博客

esfpicture有千万以上的数据量,根据bid对100取模分表.

1.先对bid取模,保存到sitecode字段

UPDATE esfpicture_all esf SET esf.sitecode = esf.bid%100 ;

2.编写存储过程,创建分表,并将总表数据插入到分表

DELIMITER $$

USE `public`$$

DROP PROCEDURE IF EXISTS `esfpicture`$$

CREATE DEFINER=`jiwudev`@`192.168.0.%` PROCEDURE `esfpicture`()
BEGIN
DECLARE `@i` INT(11);
DECLARE `@siteCount` INT(11);
DECLARE `@sqlstr` VARCHAR(2560);
DECLARE `@sqlinsert` VARCHAR(2560);

SET `@i`=1;
WHILE `@i`<=100 DO
SET @sqlstr = CONCAT('
CREATE TABLE esfpicture',`@i`,' (
`BPID` int(11) NOT NULL,
`Type` smallint(6) NOT NULL,
`FileName` varchar(100) NOT NULL,
`Path` varchar(500) NOT NULL,
`Status` smallint(6) NOT NULL,
`Suffix` varchar(10) NOT NULL,
`Remark` text,
`BID` int(11) NOT NULL,
`CTime` date NOT NULL,
`NID` int(11) NOT NULL,
`NIP` varchar(20) DEFAULT NULL,
`SiteCode` smallint(6) NOT NULL,
`SourceName` varchar(200) NOT NULL,
`PictureSize` int(11) NOT NULL,
`AllPath` varchar(200) NOT NULL,
`Vtimes` int(11) NOT NULL,
`IsCover` smallint(6) NOT NULL,
`HID` int(11) NOT NULL,
UNIQUE KEY `BPID` (`BPID`),
KEY `AllPath` (`AllPath`),
KEY `HID` (`HID`),
KEY `BID_ALLpath` (`BID`,`AllPath`)
) ENGINE=MyISAM AUTO_INCREMENT=13392873 DEFAULT CHARSET=utf8
');
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
SET @sqlinsert = CONCAT('INSERT INTO esfpicture',`@i`,' (`BPID`,`Type`,`FileName`,`Path`,`Status`,`Suffix`,`Remark`,`BID`,`CTime`,`NID`,`NIP`,`SiteCode`,`SourceName`,`PictureSize`,`AllPath`,`Vtimes`,`IsCover`,`HID`)
SELECT `BPID`,`Type`,`FileName`,`Path`,`Status`,`Suffix`,`Remark`,`BID`,`CTime`,`NID`,`NIP`,`SiteCode`,`SourceName`,`PictureSize`,`AllPath`,`Vtimes`,`IsCover`,`HID` FROM `esfpicture_all` where sitecode = ',`@i`,'');
PREPARE stmt FROM @sqlinsert;
EXECUTE stmt;
SET `@i`= `@i`+1;
END WHILE;
END$$

DELIMITER ;