不論創(chuàng)建何種類型的分區(qū)，如果表中存在主鍵或唯一索引時，分區(qū)列必須是唯一索引的一個組成部分 mysql> create table t1( -> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2)) partition by hash(col3) partitions 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> create table t2( -> col1 int null, -> col2 date null, -> col3 int null, -> col4 int null -> ) engine=innodb -> partition by hash(col3) -> partitions 4; Query OK, 0 rows affected (0.40 sec) mysql> create table t3( -> col1 int null, -> col2 date null, -> col3 int null, -> col4 int null, -> key (col4) -> ) engine=innodb -> partition by hash(col3) -> partitions 4; Query OK, 0 rows affected (0.23 sec)
--查看數(shù)據(jù)庫是否支持分區(qū) MariaDB [test]> show plugins; +-------------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +-------------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | ..... | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +-------------------------------+----------+--------------------+---------+---------+
--范圍分區(qū) MariaDB [test]> CREATE TABLE members ( -> firstname VARCHAR(25) NOT NULL, -> lastname VARCHAR(25) NOT NULL, -> username VARCHAR(16) NOT NULL, -> email VARCHAR(35), -> joined DATE NOT NULL -> ) -> PARTITION BY RANGE COLUMNS(joined) ( -> PARTITION p0 VALUES LESS THAN ('1960-01-01'), -> PARTITION p1 VALUES LESS THAN ('1970-01-01'), -> PARTITION p2 VALUES LESS THAN ('1980-01-01'), -> PARTITION p3 VALUES LESS THAN ('1990-01-01'), -> PARTITION p4 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.45 sec)
MariaDB [test]> 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 NOT NULL, -> store_id INT NOT NULL -> ) -> PARTITION BY RANGE (store_id) ( -> PARTITION p0 VALUES LESS THAN (6), -> PARTITION p1 VALUES LESS THAN (11), -> PARTITION p2 VALUES LESS THAN (16), -> PARTITION p3 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.49 sec)
MariaDB [test]> show create table employees/G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '9999-12-31', `job_code` int(11) NOT NULL, `store_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)
按照年進(jìn)行分區(qū) mysql> create table sales( -> money int unsigned not null, -> date datetime -> ) engine=innodb -> partition by range (year(date)) ( -> partition p2008 values less than (2009), -> partition p2009 values less than (2010), -> partition p2010 values less than (2011) -> ); Query OK, 0 rows affected (0.31 sec)
優(yōu)化器只能對YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()這類函數(shù)進(jìn)行優(yōu)化選擇 下面這個例子中的分區(qū)創(chuàng)建有問題，在分區(qū)掃描的時候會掃描多個分區(qū) 按照每年每月來進(jìn)行分區(qū) mysql> create table sales2( -> money int unsigned not null, -> date datetime -> ) engine=innodb -> partition by range (year(date)*100+month(date)) ( -> partition p201001 values less than (201002), -> partition p201002 values less than (201003), -> partition p201003 values less than (201004) -> ); Query OK, 0 rows affected (0.20 sec)
