匿名通过本文主要向大家介绍了MySQL ，partition，分区字段等相关知识,希望本文的分享对您有所帮助
公司线上在用partition，有一个表的分区字段错了，需要重建，结果发现没有办法像修改主键字段或者修改索引字段那样直接一条sql搞定。而是需要建临时表，有down time，所以去仔细看了文档，研究下partition的细节问题。
自己公司线上采取的时候，凌晨1点业务低峰期，执行：
建立临时表
CREATE TABLE tbname_TMP (
SHARD_ID INT NOT NULL,
...
xxx_DATE DATETIME NOT NULL,
PRIMARY KEY (xxx_DATE,shard_id)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binPARTITION BY LIST(MONTH(xxx_DATE)) (
PARTITION m1 VALUES IN (1),
PARTITION m2 VALUES IN (2),
PARTITION m3 VALUES IN (3),
PARTITION m4 VALUES IN (4),
PARTITION m5 VALUES IN (5),
PARTITION m6 VALUES IN (6),
PARTITION m7 VALUES IN (7),
PARTITION m8 VALUES IN (8),
PARTITION m9 VALUES IN (9),
PARTITION m10 VALUES IN (10),
PARTITION m11 VALUES IN (11),
PARTITION m12 VALUES IN (12)
);切换表名字，修改表结构
RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;
导入原始数据
insert into xxx select * from xxx_DELETEDxxx_DELETED;
OK，一切搞定，整个过程50分钟，MMM failover切换中后outline操作表结构变更以及数据导入，实际downtime不包括修改表结构分区字段的时间，只包括failover切换时间 为30秒
MySQL Partition，看的官方英文资料，翻译水平有限，有些不翻译成中文了，直接贴英文了。
1 list partition table
mysql> CREATE TABLE `eh` (
-> `id` int(11) NOT NULL,
-> `ENTITLEMENT_HIST_ID` bigint(20) NOT NULL,
-> `ENTITLEMENT_ID` bigint(20) NOT NULL,
-> `USER_ID` bigint(20) NOT NULL,
-> `DATE_CREATED` datetime NOT NULL,
-> `STATUS` smallint(6) NOT NULL,
-> `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
-> `MODIFIED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
-> `DATE_MODIFIED` datetime NOT NULL,
-> PRIMARY KEY (`DATE_MODIFIED`,`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
-> /*!50100 PARTITION BY LIST (MONTH(DATE_MODIFIED))
-> (PARTITION m1 VALUES IN (1) ENGINE = InnoDB,
-> PARTITION m2 VALUES IN (2) ENGINE = InnoDB,
-> PARTITION m3 VALUES IN (3) ENGINE = InnoDB,
-> PARTITION m4 VALUES IN (4) ENGINE = InnoDB,
-> PARTITION m5 VALUES IN (5) ENGINE = InnoDB,
-> PARTITION m6 VALUES IN (6) ENGINE = InnoDB,
-> PARTITION m7 VALUES IN (7) ENGINE = InnoDB,
-> PARTITION m8 VALUES IN (8) ENGINE = InnoDB,
-> PARTITION m9 VALUES IN (9) ENGINE = InnoDB,
-> PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
-> PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
-> PARTITION m12 VALUES IN (12) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.10 sec)
2 rang partition table
mysql> CREATE TABLE rcx (
-> a INT,
-> b INT,
-> c CHAR(3),
-> d INT
-> )
-> PARTITION BY RANGE COLUMNS(a,d,c) (
-> PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
-> PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
Query OK, 0 rows affected (0.15 sec)
3 create range use less character
CREATE TABLE employees_by_lname (
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 NOT NULL,
store_id INT NOT NULL
)PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);alter table structure，add a new partition block
ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN ('u'),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
4 List columns partitioning
character column
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),
PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo')
);date column
CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);5 HASH Partitioning
int column,it can use digital function
CREATE TABLE employeesint (
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 HASH(MOD(store_id,4))
PARTITIONS 4;If you do not include a PARTITIONS clause, the number of partitions defaults to 1. as below:
CREATE TABLE employeestest (
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 HASH(store_id); date colum
CREATE TABLE employees2 (
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 HASH( YEAR(hired) )
PARTITIONS 4;truncate all data rows: