mysql将普通表转为分区表 - zhangwuji的博客 - 91名师指路91mszl.com-让能力变成价值

原 mysql将普通表转为分区表

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1443

由于某些原因，现在不支持支付宝支付，如需要购买源码请加博主微信进行购买，微信号：13248254750

需求：

1）在创建表的时候直接将表设置为分区表

2）将普通表转为分区表

我们这里是根据年进行分区，每一个年份一张表

表分区类型：

1）RANGE表分区：范围表分区，按照⼀定的范围值来确定每个分区包含的数据
2）LIST表分区：列表表分区，按照⼀个⼀个确定的值来确定每个分区包含的数据
3）HASH表分区：哈希表分区，按照⼀个⾃定义的函数返回值来确定每个分区包含的数据
4）KEY表分区：key表分区，与哈希表分区类似，只是⽤MySQL⾃⼰的HASH函数来确定每个分区包含的数据

一：创建表的时候直接设置为分区（这里是RANGE分区）

CREATE TABLE `d_test`  (
  `id` bigint(32) NOT NULL AUTO_INCREMENT,
  `date_year` int(11) NOT NULL,
  `date_month` varchar(7) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `doc_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `vendor_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_all_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_all_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_product_value` float(10, 2) NULL DEFAULT NULL,
  `org_product_price` float NULL DEFAULT NULL,
  `create_id` bigint(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`, `date_year`) USING BTREE,
  INDEX `idx_d_data_org_product_month_org`(`date_month`, `vendor_no`, `org_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 37633860 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '组织产品数据表' ROW_FORMAT = DYNAMIC PARTITION BY RANGE (date_year)
PARTITIONS 24
(PARTITION `P_2017` VALUES LESS THAN (2017) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2018` VALUES LESS THAN (2018) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2019` VALUES LESS THAN (2019) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2020` VALUES LESS THAN (2020) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2021` VALUES LESS THAN (2021) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2022` VALUES LESS THAN (2022) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2023` VALUES LESS THAN (2023) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2024` VALUES LESS THAN (2024) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2025` VALUES LESS THAN (2025) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2026` VALUES LESS THAN (2026) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2027` VALUES LESS THAN (2027) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2028` VALUES LESS THAN (2028) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2029` VALUES LESS THAN (2029) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2030` VALUES LESS THAN (2030) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2031` VALUES LESS THAN (2031) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2032` VALUES LESS THAN (2032) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2033` VALUES LESS THAN (2033) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2034` VALUES LESS THAN (2034) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2035` VALUES LESS THAN (2035) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2036` VALUES LESS THAN (2036) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2037` VALUES LESS THAN (2037) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2038` VALUES LESS THAN (2038) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2039` VALUES LESS THAN (2039) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2040` VALUES LESS THAN (2040) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 )
;

SET FOREIGN_KEY_CHECKS = 1;

二：将普通表转为分区表（之前创建的是普通表，由于业务增长，现在需要进行分区）

2.1）创建普通表

