存储过程查看：select name from mysql.proc where db = 'dbname' and type = 'PROCEDURE';
自动创建分区字段存储过程：
代码语言：javascript复制DELIMITER ||
drop procedure if exists auto_create_partitions ||
create procedure auto_create_partitions (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
L_END:
begin
declare max_partition_description varchar(255) default '';
declare p_name varchar(255) default 0;
declare p_description varchar(255) default 0;
declare isexist_partition varchar(255) default 0;
declare i int default 1;
select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename and table_name = tablename limit 1;
if isexist_partition <=> "" then
select "partition table not is exist" as "ERROR";
leave L_END;
end if;
select partition_description into max_partition_description from information_schema.partitions where table_schema = databasename and table_name = tablename order by partition_description desc limit 1;
if max_partition_description <=> "" then
select "partition table is error" as "ERROR";
leave L_END;
end if;
while (i <= partition_number) do
if (partitiontype = 0) then
set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day);
elseif (partitiontype = 1) then
set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month);
else
set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
end if;
set p_name = REPLACE(p_description, ' ', '');
set p_name = REPLACE(p_name, '-', '');
set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS('', p_description ,'')))');
select @sql;
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set i = (i 1);
end while;
end; ||
DELIMITER ||-- 查看分区的执行计划
explain partitions select * from table_name where start_time>DATE_FORMAT(now(),'%Y-%m-%d') and start_time<DATE_FORMAT(date_add(now(), interval 1 day),'%Y-%m-%d');
date_format( date_add(now(), interval -1 hour) , '%Y-%m-%d %h' )
-- 删除分区
alter table table_name drop partition p0;
-- 删除事件
DROP EVENT [IF EXISTS] auto_set_partitions;
-- 查看事件状态
show variables like 'event_scheduler';
-- 查询事件是否启动
SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event_scheduler';
-- 开启事件
SET GLOBAL event_scheduler = ON;
-- 查询事件
SELECT * FROM information_schema.events limit 1G;数据库查看每个库的磁盘使用情况
代码语言：javascript复制SELECT
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',
sum(truncate(DATA_FREE/1024/1024, 2)) as '碎片占用(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;数据库每张表的容量大小
代码语言：javascript复制SELECT
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)',
truncate(DATA_FREE/1024/1024, 2) as '碎片占用(MB)'
from
information_schema.tables
where
table_schema='kalacloud_test_data'
order by
data_length desc, index_length desc;-- 查看分区的执行计划
explain partitions select * from table_name where start_time>DATE_FORMAT(now(),'%Y-%m-%d') and start_time<DATE_FORMAT(date_add(now(), interval 1 day),'%Y-%m-%d');
date_format( date_add(now(), interval -1 hour) , '%Y-%m-%d %h' )
-- 删除分区
alter table table_name drop partition p0;
-- 删除事件
DROP EVENT [IF EXISTS] auto_set_partitions;
-- 查看事件状态
show variables like 'event_scheduler';
-- 查询事件是否启动
SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event_scheduler';
-- 开启事件
SET GLOBAL event_scheduler = ON;
-- 查询事件
SELECT * FROM information_schema.events limit 1G;
ALTER TABLE table_name MODIFY colname varchar(128) DEFAULT '';
-- 创建数据库设置编码
CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 增加字段
ALTER TABLE table_name ADD COLUMN colname VARCHAR(64) NOT NULL COMMENT '计量子类' AFTER dosage_type;
-- 删除唯一性索引
drop index serialId_index on table_name;
-- 创建唯一性索引
create unique index serialId_index on table_name (`123`,`123`,`3`,`4`);
create index end_time_index on table_name (`end_time`);
create index filter_tbds_idx on table_name (`colname`);
create index is_dma_idx on table_name (`colname`);
create index get_ear_idx on table_name (`colname`,`colname`,`colname`,`colname`);
DROP INDEX <索引名> ON <表名> ('字段名')
-- 修改字段名称
alter table table_name change colname colname2 varchar(64) NOT NULL COMMENT '';
-- 修改字段属性
alter table table_name MODIFY event_id varchar(128) DEFAULT '';
alter table table_name MODIFY event_id varchar(128) DEFAULT '';
-- 新增字段
ALTER TABLE table_name ADD COLUMN row_number int DEFAULT 0 COMMENT '' AFTER attribute_union_md5;
-- 查看执行情况
select id, db, user, host, command, time, state, info from information_schema.processlist order by time desc limit 100
select id, db, user, host, command, time, state, info from information_schema.processlist where command!='Sleep' limit 10
CREATE USER '用户名' @'%' IDENTIFIED BY '密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON 库名.表名 TO '用户名' @'%';
