Mysql使用小技巧总结

# 1.创建带分区的表
DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名 (
date_id date DEFAULT NULL COMMENT '日期',
字段二 varchar(100) DEFAULT NULL)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( TO_DAYS(date_str) ) (
PARTITION p20220711 VALUES LESS THAN (738712),
PARTITION p20220712 VALUES LESS THAN (738713),
PARTITION p20220713 VALUES LESS THAN (738714)
);

# 2.增加分区，注意数字与新建时匹配
ALTER TABLE 表名 ADD PARTITION (PARTITION p20220815 VALUES LESS THAN (TO_DAYS ('2022-08-16')));
ALTER TABLE 表名 ADD PARTITION (PARTITION p20220816 VALUES LESS THAN (TO_DAYS ('2022-08-17')));

# 3.删除分区
删除分区：
ALTER TABLE 表名 DROP PARTITION p20180718;

# 4.取group by后数据的分组前2
select date_id,msisdn,"home" as area_type from user_resident a where (se