CREATE TABLE `d_test`  (
  `id` bigint(32) NOT NULL AUTO_INCREMENT,
  `date_year` int(11) NULL DEFAULT NULL,
  `date_month` varchar(7) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `doc_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `vendor_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_all_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_all_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_product_value` float(10, 2) NULL DEFAULT NULL,
  `create_id` bigint(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_d_data_org_product_month_org`(`date_month`, `vendor_no`, `org_no`) USING BTREE,
  INDEX `idx_d_data_org_product_month_product`(`date_month`, `vendor_no`, `product_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10001 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '组织产品表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.2）将普通表转为分区表（这里是RANGE分区）。

ALTER TABLE d_test PARTITION BY RANGE (date_year)(
    PARTITION P_2017 VALUES LESS THAN(2017),
    PARTITION P_2018 VALUES LESS THAN(2018),
    PARTITION P_2019 VALUES LESS THAN(2019),
    PARTITION P_2020 VALUES LESS THAN(2020),
    PARTITION P_2021 VALUES LESS THAN(2021),
    PARTITION P_2022 VALUES LESS THAN(2022),
    PARTITION P_2023 VALUES LESS THAN(2023),
    PARTITION P_2024 VALUES LESS THAN(2024),
    PARTITION P_2025 VALUES LESS THAN(2025),
    PARTITION P_2026 VALUES LESS THAN(2026),
    PARTITION P_2027 VALUES LESS THAN(2027),
    PARTITION P_2028 VALUES LESS THAN(2028),
    PARTITION P_2029 VALUES LESS THAN(2029),
    PARTITION P_2030 VALUES LESS THAN(2030),
    PARTITION P_2031 VALUES LESS THAN(2031),
    PARTITION P_2032 VALUES LESS THAN(2032),
    PARTITION P_2033 VALUES LESS THAN(2033),
    PARTITION P_2034 VALUES LESS THAN(2034),
    PARTITION P_2035 VALUES LESS THAN(2035),
    PARTITION P_2036 VALUES LESS THAN(2036),
    PARTITION P_2037 VALUES LESS THAN(2037),
    PARTITION P_2038 VALUES LESS THAN(2038),
    PARTITION P_2039 VALUES LESS THAN(2039),
    PARTITION P_2040 VALUES LESS THAN(2040)
);

注意：分区的字段必须要设置为主键。

三：查验是否设置分区表成功

select * from information_schema.partitions where table_name='d_test';

四：查看某个分区表中的数据

SELECT * FROM d_test PARTITION (P_2018);

五：将分区表变成普通表

方法一（推荐）：

1）先备份d_test，需要注意，这种方式不会把d_test的主键和索引给复制过去，需要再次手动创建

create table d_test_bak select * from d_test

2）然后删除d_test，在将d_test_bak名称更改为d_test即可，这种方式基本没有风险

方法二：

1）备份d_test放在删除分区表的时候出现问题，方便数据回滚

create table d_test_bak select * from d_test

2）将分区表更改为普通表

ALTER TABLE d_test REMOVE PARTITIONING

六：LIST分区的语法如下

6.1）创建表的时候直接设置为分区（这里是LIST分区）

CREATE TABLE `d_test`  (
  `id` bigint(32) NOT NULL AUTO_INCREMENT,
  `date_year` int(11) NOT NULL,
  `date_month` varchar(7) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `doc_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `vendor_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_all_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_all_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `org_product_value` float(10, 2) NULL DEFAULT NULL,
  `org_product_price` float NULL DEFAULT NULL,
  `create_id` bigint(11) NOT NULL,
  `create_time` datetime(0) NOT NULL,
  PRIMARY KEY (`id`, `date_year`) USING BTREE,
  INDEX `idx_d_data_org_product_month_org`(`date_month`, `vendor_no`, `org_no`) USING BTREE,
  INDEX `idx_d_data_org_product_month_product`(`date_month`, `vendor_no`, `product_no`) USING BTREE,
  INDEX `idx_d_data_org_product_month`(`date_month`) USING BTREE,
  INDEX `idx_d_data_org_product_month_vendor_no`(`vendor_no`) USING BTREE,
  INDEX `idx_d_data_org_product_month_product_name`(`date_month`, `vendor_no`, `product_all_name`) USING BTREE,
  INDEX `idx_d_data_org_product_month_doc_no`(`date_month`, `vendor_no`, `doc_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 38353709 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic PARTITION BY LIST (date_year)
PARTITIONS 24
(PARTITION `P_2017` VALUES IN (2017) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2018` VALUES IN (2018) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2019` VALUES IN (2019) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2020` VALUES IN (2020) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2021` VALUES IN (2021) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2022` VALUES IN (2022) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2023` VALUES IN (2023) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2024` VALUES IN (2024) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2025` VALUES IN (2025) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2026` VALUES IN (2026) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2027` VALUES IN (2027) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2028` VALUES IN (2028) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2029` VALUES IN (2029) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2030` VALUES IN (2030) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2031` VALUES IN (2031) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2032` VALUES IN (2032) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2033` VALUES IN (2033) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2034` VALUES IN (2034) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2035` VALUES IN (2035) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2036` VALUES IN (2036) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2037` VALUES IN (2037) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2038` VALUES IN (2038) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2039` VALUES IN (2039) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P_2040` VALUES IN (2040) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 )
;

SET FOREIGN_KEY_CHECKS = 1;

6.2）将普通表转为分区表（这里是LIST分区）

ALTER TABLE d_test PARTITION BY LIST (date_year)(
    PARTITION P_2017 VALUES IN(2017),
    PARTITION P_2018 VALUES IN(2018),
    PARTITION P_2019 VALUES IN(2019),
    PARTITION P_2020 VALUES IN(2020),
    PARTITION P_2021 VALUES IN(2021),
    PARTITION P_2022 VALUES IN(2022),
    PARTITION P_2023 VALUES IN(2023),
    PARTITION P_2024 VALUES IN(2024),
    PARTITION P_2025 VALUES IN(2025),
    PARTITION P_2026 VALUES IN(2026),
    PARTITION P_2027 VALUES IN(2027),
    PARTITION P_2028 VALUES IN(2028),
    PARTITION P_2029 VALUES IN(2029),
    PARTITION P_2030 VALUES IN(2030),
    PARTITION P_2031 VALUES IN(2031),
    PARTITION P_2032 VALUES IN(2032),
    PARTITION P_2033 VALUES IN(2033),
    PARTITION P_2034 VALUES IN(2034),
    PARTITION P_2035 VALUES IN(2035),
    PARTITION P_2036 VALUES IN(2036),
    PARTITION P_2037 VALUES IN(2037),
    PARTITION P_2038 VALUES IN(2038),
    PARTITION P_2039 VALUES IN(2039),
    PARTITION P_2040 VALUES IN(2040)
);

2023-07-13 13:32:44 阅读(242)

名师出品，必属精品 https://www.91mszl.com

联系博主

博主信息

昵称： zhangwuji
最新文章

1. spring-boot 3.2.3整合mybatis plus报错：java.lang.IllegalArgumentException: Invalid value type for attribu

2. spring-boot 3.2.3整合druid报错：Failed to configure a DataSource: 'url' attribute is not specified and no

3. 使用hutool实现姓名、身份证、手机号、地址、邮箱、车牌、银行卡脱敏

4. java集成ip2region实现离线IP地址定位

5. java验证邮箱是否可用

6. easyexcel（二十六）：easyexcel导出添加水印

7. java使用proguard对代码进行混淆

8. postgresql分组后只取其中一条数据

9. easyexcel（二十五）：easyexcel导出并设置密码