分区 · 宇飞林海

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;