MySQL 分區表 partition線上修改分區字段，後續進一步學習partition (1)

公司線上在用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)
    );

切換表名字，修改表結構

reee

OK，一切搞定，整個過程50分鐘，MMM failover切換中後outline操作表結構變更以及資料導入，實際downtime不包括修改表結構分區字段的時間，只包括failover切換時間為30秒

MySQL Partition，看的官方英文資料，翻譯程度有限，有些不翻譯成中文了，直接貼英文了。 1 list partition table

RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;

2 rang partition table

insert into xxx select * from xxx_DELETEDxxx_DELETED;

 
r 333 range alter table structure，add a new partition block

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)

4 List columns partitioning

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)

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
)

date column

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)
);

rrree S clause, the number of partitions defaults to 1. as below:

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)
);

 date colum

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
)

tru​​ncate all data rows: 

alter table rcx truncate PARTIPARTION;

6 LINEAR HASH Partitioning

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')
);

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 ollos, wherem (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)))) Then LOG(2,13)​​ is 3.7004397181411. CEILING(3.7004397181411) is 4, and V = POWER(2,4), which is 16.)(2) Set Nlist = F(column_list) & (Vlumn_list).
(3)  While N >= num:     Set V = CEIL(V / 2)

    Set N = N & (V - 1)

[  把十進制轉化進製成二進制，就得到了 http://www.php.cn/

 先按右對齊，例如變成0011和1000，按照每一位的數字來判斷，如果兩個都是1，則結果的相應位置就是1，否則就是0

 如果是1011和1000，結果就是1000 如果是0110和1010，結果就是0010
 但是3是0011，8 是1000，所以3&8結果就是0 但是3是0011，8 是1000，所以3&8結果是0

不小於X 的最小整數值。

LOG(X) LOG(B,X) :若用一個參數調用，這個函數就會傳回X 的自然對數。
POWER(X,Y) : 傳回X 的Y乘方的結果值。

資料分佈在哪個片區的計算方法：

Suppose that the table t1, using linear hash partitioning and having 6 partitions, is created using this statementing and having 6 partitions, is created using this statement:1 infmmo🜥 ormement :aconl. having the col3 column values '2003-04-14' and '1998-10-19'. The partition number for the first of these is determined as follows:

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
)

The number of the partition where the second record is stored is calculated as shown here:

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')
);

The advantage in partitioning by linear hash is that the adding, dropping, merging, and splitting of partitions is that the adding, dropping, merging, and splitting of partitions is that much s, which 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.
分區之一：MySQL 如何用一條SQL，不需要用一個問題來刪除資料表？將分區表變成普通的表？

 以上就是MySQL 分區表 partition線上修改分區字段，後續進一步學習partition (1) 的內容，更多相關內容請關注PHPcn中文（www.php.cn）！