MYSQL分区表功能测试简析(3)
来源：未知 责任编辑：责任编辑 发表时间:2015-09-16 20:04 点击:次
mysql> create table ptime6(id int,createdate datetime) engine=myisam partition by range (year(createdate))
(partition po values less than (2010),partition p1 values less than (2011));
Query OK, 0 rows affected (0.01 sec) www.2cto.com
使用年函数也可以分区。
7.Mysql可用的分区函数
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
WEEKDAY()
YEAR()
YEARWEEK() 等
当然，还有FLOOR（），CEILING() 等，前提是使用这两个分区函数的分区健必须是整型。
要小心使用其中的一些函数，避免犯逻辑性的错误，引起全表扫描。
比如：
create table ptime11(id int,createdate datetime) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));
mysql> insert into ptime11 values (1,'2010-06-17');
mysql> explain partitions select count(1) from ptime11 where createdate>'2010-08-17'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ptime11
partitions: po,p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)
8.主键及约束测试
分区健不包含在主键内
mysql> create table pprimary(id int,createdate datetime,primary key(id)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31)); www.2cto.com
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
分区健包含在主键内
mysql> create table pprimary1(id int,createdate datetime,primary key(id,createdate)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));
Query OK, 0 rows affected (0.05 sec)
说明分区健必须包含在主键里面。
mysql> create table pprimary2(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(uid)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));
相关新闻>>
- 发表评论
-
- 最新评论 进入详细评论页>>
