mysql list分区表_MySQL分区表例子——List分区

╰+哭是因爲堅強的太久メ 2023-01-12 01:58 5阅读 0赞

这里假设表中有一个sale\_item\_type 字段，数据类型为INT 型

当sale\_item\_type 为1，3，5的时候，作为一个分区

当sale\_item\_type 为2，4，6的时候，作为另一个分区

当sale\_item\_type 为7，8的时候，作为最后一个分区

mysql> CREATE TABLE sale\_data2 (

\->   sale\_date  DATETIME NOT NULL,

\->   sale\_item\_type  INT NOT NULL ,

\->   sale\_money DECIMAL(10,2) NOT NULL

\-> )

\-> PARTITION BY LIST (sale\_item\_type) (

\->   PARTITION pFoods VALUES IN (1,3,5),

\->   PARTITION pSmoke VALUES IN (2,4,6),

\->   PARTITION pDrink VALUES IN (7,8)

\-> );

Query OK, 0 rows affected (0.11 sec)

核对结果

mysql> SHOW CREATE TABLE sale\_data2\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: sale\_data2

Create Table: CREATE TABLE \`sale\_data2\` (

\`sale\_date\` datetime NOT NULL,

\`sale\_item\_type\` int(11) NOT NULL,

\`sale\_money\` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/\*!50100 PARTITION BY LIST (sale\_item\_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB) \*/

1 row in set (0.00 sec)

mysql> ALTER TABLEsale\_data2

\->   ADD PARTITION(PARTITION pOther VALUES IN (9, 10));

Query OK, 0 rows affected (0.23 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale\_data2\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: sale\_data2

Create Table: CREATE TABLE \`sale\_data2\` (

\`sale\_date\` datetime NOT NULL,

\`sale\_item\_type\` int(11) NOT NULL,

\`sale\_money\` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/\*!50100 PARTITION BY LIST (sale\_item\_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB,

PARTITION pOther VALUES IN (9,10) ENGINE = InnoDB) \*/

1 row in set (0.00 sec)

mysql> ALTER TABLE sale\_data2

\->   DROP PARTITION pOther;

Query OK, 0 rows affected (0.34 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale\_data2\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: sale\_data2

Create Table: CREATE TABLE \`sale\_data2\` (

\`sale\_date\` datetime NOT NULL,

\`sale\_item\_type\` int(11) NOT NULL,

\`sale\_money\` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/\*!50100 PARTITION BY LIST (sale\_item\_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB) \*/

1 row in set (0.00 sec)

下面的SQL将pDrink 拆分为pBeer 与pCola

mysql> ALTER TABLE sale\_data2 REORGANIZE PARTITION pDrink INTO (

\->     PARTITION pBeer VALUES IN (7),

\->     PARTITION pCola VALUES IN (8)

\-> );

Query OK, 0 rows affected (0.39 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale\_data2\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: sale\_data2

Create Table: CREATE TABLE \`sale\_data2\` (

\`sale\_date\` datetime NOT NULL,

\`sale\_item\_type\` int(11) NOT NULL,

\`sale\_money\` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/\*!50100 PARTITION BY LIST (sale\_item\_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pBeer VALUES IN (7) ENGINE = InnoDB,

PARTITION pCola VALUES IN (8) ENGINE = InnoDB) \*/

1 row in set (0.00 sec)

下面的SQL，将pBeer,pCola合并到pDrink

mysql> ALTER TABLE sale\_data2

\->   REORGANIZE PARTITION pBeer,pCola INTO (

\->     PARTITION pDrink VALUES IN (7,8)

\-> );

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale\_data2\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: sale\_data2

Create Table: CREATE TABLE \`sale\_data2\` (

\`sale\_date\` datetime NOT NULL,

\`sale\_item\_type\` int(11) NOT NULL,

\`sale\_money\` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/\*!50100 PARTITION BY LIST (sale\_item\_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB) \*/

1 row in set (0.00 sec)

mysql list分区表_MySQL分区表例子——List分区

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL分区表

相关 mysql list分区表_MySQL分区表例子——List分区

相关 mysql 分区表测试_MYSQL表分区测试

相关 mysql 分区表优化_MySQL分区表(优化)

相关 mysql 表分区查看表分区修改表分区

相关 mysql分区表简单例子

相关 MySQL表分区

相关 MySQL分区表

相关 mysql分区表---list partition

相关 mysql分区表