MySQL分区建表例子-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_46150074/article/details/144564137
以下为MySQL分区建表的例子

CREATE TABLE `mz_mjzcfxxmx` (
  `SERIALNUM_ID` varchar(96) NOT NULL COMMENT '业务角度唯一性ID',
  `DATAGENERATE_DATE` datetime NOT NULL COMMENT '业务数据产生时间',
  `ROW_ID` varchar(300) DEFAULT NULL COMMENT '数据角度唯一性ID',
  `TASK_ID` varchar(96) DEFAULT NULL COMMENT '任务号',
  `BATCH_NUM` varchar(96) DEFAULT NULL COMMENT '批次号',
  `LOCAL_ID` varchar(150) DEFAULT NULL COMMENT '人员唯一标识',
  `BUSINESS_ID` varchar(600) DEFAULT NULL COMMENT '业务流水号',
  `BASIC_ACTIVE_ID` varchar(600) DEFAULT NULL COMMENT '业务系统的逻辑主键或联合主键',
  `DOMAIN_CODE` varchar(36) DEFAULT NULL COMMENT '接入系统编码',
  `ORGANIZATION_CODE` varchar(66) DEFAULT NULL COMMENT '组织机构代码',
  `ORGANIZATION_NAME` varchar(600) DEFAULT NULL COMMENT '组织机构名称',
  `CREATE_DATE` varchar(42) DEFAULT NULL COMMENT '数据落库时间',
  `IS_ERROR` varchar(6) DEFAULT NULL COMMENT '数据质控时是否有错',
  `OUT_IN_ID` varchar(600) DEFAULT NULL COMMENT '门（急）诊（住院）号',
  `DENOMINATOR_RULE` longtext COMMENT '符合规则起效条件',
  `ERROR_RULE` longtext COMMENT '错误规则信息',
  `RECEIVE_DATE` varchar(42) DEFAULT NULL COMMENT '数据接收时间',
  `ARCHIVE_DATE` varchar(42) DEFAULT NULL COMMENT '归档日期时间',
  `RESOURCE_ID` longtext COMMENT '资源信息ID',
  `YLJGDM` varchar(66) DEFAULT NULL COMMENT '医疗机构代码',
  `CFMXID` varchar(150) DEFAULT NULL COMMENT '处方明细ID',
  `YLJGMC` varchar(210) DEFAULT NULL COMMENT '医疗机构名称',
  `GRBSH` varchar(108) DEFAULT NULL COMMENT '个人标识号',
  `CFZID` varchar(150) DEFAULT NULL COMMENT '处方主ID',
  `JZLSH` varchar(150) DEFAULT NULL COMMENT '就诊流水号',
  `CFZH` varchar(150) DEFAULT NULL COMMENT '处方组号',
  `ZTDM` varchar(60) DEFAULT NULL COMMENT '组套代码',
  `ZTMC` varchar(300) DEFAULT NULL COMMENT '组套名称',
  `XMDM` varchar(60) DEFAULT NULL COMMENT '项目代码',
  `XMMC` longtext COMMENT '项目名称',
  `FYFLDM` varchar(6) DEFAULT NULL COMMENT '费用分类代码',
  `FYFLMC` varchar(60) DEFAULT NULL COMMENT '费用分类名称',
  `YPBZ` varchar(3) DEFAULT NULL COMMENT '药品标志',
  `YPGG` varchar(150) DEFAULT NULL COMMENT '药品规格（新）',
  `YPJXDM` varchar(6) DEFAULT NULL COMMENT '药品剂型代码-新',
  `YPJXMC` varchar(240) DEFAULT NULL COMMENT '药品剂型名称-新',
  `YPSYCJL` varchar(31) DEFAULT NULL COMMENT '药品使用次剂量-新',
  `YPSYCJLDW` varchar(18) DEFAULT NULL COMMENT '药品使用次剂量单位',
  `YPSYZJL` varchar(37) DEFAULT NULL COMMENT '药品使用总剂量-新',
  `YPSYPCDM` varchar(6) DEFAULT NULL COMMENT '药品使用频次代码-新',
  `YPSYPCMC` varchar(30) DEFAULT NULL COMMENT '药品使用频次名称-新',
  `YYTS` varchar(31) DEFAULT NULL COMMENT '用药天数',
  `YWLXDM` varchar(12) DEFAULT NULL COMMENT '药物类型代码',
  `YWLXMC` varchar(150) DEFAULT NULL COMMENT '药物类型名称',
  `YYTJDM` varchar(9) DEFAULT NULL COMMENT '预约途径代码（用药途径代码）',
  `YYTJMC` varchar(60) DEFAULT NULL COMMENT '预约途径名称(用药途径名称)',
  `FYSL` varchar(31) DEFAULT NULL COMMENT '发药数量',
  `FYSLDW` varchar(150) DEFAULT NULL COMMENT '发药数量单位',
  `CFKSSJ` varchar(42) DEFAULT NULL COMMENT '处方开始时间',
  `CFTZSJ` varchar(42) DEFAULT NULL COMMENT '处方停止时间',
  `ZXKSDM` varchar(60) DEFAULT NULL COMMENT '执行科室代码',
  `ZXKSMC` varchar(150) DEFAULT NULL COMMENT '执行科室名称',
  `YZZXDM` varchar(3) DEFAULT NULL COMMENT '医嘱执行状态代码',
  `YZZXMC` varchar(150) DEFAULT NULL COMMENT '医嘱执行状态名称',
  `ZYYPCF` longtext COMMENT '中药饮片处方',
  `ZYYPJS` varchar(13) DEFAULT NULL COMMENT '中药饮片剂数',
  `ZYYPJZFF` varchar(300) DEFAULT NULL COMMENT '中药饮片煎煮方法',
  `ZYYYTJDM` varchar(9) DEFAULT NULL COMMENT '中药用药途径代码',
  `ZYYYTJMC` varchar(300) DEFAULT NULL COMMENT '中药用药途径名称',
  `ZZZF` varchar(300) DEFAULT NULL COMMENT '治则治法',
  `DPBZ` varchar(3) DEFAULT NULL COMMENT '点评标志',
  `YYSM` longtext COMMENT '用药说明',
  `BZ` longtext COMMENT '其他备注说明',
  `CYJZ` longtext COMMENT '草药脚注',
  `ZYBS` varchar(3) DEFAULT NULL COMMENT '主药标识',
  `JJBS` varchar(3) DEFAULT NULL COMMENT '加急标识',
  `PSCZY` varchar(150) DEFAULT NULL COMMENT '皮试操作员',
  `PSJLRQSJ` varchar(42) DEFAULT NULL COMMENT '皮试结论日期时间',
  `PSPB` varchar(3) DEFAULT NULL COMMENT '皮试判别',
  `PSJG` varchar(48) DEFAULT NULL COMMENT '皮试结果（新）',
  `MJ` varchar(6) DEFAULT NULL COMMENT '密级',
  `TBRQ` varchar(42) DEFAULT NULL COMMENT '填报日期（新）',
  `XGRQ` varchar(42) DEFAULT NULL COMMENT '最后更新时间（新）',
  `XGBZ` varchar(3) DEFAULT NULL COMMENT '修改标志',
  `rowkey` varchar(570) NOT NULL DEFAULT '' COMMENT '兼容IHBE的记录唯一性主键',
  PRIMARY KEY (`SERIALNUM_ID`,`DATAGENERATE_DATE`),
  KEY `mz_mjzcfxxmx_DATAGENERATE_DATE_IDX` (`DATAGENERATE_DATE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='自治区1-门急诊处方信息明细'
 PARTITION BY RANGE (((year(`DATAGENERATE_DATE`) * 100) + month(`DATAGENERATE_DATE`)))
(PARTITION p202001 VALUES LESS THAN (202002) ENGINE = InnoDB,
 PARTITION p202002 VALUES LESS THAN (202003) ENGINE = InnoDB,
 PARTITION p202003 VALUES LESS THAN (202004) ENGINE = InnoDB,
 PARTITION p202004 VALUES LESS THAN (202005) ENGINE = InnoDB,
 PARTITION p202005 VALUES LESS THAN (202006) ENGINE = InnoDB,
 PARTITION p202006 VALUES LESS THAN (202007) ENGINE = InnoDB,
 PARTITION p202007 VALUES LESS THAN (202008) ENGINE = InnoDB,
 PARTITION p202008 VALUES LESS THAN (202009) ENGINE = InnoDB,
 PARTITION p202009 VALUES LESS THAN (202010) ENGINE = InnoDB,
 PARTITION p202010 VALUES LESS THAN (202011) ENGINE = InnoDB,
 PARTITION p202011 VALUES LESS THAN (202012) ENGINE = InnoDB,
 PARTITION p202012 VALUES LESS THAN (202101) ENGINE = InnoDB,
 PARTITION p202101 VALUES LESS THAN (202102) ENGINE = InnoDB,
 PARTITION p202102 VALUES LESS THAN (202103) ENGINE = InnoDB,
 PARTITION p202103 VALUES LESS THAN (202104) ENGINE = InnoDB,
 PARTITION p202104 VALUES LESS THAN (202105) ENGINE = InnoDB,
 PARTITION p202105 VALUES LESS THAN (202106) ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN (202107) ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN (202108) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (202109) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (202110) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (202111) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (202112) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (202201) ENGINE = InnoDB,
 PARTITION p202201 VALUES LESS THAN (202202) ENGINE = InnoDB,
 PARTITION p202202 VALUES LESS THAN (202203) ENGINE = InnoDB,
 PARTITION p202203 VALUES LESS THAN (202204) ENGINE = InnoDB,
 PARTITION p202204 VALUES LESS THAN (202205) ENGINE = InnoDB,
 PARTITION p202205 VALUES LESS THAN (202206) ENGINE = InnoDB,
 PARTITION p202206 VALUES LESS THAN (202207) ENGINE = InnoDB,
 PARTITION p202207 VALUES LESS THAN (202208) ENGINE = InnoDB,
 PARTITION p202208 VALUES LESS THAN (202209) ENGINE = InnoDB,
 PARTITION p202209 VALUES LESS THAN (202210) ENGINE = InnoDB,
 PARTITION p202210 VALUES LESS THAN (202211) ENGINE = InnoDB,
 PARTITION p202211 VALUES LESS THAN (202212) ENGINE = InnoDB,
 PARTITION p202212 VALUES LESS THAN (202301) ENGINE = InnoDB,
 PARTITION p202301 VALUES LESS THAN (202302) ENGINE = InnoDB,
 PARTITION p202302 VALUES LESS THAN (202303) ENGINE = InnoDB,
 PARTITION p202303 VALUES LESS THAN (202304) ENGINE = InnoDB,
 PARTITION p202304 VALUES LESS THAN (202305) ENGINE = InnoDB,
 PARTITION p202305 VALUES LESS THAN (202306) ENGINE = InnoDB,
 PARTITION p202306 VALUES LESS THAN (202307) ENGINE = InnoDB,
 PARTITION p202307 VALUES LESS THAN (202308) ENGINE = InnoDB,
 PARTITION p202308 VALUES LESS THAN (202309) ENGINE = InnoDB,
 PARTITION p202309 VALUES LESS THAN (202310) ENGINE = InnoDB,
 PARTITION p202310 VALUES LESS THAN (202311) ENGINE = InnoDB,
 PARTITION p202311 VALUES LESS THAN (202312) ENGINE = InnoDB,
 PARTITION p202312 VALUES LESS THAN (202401) ENGINE = InnoDB,
 PARTITION p202401 VALUES LESS THAN (202402) ENGINE = InnoDB,
 PARTITION p202402 VALUES LESS THAN (202403) ENGINE = InnoDB,
 PARTITION p202403 VALUES LESS THAN (202404) ENGINE = InnoDB,
 PARTITION p202404 VALUES LESS THAN (202405) ENGINE = InnoDB,
 PARTITION p202405 VALUES LESS THAN (202406) ENGINE = InnoDB,
 PARTITION p202406 VALUES LESS THAN (202407) ENGINE = InnoDB,
 PARTITION p202407 VALUES LESS THAN (202408) ENGINE = InnoDB,
 PARTITION p202408 VALUES LESS THAN (202409) ENGINE = InnoDB,
 PARTITION p202409 VALUES LESS THAN (202410) ENGINE = InnoDB,
 PARTITION p202410 VALUES LESS THAN (202411) ENGINE = InnoDB,
 PARTITION p202411 VALUES LESS THAN (202412) ENGINE = InnoDB,
 PARTITION p202412 VALUES LESS THAN (202501) ENGINE = InnoDB,
 PARTITION p202501 VALUES LESS THAN (202502) ENGINE = InnoDB,
 PARTITION p202502 VALUES LESS THAN (202503) ENGINE = InnoDB,
 PARTITION p202503 VALUES LESS THAN (202504) ENGINE = InnoDB,
 PARTITION p202504 VALUES LESS THAN (202505) ENGINE = InnoDB,
 PARTITION p202505 VALUES LESS THAN (202506) ENGINE = InnoDB,
 PARTITION p202506 VALUES LESS THAN (202507) ENGINE = InnoDB,
 PARTITION p202507 VALUES LESS THAN (202508) ENGINE = InnoDB,
 PARTITION p202508 VALUES LESS THAN (202509) ENGINE = InnoDB,
 PARTITION p202509 VALUES LESS THAN (202510) ENGINE = InnoDB,
 PARTITION p202510 VALUES LESS THAN (202511) ENGINE = InnoDB,
 PARTITION p202511 VALUES LESS THAN (202512) ENGINE = InnoDB,
 PARTITION p202512 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) ;