本来利用了函数month对数据表进行了按月的分区,现在想改成按周分区,直接用如下命令能否可行?mysql> CREATE TABLE netxuning (
-> id INT NOT NULL,
-> mydate DATE
-> )
-> PARTITION BY RANGE (YEARWEEK(mydate)) (
-> PARTITION p0 VALUES LESS THAN (200901),
-> PARTITION p1 VALUES LESS THAN (200902),
-> PARTITION p2 VALUES LESS THAN (200906),
-> PARTITION p3 VALUES LESS THAN (200953)
-> );如果不行,如何解决这样规划分区的问题?
-> id INT NOT NULL,
-> mydate DATE
-> )
-> PARTITION BY RANGE (YEARWEEK(mydate)) (
-> PARTITION p0 VALUES LESS THAN (200901),
-> PARTITION p1 VALUES LESS THAN (200902),
-> PARTITION p2 VALUES LESS THAN (200906),
-> PARTITION p3 VALUES LESS THAN (200953)
-> );如果不行,如何解决这样规划分区的问题?
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> CREATE TABLE netxuning (
-> id INT NOT NULL,
-> mydate DATE
-> ) engine=myisam
-> PARTITION BY RANGE (YEAR(mydate)*100+MONTH(mydate)) (
-> PARTITION p0 VALUES LESS THAN (200901),
-> PARTITION p2 VALUES LESS THAN (200906),
-> PARTITION p3 VALUES LESS THAN (201001)
-> );
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> insert into netxuning values
-> (1,'2009-07-03'),
-> (1,'2009-08-03'),
-> (1,'2009-09-03'),
-> (1,'2009-10-03'),
-> (1,'2009-11-03'),
-> (1,'2009-12-03');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> select id,mydate,yearweek(mydate) from netxuning;
+----+------------+------------------+
| id | mydate | yearweek(mydate) |
+----+------------+------------------+
| 1 | 2009-07-03 | 200926 |
| 1 | 2009-08-03 | 200931 |
| 1 | 2009-09-03 | 200935 |
| 1 | 2009-10-03 | 200939 |
| 1 | 2009-11-03 | 200944 |
| 1 | 2009-12-03 | 200948 |
+----+------------+------------------+
6 rows in set (0.00 sec)mysql>[code=BatchFile]C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>dir ne*
Volume in drive C is xxx
Volume Serial Number is xxx Directory of C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn12/24/2009 11:56a 0 netxuning#P#p0.MYD
12/24/2009 11:56a 1,024 netxuning#P#p0.MYI
12/24/2009 11:56a 0 netxuning#P#p2.MYD
12/24/2009 11:56a 1,024 netxuning#P#p2.MYI
12/24/2009 11:56a 48 netxuning#P#p3.MYD
12/24/2009 11:56a 1,024 netxuning#P#p3.MYI
12/24/2009 11:56a 8,590 netxuning.frm
12/24/2009 11:56a 32 netxuning.par
8 File(s) 11,742 bytes
0 Dir(s) 24,259,038,208 bytes free[/code]
mysql> alter TABLE netxuning
-> PARTITION BY RANGE (YEARWEEK(mydate)) (
-> PARTITION w0 VALUES LESS THAN (200901),
-> PARTITION w1 VALUES LESS THAN (200920),
-> PARTITION w2 VALUES LESS THAN (200940),
-> PARTITION w3 VALUES LESS THAN (200953)
-> );
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql>可见修改已经成功。
[code=BatchFile]C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>dir ne*
Volume in drive C is xx
Volume Serial Number is xx Directory of C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn12/24/2009 12:00p 0 netxuning#P#w0.MYD
12/24/2009 12:00p 1,024 netxuning#P#w0.MYI
12/24/2009 12:00p 0 netxuning#P#w1.MYD
12/24/2009 12:00p 1,024 netxuning#P#w1.MYI
12/24/2009 12:00p 32 netxuning#P#w2.MYD
12/24/2009 12:00p 1,024 netxuning#P#w2.MYI
12/24/2009 12:00p 16 netxuning#P#w3.MYD
12/24/2009 12:00p 1,024 netxuning#P#w3.MYI
12/24/2009 12:00p 8,590 netxuning.frm
12/24/2009 12:00p 32 netxuning.par
10 File(s) 12,766 bytes
0 Dir(s) 24,259,026,944 bytes freeC:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>[/code]
1,将源表改名如: netxuning_bak
2,采用周分区的方式创建表netxuning
3,从netxuning_bak 将数据插入 netxuning
4,删除netxuning_bak