程序步骤
编写环境 mysql workbench,本篇没有创造临时表,故没有在sql语句脚本中编写表的建造。
定时器事件编写
每20s打印一次系统状态,首先应查看当前是否开启调度计划,其次开启事件调度计划,编写定时器,事件开启与关闭
// An highlighted block
set global event_scheduler=1;
drop event if exists test_event1;
create event test_event1
on schedule every 20 second
on completion preserve
do call insert1();
alter event test_event1 on completion preserve enable;
存储过程编写
编写inser1过程
// An highlighted block
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert1`()
BEGIN
drop table if exists system_status;
create table system_status(table_name varchar(45),len INT,table_rows int ,datas varchar(45));
insert into system_status(table_name,len,table_rows,datas)
SELECT table_name, data_length + index_length AS len, table_rows,
CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB')
AS datas FROM information_schema.tables
WHERE table_schema = 'bank' ORDER BY len DESC;
END
触发器编写
account表每插入一个数据时,将新插入的数据插入一个test表,并打印。
// An highlighted block
DELIMITER ||
drop trigger if exists account_log;
create trigger account_log
after insert on account for each row
begin
insert into test(insert_id,name,insert_time) values(new.accout_id ,new.account_name,new.time);
end
||
打印系统状态表格
将索引长度和数据长度连接起来并保留小数点后两位并将单位改到MB,1024*1024为1mb。
// An highlighted block
SELECT table_name, data_length + index_length AS len, table_rows,
CONCAT(ROUND((data_length+ index_length)/1024/1024,2),'MB')
AS datas FROM information_schema.tables
WHERE table_schema = 'bank' ORDER BY len DESC;
以html格式将其导出
// A code block
mysql -u root -p --html -e "select * from system_status;" bank>C:\table.html
这是打出的表格
这是插入后的account表格
这是triiger触发的表格
最后html文件的输出