第一次听说mysql的分区,记录一下相关语句:
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`age` int(11) NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `user` PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN (100000),
PARTITION p1 VALUES LESS THAN (200000),
PARTITION p2 VALUES LESS THAN (300000),
PARTITION p3 VALUES LESS THAN (400000)
);
-- 插入数据
INSERT INTO `user` (`id`, `name`, `age`) VALUES (1, '张三', 18);
INSERT INTO `user` (`id`, `name`, `age`) VALUES (2, '李四', 20);
INSERT INTO `user` (`id`, `name`, `age`) VALUES (3, '王五', 22);
INSERT INTO `user` (`id`, `name`, `age`) VALUES (200000, '赵六', 24);
INSERT INTO `user` (`id`, `name`, `age`) VALUES (200004, '何七', 26);
-- 查询数据
SELECT * FROM `user` WHERE `id` = 1;
SELECT PARTITION_NAME,PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'user';
CREATE TABLE `order` (
`order_id` INT(11) NOT NULL AUTO_INCREMENT,
`user_id` INT(11) NOT NULL,
`order_time` DATETIME NOT NULL,
`amount` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`order_id`, `order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(order_time)) (
PARTITION p0 VALUES LESS THAN (2010),
PARTITION p1 VALUES LESS THAN (2011),
PARTITION p2 VALUES LESS THAN (2012),
PARTITION p3 VALUES LESS THAN (2013),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
INSERT INTO `order` (`user_id`, `order_time`, `amount`) VALUES (1, '2010-01-01 00:00:00', 100);
INSERT INTO `order` (`user_id`, `order_time`, `amount`) VALUES (2, '2011-01-01 00:00:00', 200);
INSERT INTO `order` (`user_id`, `order_time`, `amount`) VALUES (3, '2012-01-01 00:00:00', 300);
INSERT INTO `order` (`user_id`, `order_time`, `amount`) VALUES (4, '2013-01-01 00:00:00', 400);
SELECT * FROM `order` WHERE `order_time` >= '2011-01-01 00:00:00';
SELECT PARTITION_NAME,PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'order';