mysql的分区

第一次听说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';