關于MySQL分區(qū)表怎么自動建，首先需要了解什么是MySQL分區(qū)表。MySQL分區(qū)表是指將一張大表拆分成多個小表，達到優(yōu)化大數(shù)據(jù)查詢的目的。在這個基礎上，我們可以通過以下步驟實現(xiàn)自動建立MySQL分區(qū)表：

步驟一：安裝并運行MySQL分區(qū)插件

mysql>install plugin partition;

步驟二：創(chuàng)建原始的大表結構

CREATE TABLE `big_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步驟三：將大表分區(qū)并建立小表

ALTER TABLE big_table PARTITION BY RANGE (score)(
PARTITION p0 VALUES LESS THAN (70),
PARTITION p1 VALUES LESS THAN MAXVALUE
 );
CREATE TABLE `small_table_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
 PARTITION BY RANGE (score)(
PARTITION p0 VALUES LESS THAN (70),
PARTITION p1 VALUES LESS THAN MAXVALUE
 );
CREATE TABLE `small_table_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
 PARTITION BY RANGE (score)(
PARTITION p0 VALUES LESS THAN (70),
PARTITION p1 VALUES LESS THAN MAXVALUE
 );

步驟四：通過觸發(fā)器將數(shù)據(jù)自動分配到小表

DELIMITER $$
CREATE TRIGGER `insert_trig` BEFORE INSERT ON `big_table`
 FOR EACH ROW BEGIN
IF NEW.score<70 THEN
INSERT INTO small_table_0 VALUES (NEW.id, NEW.name, NEW.age, NEW.sex, NEW.score);
ELSE
INSERT INTO small_table_1 VALUES (NEW.id, NEW.name, NEW.age, NEW.sex, NEW.score);
END IF;
 END$$
DELIMITER ;

通過以上步驟，就可以實現(xiàn)MySQL分區(qū)表的自動創(chuàng)建，從而達到優(yōu)化查詢的效果。