https://jackxiang.com/index.php zh-cn https://jackxiang.com/post// jack <xdy108@126.com> Sat, 23 Jan 2010 06:11:19 +0000 https://jackxiang.com/post// /* with MySQL 5.1  ivan @ MySQL实验室(mysqlab.net/blog/) */
CREATE TABLE mysqlab_net
(
  ivan DATE
)
PARTITION BY RANGE (TO_DAYS(ivan))
(
  PARTITION p01 VALUES LESS THAN (TO_DAYS(‘2007-08-08′)),
  PARTITION p02 VALUES LESS THAN (TO_DAYS(‘2008-08-08′)),
  PARTITION p03 VALUES LESS THAN (TO_DAYS(‘2009-08-08′)),
  PARTITION p04 VALUES LESS THAN (MAXVALUE));

SHOW CREATE TABLE mysqlab_net\G
*************************** 1. row ***************************
       TABLE: mysqlab_net
CREATE TABLE: CREATE TABLE `mysqlab_net` (
  `ivan` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(ivan))
(PARTITION p01 VALUES LESS THAN (733261) ENGINE = InnoDB,
PARTITION p02 VALUES LESS THAN (733627) ENGINE = InnoDB,
PARTITION p03 VALUES LESS THAN (733992) ENGINE = InnoDB,
PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

怎么样？读取的时候谁知道那个数字是多少？(不过也可以通过自定义函数实现还原)
MySQL5.5中加入了columns关键字，使得可读性好多了。看例子
/* with MySQL 5.5   ivan @ MySQL实验室(mysqlab.net/blog/) */
CREATE TABLE `mysqlab.net`
(
  ivan DATE
)
PARTITION BY RANGE  COLUMNS(ivan)
(
  PARTITION p01 VALUES LESS THAN (‘2007-08-08′),
  PARTITION p02 VALUES LESS THAN (‘2008-08-08′),
  PARTITION p03 VALUES LESS THAN (‘2009-08-08′),
  PARTITION p04 VALUES LESS THAN (MAXVALUE);

SHOW CREATE TABLE `mysqlab.net`\G
*************************** 1. row ***************************
       TABLE: mysqlab.net
CREATE TABLE: CREATE TABLE `mysqlab.net` (
  `ivan` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(ivan)
(PARTITION p01 VALUES LESS THAN (’2007-08-08′) ENGINE = InnoDB,
PARTITION p02 VALUES LESS THAN (’2008-08-08′) ENGINE = InnoDB,
PARTITION p03 VALUES LESS THAN (’2009-08-08′) ENGINE = InnoDB,
PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

另外MySQL5.5表分区(partition) columns关键字还支持多字段，比如 partition by range columns(a,b);将支持清空指定的分区TRUNCATE PARTITION。MySQL5.5有望在明年(2010)夏季GA。另外MySQL5.5支持的半同步功能在高可用上的使用，让人非常期待！ ]]> https://jackxiang.com/post//#blogcomment <user@domain.com> Thu, 01 Jan 1970 00:00:00 +0000 https://jackxiang.com/post//#blogcomment