MySQL partition Table partition online partition field Modification

MySQL partition Table partition online modify partition field company online use partition, there is a table partition field error, need to be rebuilt, it turns out that there is no way to directly execute an SQL statement like modifying the primary key field or modifying the index field. Instead, you need to create a temporary table with a down time. So I carefully read the document and studied the details of partition. When your company is online, the business is off-peak at. Execute: CREATE temporary 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 ), P ARTITION 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); Switch the TABLE name, modify the rename table xxx TO xxx_DELETED, xxx_TMP TO xxx; import the original data insert into xxx select * from xxx_DELETEDxxx_DELETED; OK, everything is done. The entire process is 50 minutes. After the MMM failover is switched, the outline operator changes the table structure and imports data. The actual downtime does not include the time for modifying the table structure partition field, only the failover switching time is 30 seconds. Tition: the official English documents. The translation level is limited. If some documents are not translated into Chinese, they are directly posted in English. 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 _ MODIF IED '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 I N (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) (-> PART ITION 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 '2017-01 -01 ', separated date not null default '2017-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 blockALTER 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 partitioningcharacter columnCREATE 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 LI St columns (city) (PARTITION pRegion_1 values in ('osskarshamn', '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', 'alivesta', 'v? Xjo '); date columnCREATE 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 ('2017-02-01 ', '2017-02-02', '2017-02-03 ', '2017-02-04 ', '2017-02-05 ', '2017-02-06', '2017-02-07 '), PARTITION pWeek_2 values in ('2017-02-08', '2017-02-09 ', '2017-02-10 ', '2017-02-11', '2017-02-12 ', '2017-02-13', '2017-02-14 '), PARTITION pWeek_3 values in ('2017-02-15 ', '2017-02-16', '2017-02-17 ', '2017-02-18', '2017-02-19 ', '2017-02-20 ', '2017-02-21'), PARTITION pWeek_4 values in ('2017-02-22 ', '2017-02-23', '2017-02-24 ', '2017-02-25 ', '2017-02-26', '2017-02-27 ', '2017-02-28'); 5 HASH Partitioning int column, it can use digital functionCREATE TABLE employeesint (id int not null, fname VARCHAR (30), lname VARCHAR (30), hired date not null default '2017-01-01 ', separated date not null default '2017-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 '2017-01-01 ', separated date not null default '2017-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 '2017-01-01 ', separated date not null default '2017-12-31', job_code INT, store_id INT) partition by hash (YEAR (hired) PARTITIONS 4; truncate all data rows: alter table rcx truncate PARTITION; 6 linear hash PartitioningCREATE TABLE employees_linear (id int not null, fname VARCHAR (30), lname VARCHAR (30), hired date not null default '2017-01-01 ', separated date not null default '2017-12-31', job_code INT, store_id INT) partition by linear hash (YEAR (hired) PARTITIONS 4; Given an expression expr, the partition in which the record is stored when linear hashing is used is partition number N from among num partitions, where N is derived according to the following algorithm :( 1) Find the next power of 2 greater than num. we call this value V; it can be calculated as: V = POWER (2, CEILING (LOG (2, num) (Suppose that num is 13. then LOG (2,13) is 3.7004397181411. CEILING (3.7004397181411) is 4, and V = POWER (2, 4), which is 16 .) (2) Set N = F (column_list) & (V-1 ). (3) While N> = num: Set V = CEIL (V/2) Set N = N & (V-1) [comment] & the computation principles in SQL are as follows: for example, you can convert the decimal to binary. http://zh.wikipedia.org/wiki/%E4%BA%8C%E8%BF%9B%E5%88%B6 First, align to the right, for example, to 0011 and 1000, and judge by the number of each digit. If both are 1, the corresponding position of the result is 1, otherwise, if 0 is 1011 and 1000, the result is 1000. If it is 0110 and 1010, the result is 0010, but 3 is 1000, and 8 is. Therefore, the result of 3 & 8 is 0 CEILING (X) CEIL (X): returns the smallest integer not less than X. LOG (X) LOG (B, X): If a parameter is called, this function returns the natural logarithm of X. POWER (X, Y): returns the result value of the Y multiplication of X. Calculation Method of the region where data is distributed: Suppose that the table t1, using linear hash partitioning and having 6 partitions, is created using this statement: create table t1 (col1 INT, col2 CHAR (5), col3 DATE) partition by linear hash (YEAR (col3) PARTITIONS 6; now assume that you want to insert two records into t1 having the col3 column values '2017-04-14 'and '2017-10-19 '. the partition number for the first of these is determined as follows: V = POWER (2, CEILING (LOG (2003) = 8 N = YEAR ('2017-04-14 ') & (8-1) = 2003 & 7 = 3 (3> = 6 is FALSE: record stored in partition #3) the number of the partition where the second record is stored is calculated as shown here: V = 8 N = YEAR ('2017-10-19 ') & (8-1) = 1998 & 7 = 6 (6> = 6 is TRUE: additional step required) N = 6 & CEILING (8/2) = 6 & 3 = 2 (2> = 6 is FALSE: record stored in partition #2) The advantage in partitioning by linear hash is that the adding, dropping, merging, and splitting of partitions is made much faster, which can be beneficial when dealing with tables containing extremely large amounts (terabytes) of data. the disadvantage is that data is less likely to be evenly distributed between partitions as compared with the distribution obtained using regular hash partitioning.