mysql分区

-- mysql更新判断
UPDATE `student` SET SEX = (
CASE WHEN AGE < 17 THEN '小孩'
      WHEN AGE > 22 THEN '帅哥'
ELSE '学生' END);

-- mysql查询判断
SELECT
 `NAME`, AGE, SEX ,
 case when AGE < 17 then '小孩'
     when AGE > 22 then '帅哥'
 else '学生'
  end as 'val'
FROM `student`

-- RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
CREATE TABLE `a` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `stsdate` DATE NOT NULL,
   PRIMARY KEY (`id`, `stsdate`)
)
PARTITION BY RANGE COLUMNS(stsdate) (
    PARTITION p0 VALUES LESS THAN ('2016-06-01'),
    PARTITION p1 VALUES LESS THAN ('2016-07-01'),
    PARTITION p2 VALUES LESS THAN ('2016-08-01'),
    PARTITION p3 VALUES LESS THAN ('2016-09-01'),
    PARTITION p4 VALUES LESS THAN ('2016-10-01'),
    PARTITION p5 VALUES LESS THAN ('2016-11-01'),
    PARTITION p6 VALUES LESS THAN ('2016-12-01'),
    PARTITION p7 VALUES LESS THAN ('2017-01-01'),
    PARTITION p8 VALUES LESS THAN ('2017-02-01'),
    PARTITION p9 VALUES LESS THAN ('2017-03-01'),
    PARTITION p10 VALUES LESS THAN ('2017-05-01'),
    PARTITION p11 VALUES LESS THAN ('2017-06-01'),
    PARTITION p12 VALUES LESS THAN ('2017-07-01'),
    PARTITION p13 VALUES LESS THAN ('2017-08-01'),
    PARTITION p14 VALUES LESS THAN ('2017-09-01'),
    PARTITION p15 VALUES LESS THAN MAXVALUE
);
-- LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);


-- HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

CREATE TABLE c (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

-- KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。