mysql常用命令记录

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 1\G;

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;

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 1\G;

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 '用户名' @'%';