MySQL普通表如何轉(zhuǎn)換成分區(qū)表
更新時(shí)間：2022年05月30日 16:11:04 作者：牛牛的筆記
分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān),提高數(shù)據(jù)庫的效率,通常點(diǎn)來講就是提高表的增刪改查效率,下面這篇文章主要給大家介紹了關(guān)于MySQL普通表如何轉(zhuǎn)換成分區(qū)表的相關(guān)資料,需要的朋友可以參考下
版本:
MySQL-5.7.32
前言:
對于業(yè)務(wù)繁忙的數(shù)據(jù)庫來說，在運(yùn)行了一定時(shí)間后，往往會產(chǎn)生一些數(shù)據(jù)量較大的表，特別是對于每天新增數(shù)據(jù)較多的日志表或者流水表，大表對于日常的運(yùn)維非常的不方便，特別是數(shù)據(jù)的清理、遷移，表的訪問性能也會隨著數(shù)據(jù)量的增大而受到影響，因此，對于大表我們需要進(jìn)行優(yōu)化拆分，通常拆分的方案有
所以，通常選擇分區(qū)表改造方案的主要原因都是為了避免應(yīng)用層面的改造，對應(yīng)用層面透明，以及方便日常的運(yùn)維，前提是表具備改造分區(qū)條件。
改造分區(qū)前期條件:
參考文檔
1 根據(jù)業(yè)務(wù)的場景以及數(shù)據(jù)的分布，確認(rèn)是否有匹配的分區(qū)表類型以及分區(qū)列
- 對于日志表，流水表這種按日期類型進(jìn)行操作的，可以選擇進(jìn)行range分區(qū)
- 對于按用戶ID類型的進(jìn)行操作的，可以選擇進(jìn)行hash，key分區(qū)
- 對于按渠道，類型的進(jìn)行操作的，可以選擇進(jìn)行l(wèi)ist分區(qū)
2 應(yīng)用涉及的sql，需要90%以上的操作都包含分區(qū)列，按分區(qū)操作，如果sql沒有包含分區(qū)條件，掃描全分區(qū)，性能會出現(xiàn)下降。
查詢表sql操作歷史
select db,query,exec_count from sys.x$statement_analysis where lower(query) like '%%'order by exec_count;
3 主鍵必須包含分區(qū)鍵
4 分區(qū)鍵表達(dá)式只支持部分函數(shù)，存儲過程，不支持二級制操作符以及/
5 分區(qū)不支持外鍵
查詢表外鍵
select * from information_schema.KEY_COLUMN_USAGE where constraint_schema ='' and REFERENCED_TABLE_SCHEMA is not null\G
6 不支持查詢緩存
7 5.7版本單個(gè)表分區(qū)最大支持8192個(gè)，并且會話第一次訪問分區(qū)表，都需要打開全部的分區(qū)表，所以避免建立過多的分區(qū)
8 數(shù)據(jù)庫最大文件打開數(shù)open_files_limit要設(shè)置足夠大以滿足表，分區(qū)的打開數(shù)量
9 數(shù)據(jù)庫大文件large_files_support設(shè)置為on
10 分區(qū)列支持null值(對于rang分區(qū)，null值總小于任何的一個(gè)非null值，即存放在最左邊的分區(qū);對于list分區(qū)需要顯示指定null值條件)，但從數(shù)據(jù)管理以及規(guī)范來看，不建議分區(qū)列存放null值，并且如果表有主鍵，則分區(qū)列不能為null值，因?yàn)榉謪^(qū)列需要作為主鍵的一部分，不能為null
12 MySQL目前沒有自動(dòng)分區(qū)功能，所以需要監(jiān)控分區(qū)的使用情況，通過job自動(dòng)或者定時(shí)手動(dòng)添加新分區(qū)
13 確定數(shù)據(jù)保留期限，定期歸檔分區(qū)數(shù)據(jù)
分區(qū)改造案例:
以下是一張數(shù)據(jù)量為766萬的大表xxxx_user. xxxx_tab,計(jì)劃將其改造為范圍分區(qū)，按月存放。
select table_schema,table_name,table_rows,data_length/1024/1024/1024 from information_schema.tables where table_name='xxxx_tab';
表結(jié)構(gòu)
CREATE TABLE `xxxx_tab` ( `ROLE_SEQ` bigint(20) NOT NULL , `PRD_ID` varchar(64) NOT NULL , `MAKE_RIGHT` varchar(1) DEFAULT '0' , `CHECK_RIGHT` varchar(1) DEFAULT '0' , `AUTH_RIGHT` varchar(1) DEFAULT '0' , `AUTH_GROUP` varchar(4) DEFAULT NULL , `RELEASE_RIGHT` varchar(1) DEFAULT '0' , `CREATE_USER_SEQ` bigint(20) DEFAULT NULL , `CREATE_DEPT_SEQ` bigint(20) DEFAULT NULL , `CREATE_TIME` datetime DEFAULT NULL , `UPDATE_USER_SEQ` bigint(20) DEFAULT NULL , `UPDATE_DEPT_SEQ` bigint(20) DEFAULT NULL , `UPDATE_TIME` datetime DEFAULT NULL , PRIMARY KEY (`PRD_ID`,`ROLE_SEQ`), KEY `xxxx_tab_IDX01` (`ROLE_SEQ`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
分區(qū)列create_time日期最大值，最小值，根據(jù)這個(gè)范圍按月創(chuàng)建分區(qū)
select max(CREATE_TIME),min(CREATE_TIME) from xxxx_tab;
分區(qū)列null值，對于存在的null值，需要應(yīng)用對null數(shù)據(jù)進(jìn)行處理，并且程序上需要確保數(shù)據(jù)寫入not null
select count(*) from xxxx_tab where create_time is null;
主鍵重建添加分區(qū)列
alter table xxxx_tab drop primary key,add primary key (`PRD_ID`,`ROLE_SEQ`,`CREATE_TIME`);
由于主鍵沒有包含分區(qū)列，需要重建主鍵添加分區(qū)列，對于主鍵重建我采取的是官方的online ddl功能，這種ddl操作會造成主從延時(shí)，但是不會產(chǎn)生大量的binlog，對于主從實(shí)時(shí)性要求高的，可以采用第三方的在線工具pt-osc,gh-ost
表轉(zhuǎn)化為分區(qū)表
采用pt-osc在線將表轉(zhuǎn)化為分區(qū)表，對于partition by 官方是不支持online ddl的，所以需要采用第三方的在線工具
./pt-online-schema-change --user=xxx --password=xxx --charset=utf8 D=xxxx_user,t=xxxx_tab --alter "PARTITION BY RANGE COLUMNS(CREATE_TIME)
(PARTITION p200001 VALUES LESS THAN ('2000-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p200101 VALUES LESS THAN ('2001-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p201707 VALUES LESS THAN ('2017-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p201708 VALUES LESS THAN ('2017-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p201709 VALUES LESS THAN ('2017-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p201710 VALUES LESS THAN ('2017-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p201711 VALUES LESS THAN ('2017-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p201712 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p201801 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p201802 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p201803 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p201804 VALUES LESS THAN ('2018-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p201805 VALUES LESS THAN ('2018-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p201806 VALUES LESS THAN ('2018-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p201807 VALUES LESS THAN ('2018-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p201808 VALUES LESS THAN ('2018-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p201809 VALUES LESS THAN ('2018-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p201810 VALUES LESS THAN ('2018-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p201811 VALUES LESS THAN ('2018-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p201812 VALUES LESS THAN ('2019-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p201901 VALUES LESS THAN ('2019-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p201902 VALUES LESS THAN ('2019-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p201903 VALUES LESS THAN ('2019-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p201904 VALUES LESS THAN ('2019-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p201905 VALUES LESS THAN ('2019-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p201906 VALUES LESS THAN ('2019-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p201907 VALUES LESS THAN ('2019-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p201908 VALUES LESS THAN ('2019-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p201909 VALUES LESS THAN ('2019-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p201910 VALUES LESS THAN ('2019-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p201911 VALUES LESS THAN ('2019-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p201912 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202001 VALUES LESS THAN ('2020-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202002 VALUES LESS THAN ('2020-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202003 VALUES LESS THAN ('2020-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202004 VALUES LESS THAN ('2020-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202005 VALUES LESS THAN ('2020-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202006 VALUES LESS THAN ('2020-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202007 VALUES LESS THAN ('2020-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202008 VALUES LESS THAN ('2020-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202009 VALUES LESS THAN ('2020-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202010 VALUES LESS THAN ('2020-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202011 VALUES LESS THAN ('2020-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202012 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202101 VALUES LESS THAN ('2021-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202102 VALUES LESS THAN ('2021-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202103 VALUES LESS THAN ('2021-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202104 VALUES LESS THAN ('2021-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202105 VALUES LESS THAN ('2021-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202106 VALUES LESS THAN ('2021-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202107 VALUES LESS THAN ('2021-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202108 VALUES LESS THAN ('2021-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202109 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202110 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202111 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202112 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202201 VALUES LESS THAN ('2022-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202202 VALUES LESS THAN ('2022-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202203 VALUES LESS THAN ('2022-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202204 VALUES LESS THAN ('2022-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202205 VALUES LESS THAN ('2022-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202206 VALUES LESS THAN ('2022-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202207 VALUES LESS THAN ('2022-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202208 VALUES LESS THAN ('2022-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202209 VALUES LESS THAN ('2022-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202210 VALUES LESS THAN ('2022-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202211 VALUES LESS THAN ('2022-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202212 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202301 VALUES LESS THAN ('2023-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202302 VALUES LESS THAN ('2023-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202303 VALUES LESS THAN ('2023-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202304 VALUES LESS THAN ('2023-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202305 VALUES LESS THAN ('2023-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202306 VALUES LESS THAN ('2023-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)" --recursion-method hosts --max-lag 600 --nodrop-old-table --print --statistics --execute分區(qū)后表模型
CREATE TABLE `xxxx_tab` (
`ROLE_SEQ` bigint(20) NOT NULL ,
`PRD_ID` varchar(64) NOT NULL ,
`MAKE_RIGHT` varchar(1) DEFAULT '0' ,
`CHECK_RIGHT` varchar(1) DEFAULT '0' ,
`AUTH_RIGHT` varchar(1) DEFAULT '0' ,
`AUTH_GROUP` varchar(4) DEFAULT NULL ,
`RELEASE_RIGHT` varchar(1) DEFAULT '0' ,
`CREATE_USER_SEQ` bigint(20) DEFAULT NULL ,
`CREATE_DEPT_SEQ` bigint(20) DEFAULT NULL ,
`CREATE_TIME` datetime NOT NULL ,
`UPDATE_USER_SEQ` bigint(20) DEFAULT NULL ,
`UPDATE_DEPT_SEQ` bigint(20) DEFAULT NULL ,
`UPDATE_TIME` datetime DEFAULT NULL ,
PRIMARY KEY (`PRD_ID`,`ROLE_SEQ`,`CREATE_TIME`),
KEY `xxxx_tab_IDX01` (`ROLE_SEQ`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(CREATE_TIME)
(PARTITION p200001 VALUES LESS THAN ('2000-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p200101 VALUES LESS THAN ('2001-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p201707 VALUES LESS THAN ('2017-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p201708 VALUES LESS THAN ('2017-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p201709 VALUES LESS THAN ('2017-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p201710 VALUES LESS THAN ('2017-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p201711 VALUES LESS THAN ('2017-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p201712 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p201801 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p201802 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p201803 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p201804 VALUES LESS THAN ('2018-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p201805 VALUES LESS THAN ('2018-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p201806 VALUES LESS THAN ('2018-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p201807 VALUES LESS THAN ('2018-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p201808 VALUES LESS THAN ('2018-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p201809 VALUES LESS THAN ('2018-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p201810 VALUES LESS THAN ('2018-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p201811 VALUES LESS THAN ('2018-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p201812 VALUES LESS THAN ('2019-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p201901 VALUES LESS THAN ('2019-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p201902 VALUES LESS THAN ('2019-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p201903 VALUES LESS THAN ('2019-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p201904 VALUES LESS THAN ('2019-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p201905 VALUES LESS THAN ('2019-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p201906 VALUES LESS THAN ('2019-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p201907 VALUES LESS THAN ('2019-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p201908 VALUES LESS THAN ('2019-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p201909 VALUES LESS THAN ('2019-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p201910 VALUES LESS THAN ('2019-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p201911 VALUES LESS THAN ('2019-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p201912 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202001 VALUES LESS THAN ('2020-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202002 VALUES LESS THAN ('2020-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202003 VALUES LESS THAN ('2020-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202004 VALUES LESS THAN ('2020-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202005 VALUES LESS THAN ('2020-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202006 VALUES LESS THAN ('2020-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202007 VALUES LESS THAN ('2020-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202008 VALUES LESS THAN ('2020-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202009 VALUES LESS THAN ('2020-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202010 VALUES LESS THAN ('2020-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202011 VALUES LESS THAN ('2020-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202012 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202101 VALUES LESS THAN ('2021-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202102 VALUES LESS THAN ('2021-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202103 VALUES LESS THAN ('2021-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202104 VALUES LESS THAN ('2021-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202105 VALUES LESS THAN ('2021-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202106 VALUES LESS THAN ('2021-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202107 VALUES LESS THAN ('2021-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202108 VALUES LESS THAN ('2021-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202109 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202110 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202111 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202112 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202201 VALUES LESS THAN ('2022-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202202 VALUES LESS THAN ('2022-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202203 VALUES LESS THAN ('2022-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202204 VALUES LESS THAN ('2022-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202205 VALUES LESS THAN ('2022-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202206 VALUES LESS THAN ('2022-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202207 VALUES LESS THAN ('2022-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202208 VALUES LESS THAN ('2022-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202209 VALUES LESS THAN ('2022-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202210 VALUES LESS THAN ('2022-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202211 VALUES LESS THAN ('2022-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202212 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p202301 VALUES LESS THAN ('2023-02-01 00:00:00') ENGINE = InnoDB,
PARTITION p202302 VALUES LESS THAN ('2023-03-01 00:00:00') ENGINE = InnoDB,
PARTITION p202303 VALUES LESS THAN ('2023-04-01 00:00:00') ENGINE = InnoDB,
PARTITION p202304 VALUES LESS THAN ('2023-05-01 00:00:00') ENGINE = InnoDB,
PARTITION p202305 VALUES LESS THAN ('2023-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p202306 VALUES LESS THAN ('2023-07-01 00:00:00') ENGINE = InnoDB,
PARTITION p202307 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
PARTITION p202308 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
PARTITION p202309 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
PARTITION p202310 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
PARTITION p202311 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)總結(jié)
到此這篇關(guān)于MySQL普通表如何轉(zhuǎn)換成分區(qū)表的文章就介紹到這了,更多相關(guān)MySQL普通表轉(zhuǎn)分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL用truncate命令快速清空一個(gè)數(shù)據(jù)庫中的所有表
這篇文章主要介紹了MySQL用truncate命令快速清空一個(gè)數(shù)據(jù)庫中的所有表，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
mySQL count多個(gè)表的數(shù)據(jù)實(shí)例詳解
這篇文章通過實(shí)例給大家介紹了mySQL中count多個(gè)表的數(shù)據(jù)，也就是多個(gè)表如何聯(lián)合查詢，文中通過項(xiàng)目中遇到的一個(gè)問題進(jìn)行分析和實(shí)現(xiàn)，給出了詳細(xì)的示例代碼，相信對大家的理解和學(xué)習(xí)很有幫助，有需要的朋友們下面來一起看看吧。2016-11-11
centos上安裝mysql并設(shè)置遠(yuǎn)程訪問的操作方法
這篇文章主要介紹了centos上安裝mysql并設(shè)置遠(yuǎn)程訪問的操作方法,需要的朋友可以參考下2017-11-11
這篇文章主要介紹了詳解MySQL的內(nèi)連接和外連接,mySQL包含兩種聯(lián)接，分別是內(nèi)連接(inner join)和外連接(out join),但我們又同時(shí)聽說過左連接，交叉連接等術(shù)語,本文就帶大家來了解一下,需要的朋友可以參考下2023-05-05
云服務(wù)器安裝mysql的實(shí)現(xiàn)步驟
在當(dāng)前的互聯(lián)網(wǎng)時(shí)代,云計(jì)算已經(jīng)成為重要的平臺支撐,本文就來介紹一下云服務(wù)器安裝mysql的實(shí)現(xiàn)步驟,具有一定的參考價(jià)值,感興趣的可以了解一下2024-01-01
這篇文章主要給大家介紹了關(guān)于RR與RC隔離級別下索引和鎖的測試腳本的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-12-12
這篇文章主要介紹了MySQL的driverClassName與url使用方式，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-01-01
MySQL數(shù)據(jù)歸檔小工具mysql_archiver詳解
這篇文章主要介紹了MySQL數(shù)據(jù)歸檔小工具mysql_archiver詳解,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-12-12
MySQL主從復(fù)制的原理及配置方法（比較詳細(xì)）
MySQL 的數(shù)據(jù)庫的高可用性的架構(gòu)大概有以下幾種：集群，讀寫分離，主備。而后面兩種都是通過復(fù)制來實(shí)現(xiàn)的。下面將簡單介紹復(fù)制的原理及配置，以及一些常見的問題2014-05-05
這篇文章主要介紹了linux mint 下mysql中文支持問題的相關(guān)資料,需要的朋友可以參考下2015-10-10