大数据表分区

CREATE TABLE `stat_refurl1` (

  `url` varchar(500) NOT NULL,

  `urlhash` varchar(64) NOT NULL,

  `hashcode` int(11) DEFAULT NULL,

  UNIQUE KEY `IndexUnique` (`urlhash`,`hashcode`)

) ENGINE=innodb DEFAULT CHARSET=utf8

 PARTITION BY RANGE (hashcode)

(PARTITION p0 VALUES LESS THAN (49) ENGINE = innodb,

 PARTITION p1 VALUES LESS THAN (50) ENGINE = innodb,

 PARTITION p2 VALUES LESS THAN (51) ENGINE = innodb,

 PARTITION p3 VALUES LESS THAN (52) ENGINE = innodb,

 PARTITION p4 VALUES LESS THAN (53) ENGINE = innodb,

 PARTITION p5 VALUES LESS THAN (54) ENGINE = innodb,

 PARTITION p6 VALUES LESS THAN (55) ENGINE = innodb,

 PARTITION p7 VALUES LESS THAN (56) ENGINE = innodb,

 PARTITION p8 VALUES LESS THAN (57) ENGINE = innodb,

 PARTITION p9 VALUES LESS THAN (58) ENGINE = innodb,

 PARTITION pa VALUES LESS THAN (98) ENGINE = innodb,

 PARTITION pb VALUES LESS THAN (99) ENGINE = innodb,

 PARTITION pc VALUES LESS THAN (100) ENGINE = innodb,

 PARTITION pd VALUES LESS THAN (101) ENGINE = innodb,

 PARTITION pe VALUES LESS THAN (102) ENGINE = innodb,

 PARTITION pf VALUES LESS THAN MAXVALUE ENGINE = innodb)

 

 

 

 CREATE TABLE `stat_url1` (

  `url` varchar(500) NOT NULL,

  `urlhash` varchar(64) NOT NULL,

  `title` TEXT NOT NULL COMMENT '来源域名',

  `hashcode` int(11) DEFAULT NULL,

  UNIQUE KEY `IndexUnique` (`urlhash`,`hashcode`)

) ENGINE=innodb DEFAULT CHARSET=utf8

 PARTITION BY RANGE (hashcode)

(PARTITION p0 VALUES LESS THAN (49) ENGINE = innodb,

 PARTITION p1 VALUES LESS THAN (50) ENGINE = innodb,

 PARTITION p2 VALUES LESS THAN (51) ENGINE = innodb,

 PARTITION p3 VALUES LESS THAN (52) ENGINE = innodb,

 PARTITION p4 VALUES LESS THAN (53) ENGINE = innodb,

 PARTITION p5 VALUES LESS THAN (54) ENGINE = innodb,

 PARTITION p6 VALUES LESS THAN (55) ENGINE = innodb,

 PARTITION p7 VALUES LESS THAN (56) ENGINE = innodb,

 PARTITION p8 VALUES LESS THAN (57) ENGINE = innodb,

 PARTITION p9 VALUES LESS THAN (58) ENGINE = innodb,

 PARTITION pa VALUES LESS THAN (98) ENGINE = innodb,

 PARTITION pb VALUES LESS THAN (99) ENGINE = innodb,

 PARTITION pc VALUES LESS THAN (100) ENGINE = innodb,

 PARTITION pd VALUES LESS THAN (101) ENGINE = innodb,

 PARTITION pe VALUES LESS THAN (102) ENGINE = innodb,

 PARTITION pf VALUES LESS THAN MAXVALUE ENGINE = innodb)

 

 

  CREATE TABLE `stat_keyword1` (

  `keywordhash` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL,

  `keyword` TEXT COLLATE utf8_bin,

  `hashcode` int(11) DEFAULT NULL,

  UNIQUE KEY `IndexUnique` (`keywordhash`,`hashcode`)

) ENGINE=innodb DEFAULT CHARSET=utf8

 PARTITION BY RANGE (hashcode)

(PARTITION p0 VALUES LESS THAN (49) ENGINE = innodb,

 PARTITION p1 VALUES LESS THAN (50) ENGINE = innodb,

 PARTITION p2 VALUES LESS THAN (51) ENGINE = innodb,

 PARTITION p3 VALUES LESS THAN (52) ENGINE = innodb,

 PARTITION p4 VALUES LESS THAN (53) ENGINE = innodb,

 PARTITION p5 VALUES LESS THAN (54) ENGINE = innodb,

 PARTITION p6 VALUES LESS THAN (55) ENGINE = innodb,

 PARTITION p7 VALUES LESS THAN (56) ENGINE = innodb,

 PARTITION p8 VALUES LESS THAN (57) ENGINE = innodb,

 PARTITION p9 VALUES LESS THAN (58) ENGINE = innodb,

 PARTITION pa VALUES LESS THAN (98) ENGINE = innodb,

 PARTITION pb VALUES LESS THAN (99) ENGINE = innodb,

 PARTITION pc VALUES LESS THAN (100) ENGINE = innodb,

 PARTITION pd VALUES LESS THAN (101) ENGINE = innodb,

 PARTITION pe VALUES LESS THAN (102) ENGINE = innodb,

 PARTITION pf VALUES LESS THAN MAXVALUE ENGINE = innodb)

 

 

 DELIMITER $$

 

USE `new_stat`$$

 

DROP TRIGGER /*!50032 IF EXISTS */ `insertRefurl`$$

 

CREATE

    /*!50017 DEFINER = 'root'@'219.224.%' */

    TRIGGER `insertRefurl` BEFORE INSERT ON `stat_refurl1` 

    FOR EACH ROW BEGIN

     SET NEW.hashcode = ASCII(LEFT(NEW.urlhash,1));

    END;

$$

 

DELIMITER ;

 

DELIMITER $$

 

CREATE

    TRIGGER `new_stat`.`insertUrl` BEFORE INSERT

    ON `new_stat`.`stat_url1`

    FOR EACH ROW BEGIN

    SET NEW.hashcode = ASCII(LEFT(NEW.urlhash,1));

    END$$

 

DELIMITER ;

 

 

DELIMITER $$

 

CREATE

    /*[DEFINER = { user | CURRENT_USER }]*/

    TRIGGER `new_stat`.`insertKeyWord` BEFORE INSERT

    ON `new_stat`.`stat_keyword1`

    FOR EACH ROW BEGIN

    SET NEW.hashcode = ASCII(LEFT(NEW.keywordhash,1));

    END$$

 

DELIMITER ;