mysql分区表数据迁移方案 mysql分区交换

• 对分区表可以通过 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 命令将一个分区或者是子分区的数据与普通的表的数据相互交换，其本身的表结构不会变化
• 交换的分区表和目标表必须结构完全相同，包括字段，类型，索引，存储引擎必须完全一样

mysql> select * from tr;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.00 sec)

mysql> select * from tr_history;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
+------+------+------------+
1 row in set (0.00 sec)

mysql> select * from tr partition(p3);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.00 sec)

mysql> desc tr;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| name      | varchar(50) | YES  |     | NULL    |       |
| purchased | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc tr_history;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| name      | varchar(50) | YES  |     | NULL    |       |
| purchased | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tr_history modify name varchar(100);
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table tr EXCHANGE PARTITION p3 WITH TABLE tr_history;
ERROR 1736 (HY000): Tables have different definitions
mysql> alter table tr_history modify name varchar(50);
Query OK, 1 row affected (0.26 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table tr_history;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                   |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr_history | CREATE TABLE `tr_history` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create index idx_1 on tr_history(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tr EXCHANGE PARTITION p3 WITH TABLE tr_history;
ERROR 1736 (HY000): Tables have different definitions

• 执行 exchange 命令时，目标表里不一定是空数据，如果有数据需要保证里面的数据符合表分区的条件，否则只能用 WITHOUT VALIDATION 来跳过验证环节

mysql> select * from tr_history;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
+------+------+------------+
1 row in set (0.00 sec)

mysql> show create table tr;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr    | CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table tr EXCHANGE PARTITION p1 WITH TABLE tr_history;
ERROR 1736 (HY000): Tables have different definitions
mysql> drop index idx_1 on tr_history;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tr EXCHANGE PARTITION p1 WITH TABLE tr_history;
ERROR 1737 (HY000): Found a row that does not match the partition
mysql> alter table tr EXCHANGE PARTITION p1 WITH TABLE tr_history WITHOUT VALIDATION;
Query OK, 0 rows affected (0.31 sec)

mysql> select * from tr partition(p1);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
+------+------+------------+
1 row in set (0.01 sec)

mysql> select * from tr;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
3 rows in set (0.00 sec)

mysql> show create table tr;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr    | CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tr wherepurchased='2010-01-01';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='2010-01-01'' at line 1
mysql> explain select * from tr where purchased='2010-01-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tr    | p5         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tr;
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tr    | p1,p3,p4,p5 | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tr where name='a';
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tr    | p1,p3,p4,p5 | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)