分區 · 宇飛林海

SELECT \* FROM INFORMATION\_SCHEMA.partitions WHERE TABLE\_SCHEMA = schema() AND TABLE\_NAME='logo\_logo\_history'; SELECT \* FROM `logo\_logo\_history` PARTITION(p201904) where username='SnPlHJiV'; ***** 創建表 CREATE TABLE `logo\_logo\_history` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `logo\_id` int(11) NOT NULL DEFAULT '0', `title` varchar(45) NOT NULL DEFAULT '' COMMENT '主標題', `slogan` varchar(45) NOT NULL DEFAULT '' COMMENT '副標題', `industry\_id` tinyint(45) NOT NULL DEFAULT '0' COMMENT '0-互聯網 1-電子商務 2-餐飲食品 3-金融 4-電子器械 5-房地產 6-醫療健康 7-教育培訓 8-法律 9-文化創意 10-農林畜牧 11-其他行業', `description` varchar(255) NOT NULL DEFAULT '' COMMENT '描述', `color\_num` varchar(255) NOT NULL DEFAULT '' COMMENT '色系 0-綠色 1-橙色 2-藍色 3-黃色 4-黑白 5-咖色 6-金色 7-紅色 8-紫色 9-不限制', `style\_ids` varchar(45) NOT NULL DEFAULT '' COMMENT '風格（保留字段）', `creat\_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '創建時間', `show\_path` varchar(255) NOT NULL DEFAULT '' COMMENT '圖標展示的png path 做base64處理', `oss\_path` varchar(255) NOT NULL DEFAULT '' COMMENT '真正的png地址', `proto\_pay\_level` varchar(10) NOT NULL DEFAULT '' COMMENT 'ai傳過來數據(free三級 basic二級 expert一級)', `proto\_id` varchar(255) NOT NULL DEFAULT '' COMMENT '對應mongo版型庫的id', `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-版型未被使用 1-版型正在被下單鎖定 2-版型已被使用所以該素材不能用了', `mid` int(11) NOT NULL COMMENT '用戶id', `is\_register` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否被下單(無關支付) 0---沒有 1--有', `is\_buy` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0--未被買 1--該logo已被買', `is\_collect` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0--未被收藏 1--已被收藏', `is\_share` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否分享', `batch\_number` varchar(255) NOT NULL DEFAULT '' COMMENT 'logo生成頁的鏈接尾數', `bottom\_color` varchar(10) NOT NULL DEFAULT '' COMMENT '底色', `batch\_page` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logo生成頁第幾頁', `collection` varchar(255) NOT NULL DEFAULT '' COMMENT '素材的數據庫名', `proto\_down` tinyint(4) NOT NULL DEFAULT '0' COMMENT '素材原型是否下架 0 不用下架 1--下架 ', `proto\_lock` tinyint(4) NOT NULL DEFAULT '0' COMMENT '素材是否被鎖住 (被添加進訂單未支付) 0 --- 未鎖 1--已鎖', `delete` int(2) NOT NULL DEFAULT '0' COMMENT '0未刪除,1已刪除', `banxing` varchar(255) NOT NULL DEFAULT '' COMMENT '版型', `zan` int(11) NOT NULL DEFAULT '0' COMMENT '點贊數', `slogen\_font` varchar(255) DEFAULT NULL COMMENT '副標題字體信息', `title\_font` varchar(255) DEFAULT NULL COMMENT '主標題的字體信息', `update\_time` int(10) NOT NULL DEFAULT '0' COMMENT '更新時間', `is\_history\_logo` tinyint(4) DEFAULT '0' COMMENT '是否是歷史logo', `back\_color` tinyint(2) DEFAULT NULL COMMENT '是否背景色', `is\_case` tinyint(2) DEFAULT '0' COMMENT '0 不是案例 1是案例', `czy\_file\_id` int(11) DEFAULT NULL COMMENT '存證云hash對應的file\_id', `file\_hash\_code` varchar(255) DEFAULT NULL COMMENT '文件哈希值', `width` float(10,2) DEFAULT NULL COMMENT '寬', `height` float(10,2) DEFAULT NULL COMMENT '高', `insert\_time` int(11) NOT NULL DEFAULT '0' COMMENT '寫入時間', PRIMARY KEY (`id`,`creat\_time`), KEY `idx\_logo\_id` (`logo\_id`) USING BTREE, KEY `idx\_mid` (`mid`) USING BTREE, KEY `idx\_is\_buy` (`is\_buy`) USING BTREE, KEY `idx\_is\_collect` (`is\_collect`) USING BTREE, KEY `idx\_proto\_id` (`proto\_id`) USING BTREE, KEY `idx\_batch\_number` (`batch\_number`) USING BTREE, KEY `idx\_collection` (`collection`) USING BTREE, KEY `idx\_is\_register` (`is\_register`) USING BTREE, KEY `idx\_is\_share` (`is\_share`) USING BTREE, KEY `idx\_is\_case` (`is\_case`) USING BTREE, KEY `idx\_creat\_time` (`creat\_time`) USING BTREE, KEY `idx\_title` (`title`) USING BTREE, KEY `idx\_delete` (`delete`) USING BTREE ) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='logo設計歸檔庫' PARTITION BY RANGE (`creat\_time`) ( PARTITION p201809 VALUES LESS THAN (1538323200), PARTITION p201810 VALUES LESS THAN (1541001600), PARTITION p201811 VALUES LESS THAN (1543593600), PARTITION p201812 VALUES LESS THAN (1546272000), PARTITION p201901 VALUES LESS THAN (1548950400), PARTITION p201902 VALUES LESS THAN (1551369600), PARTITION p201903 VALUES LESS THAN (1554048000), PARTITION p201904 VALUES LESS THAN (1556640000), PARTITION p201905 VALUES LESS THAN (1559318400), PARTITION p201906 VALUES LESS THAN (1561910400), PARTITION p201907 VALUES LESS THAN (1564588800), PARTITION p201908 VALUES LESS THAN (1567267200) ); ***** 開始 delimiter $$ CREATE EVENT `logo\_history\_job` ON SCHEDULE EVERY 1 MONTH STARTS '2018-09-01 01:00:00' ON COMPLETION PRESERVE ENABLE COMMENT '每個月對表進行下個月分區' DO BEGIN SET @part\_name = CONCAT("p", DATE\_FORMAT(DATE\_ADD(NOW(), INTERVAL 1 MONTH), '%Y%m')); SET @less\_time = UNIX\_TIMESTAMP(DATE\_FORMAT(DATE\_ADD(NOW(), INTERVAL 2 MONTH), '%Y-%m-01 00:00:00')); SET @part\_create = CONCAT("ALTER TABLE `logo\_history` PARTITION BY RANGE COLUMNS (`cretat\_time`) (PARTITION ", @part\_name," VALUES LESS THAN (", @less\_time,"))"); PREPARE part\_create FROM @part\_create; EXECUTE part\_create; END$$ delimiter ; ALTER TABLE `logo\_history` PARTITION BY RANGE COLUMNS (`cretat\_time`) (PARTITION p201909 VALUES LESS THAN (1569859200)) CREATE TABLE T1 ( id int(8) NOT NULL AUTO\_INCREMENT, createtime datetime NOT NULL, PRIMARY KEY (id,createtime) ) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8 PARTITION BY RANGE(TO\_DAYS (createtime)) ( PARTITION p0 VALUES LESS THAN (TO\_DAYS('201901')), PARTITION p1 VALUES LESS THAN (TO\_DAYS('2010-05-01')), PARTITION p2 VALUES LESS THAN (TO\_DAYS('2010-05-15')), PARTITION p3 VALUES LESS THAN (TO\_DAYS('2010-05-31')), PARTITION p4 VALUES LESS THAN (TO\_DAYS('2010-06-15')), PARTITION p19 VALUES LESS ThAN MAXVALUE); truncate `tbl\_user\_no\_part` CREATE TABLE `tbl\_user\_no\_part` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create\_time` int(11) DEFAULT NULL COMMENT '創建時間', PRIMARY KEY (`id`,`create\_time`) ) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8; PARTITION BY RANGE (create\_time) ( PARTITION p201901 VALUES LESS THAN (1548950400), PARTITION p201902 VALUES LESS THAN (1551369600), PARTITION p201903 VALUES LESS THAN (1554048000), PARTITION p201904 VALUES LESS THAN (1556640000), PARTITION p201905 VALUES LESS THAN (1559318400), PARTITION p201906 VALUES LESS THAN (1561910400), PARTITION p201907 VALUES LESS THAN (1564588800) ); \-- 修改mysql默認的結束符號，默認是分號；但是在函數和存儲過程中會使用到分號導致解析不正確 delimiter $$ \-- 隨機生成一個指定長度的字符串 create function rand\_string(n int) returns varchar(255) begin \# 定義三個變量 declare chars\_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return\_str varchar(255) default ''; declare i int default 0; while i < n do set return\_str = concat(return\_str, substring(chars\_str, floor(1+rand()\*52), 1)); set i = i + 1; end while; return return\_str; end $$ \-- 創建插入的存儲過程 create procedure insert\_user(in start int(10), in max\_num int(10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into tbl\_user\_no\_part values ((start+i) ,rand\_string(8), concat(rand\_string(6), '@random.com'), 1+FLOOR(RAND()\*100), 3, unix\_timestamp(CONCAT('2019-0',FLOOR(RAND()\*6+1),'-', FLOOR(RAND() \* 27 + 1), ' ', FLOOR(RAND() \* 24), ':', FLOOR(RAND() \* 59), ':', FLOOR(RAND() \* 59)))); until i = max\_num end repeat; commit; end $$ \-- 將命令結束符修改回來 delimiter ; \-- 調用存儲過程，插入500萬數據，需要等待一會時間，等待執行完成 call insert\_user(1,10); CREATE TABLE `tbl\_user\_no\_part` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create\_time` int(11) NOT NULL COMMENT '創建時間', PRIMARY KEY (`id`,`create\_time`), KEY `idx\_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (create\_time) ( PARTITION p201901 VALUES LESS THAN (1548950400), PARTITION p201902 VALUES LESS THAN (1551369600), PARTITION p201903 VALUES LESS THAN (1554048000), PARTITION p201904 VALUES LESS THAN (1556640000), PARTITION p201905 VALUES LESS THAN (1559318400), PARTITION p201906 VALUES LESS THAN (1561910400), PARTITION p201907 VALUES LESS THAN (1564588800) ); //查詢分區有多少個 SELECT \* FROM INFORMATION\_SCHEMA.partitions WHERE TABLE\_SCHEMA = schema() AND TABLE\_NAME='tbl\_user\_no\_part'; SELECT \* FROM `tbl\_user\_no\_part` PARTITION(p201904) where username='SnPlHJiV'; ***** CREATE TABLE `tbl\_user\_no\_part` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create\_time` timestamp DEFAULT CURRENT\_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8; \-- 修改mysql默認的結束符號，默認是分號；但是在函數和存儲過程中會使用到分號導致解析不正確 delimiter $$ \-- 隨機生成一個指定長度的字符串 create function rand\_string(n int) returns varchar(255) begin \# 定義三個變量 declare chars\_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return\_str varchar(255) default ''; declare i int default 0; while i < n do set return\_str = concat(return\_str, substring(chars\_str, floor(1+rand()\*52), 1)); set i = i + 1; end while; return return\_str; end $$ \-- 創建插入的存儲過程 create procedure insert\_user(in start int(10), in max\_num int(10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into tbl\_user\_no\_part values ((start+i) ,rand\_string(8), concat(rand\_string(6), '@random.com'), 1+FLOOR(RAND()\*100), 3, now()); until i = max\_num end repeat; commit; end $$ \-- 將命令結束符修改回來 delimiter ; \-- 調用存儲過程，插入500萬數據，需要等待一會時間，等待執行完成 call insert\_user(100001,5000000); \--------------------- 作者：vbirdbest 來源：CSDN 原文：https://blog.csdn.net/vbirdbest/article/details/82461109 版權聲明：本文為博主原創文章，轉載請附上博文鏈接！ alter table user\_test PARTITION BY RANGE (age) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (50), PARTITION p3 VALUES LESS THAN MAXVALUE ); ALTER TABLE 表名 DROP PARTITION 分區名; //查看分區數據 explain partitions select \* from user\_test; //查詢個個分區的具體情況 SELECT \* FROM INFORMATION\_SCHEMA.partitions WHERE TABLE\_SCHEMA = schema() AND TABLE\_NAME='user\_test'; ![](C:/Users/Administrator/AppData/Local/YNote/data/qq687938EA3FCF98736CBB3342CDC12736/a8531373a96c4cebbda0e93ed4826f34/clipboard.png) table\_rows 分區的數量 SELECT \* FROM INFORMATION\_SCHEMA.partitions WHERE TABLE\_SCHEMA = schema() AND TABLE\_NAME='tbl\_user\_no\_part'; SELECT \* FROM `tbl\_user\_no\_part` PARTITION(p201904) where username='SnPlHJiV'; //測試例子 alter table user\_test PARTITION BY RANGE (age) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30), PARTITION p3 VALUES LESS THAN (40), PARTITION p4 VALUES LESS THAN (50), PARTITION p5 VALUES LESS THAN MAXVALUE ); 刪除分區 ALTER TABLE user\_test DROP PARTITION p0; ALTER TABLE user\_test DROP PARTITION p1; ALTER TABLE user\_test DROP PARTITION p2; CREATE TABLE `user\_test` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) NOT NULL DEFAULT '0', `type` int(11) DEFAULT NULL, `create\_time` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP, PRIMARY KEY (`id`,`age`), KEY `username` (`username`) USING BTREE ) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8; drop table user\_test; call insert\_user\_test(1,2000000); CREATE TABLE T1 ( ? ? ?id int(8) NOT NULL AUTO\_INCREMENT, ? ? ?createtime datetime NOT NULL, ? ? ? PRIMARY KEY (id,createtime) ) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8 PARTITION BY RANGE(TO\_DAYS (createtime)) ( PARTITION p0 VALUES LESS THAN (TO\_DAYS('2010-04-15')), PARTITION p1 VALUES LESS THAN (TO\_DAYS('2010-05-01')), PARTITION p2 VALUES LESS THAN (TO\_DAYS('2010-05-15')), PARTITION p3 VALUES LESS THAN (TO\_DAYS('2010-05-31')), PARTITION p4 VALUES LESS THAN (TO\_DAYS('2010-06-15')), PARTITION p19 VALUES LESS ThAN ?MAXVALUE); SELECT count(\*) FROM `user\_test` WHERE age >10 and age < 20;