MySQL普通表如何轉(zhuǎn)換成分區(qū)表
更新時間：2022年05月30日 16:11:04 作者：牛牛的筆記
分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān),提高數(shù)據(jù)庫的效率,通常點來講就是提高表的增刪改查效率,下面這篇文章主要給大家介紹了關(guān)于MySQL普通表如何轉(zhuǎn)換成分區(qū)表的相關(guān)資料,需要的朋友可以參考下
版本:
MySQL-5.7.32
前言:
對于業(yè)務(wù)繁忙的數(shù)據(jù)庫來說，在運(yùn)行了一定時間后，往往會產(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版本單個表分區(qū)最大支持8192個，并且會話第一次訪問分區(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值總小于任何的一個非null值，即存放在最左邊的分區(qū);對于list分區(qū)需要顯示指定null值條件)，但從數(shù)據(jù)管理以及規(guī)范來看，不建議分區(qū)列存放null值，并且如果表有主鍵，則分區(qū)列不能為null值，因為分區(qū)列需要作為主鍵的一部分，不能為null
12 MySQL目前沒有自動分區(qū)功能，所以需要監(jiān)控分區(qū)的使用情況，通過job自動或者定時手動添加新分區(qū)
13 確定數(shù)據(jù)保留期限，定期歸檔分區(qū)數(shù)據(jù)
分區(qū)改造案例:
以下是一張數(shù)據(jù)量為766萬的大表xxxx_user. xxxx_tab,計劃將其改造為范圍分區(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ù)這個范圍按月創(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操作會造成主從延時，但是不會產(chǎn)生大量的binlog，對于主從實時性要求高的，可以采用第三方的在線工具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 中去除小數(shù)點后面多余的0的方法 ,需要的朋友可以參考下2014-03-03
這篇文章主要給大家介紹了關(guān)于MySQL復(fù)合索引的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
SQLyog錯誤號碼MySQL?plugin?caching_sha2_password?could?not?
這篇文章主要介紹了SQLyog錯誤號碼?plugin?caching_sha2_password?could?not?be?loaded的解決方法,需要的朋友可以參考下2023-06-06
詳細(xì)分析mysql MDL元數(shù)據(jù)鎖
這篇文章主要介紹了mysql MDL元數(shù)據(jù)鎖的相關(guān)資料，文中講解非常細(xì)致，代碼幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-08-08
這篇文章主要為大家詳細(xì)介紹了RedHat6.5下MySQL5.7的安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03
在MySQL中,可以通過使用EXPLAIN語句來分析SQL查詢是否成功使用了索引,本文給大家介紹了使用EXPLAIN語句分析SQL語句是否成功使用索引的步驟,需要的朋友可以參考下2023-12-12
MySQL 5.0以后的版本開始支持存儲過程,存儲過程具有一致性、高效性、安全性和體系結(jié)構(gòu)等特點,本文主要來分享下本人關(guān)于存儲過程的一些心得體會。2014-07-07
mysql 一個較特殊的問題：You can''t specify target table ''wms_cabinet
mysql 一個較特殊的問題：You can't specify target table 'wms_cabinet_form' for update in F2010-11-11
SQLyog連接不上mysql問題的解決方法(按照步驟，包解決)
這篇文章主要介紹了SQLyog連接不上mysql問題的解決方法,文中給大家分析了SQLyog連接不上mysql的幾種原因,并通過圖文結(jié)合的方式給大家講解的非常詳細(xì),需要的朋友可以參考下2024-03-03
本文主要介紹了MySQL 查看庫中大表的幾種方法,為了識別可能影響數(shù)據(jù)庫性能的表,下面主要了4種方式,具有一定的參考價值,感興趣的可以了解一下2024-04-04