MySQL数据库对象类型和个数统计
👉 本文共约138个字，系统预计阅读时间或需1分钟。
MySQL数据库常见对象：
- 表（Table）
- 视图（View）
- Routines（存储过程（Procedure）、函数（function））
- 触发器（Triggers）
- 事件（EVENTS）：JOB
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | drop view vw_ob_lhr; create view vw_ob_lhr as select db ,type ,cnt from (select 'TABLE' type,table_schema db, count(*) cnt from information_schema.TABLES a where table_type='BASE TABLE' group by table_schema union all select 'EVENTS' type,event_schema db,count(*) cnt from information_schema.EVENTS b group by event_schema union all select 'TRIGGERS' type,trigger_schema db,count(*) cnt from information_schema.TRIGGERS c group by trigger_schema union all select 'PROCEDURE' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d whereROUTINE_TYPE = 'PROCEDURE' group by db union all select 'FUNCTION' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d whereROUTINE_TYPE = 'FUNCTION' group by db union all select 'VIEWS' type,table_schema db,count(*) cnt from information_schema.VIEWS f group by table_schema ) t order by db,type; select * from vw_ob_lhr where db='lhrdb'; MySQL [lhrdb]> select * from vw_ob_lhr where db='lhrdb'; +-------+-----------+-----+ | db | type | cnt | +-------+-----------+-----+ | lhrdb | EVENTS | 1 | | lhrdb | FUNCTION | 1 | | lhrdb | PROCEDURE | 2 | | lhrdb | TABLE | 4 | | lhrdb | TRIGGERS | 1 | | lhrdb | VIEWS | 1 | +-------+-----------+-----+ 6 rows in set (0.04 sec) |
创建常见对象：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | -- 函数 set GLOBAL log_bin_trust_function_creators=on; -- ERROR 1418 (HY000) at line 200: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) DROP FUNCTION if exists rand_string; delimiter // CREATE DEFINER=`root`@`%` FUNCTION lhrdb.`rand_string`(n INT) RETURNS varchar(255) CHARSET utf8 BEGIN DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 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()*62 ),1)); SET i = i +1; END WHILE; RETURN return_str; END;// delimiter ; select lhrdb.rand_string(5); -- 存储过程 delimiter // DROP PROCEDURE IF EXISTS mock_isam// CREATE PROCEDURE mock_isam (IN rowCount int) BEGIN DECLARE insertCount int; SET insertCount = 0; DROP TABLE IF EXISTS `isam_table`; /*删掉之前创建的表，因为下面要建这个名字的表*/ /*自己按需求修改以下建表语句构造需要的表*/ CREATE TABLE `isam_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` tinyint(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; START TRANSACTION; loopHandler : LOOP /*插入数据*/ INSERT INTO `isam_table`(`name`,`age`) VALUES( rand_string(20), CEIL(RAND() * 110) ); SET insertCount = insertCount + 1; IF (insertCount >= rowCount) THEN LEAVE loopHandler; END IF; END LOOP loopHandler; COMMIT; END // delimiter ; delimiter // DROP PROCEDURE IF EXISTS mock_innodb// CREATE PROCEDURE mock_innodb (IN rowCount int) BEGIN DECLARE insertCount int; SET insertCount = 0; DROP TABLE IF EXISTS `innodb_table`; /*删掉之前创建的表，因为下面要建这个名字的表*/ /*自己按需求修改以下建表语句构造需要的表*/ CREATE TABLE `innodb_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` tinyint(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=innodb DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; START TRANSACTION; loopHandler : LOOP /*插入数据*/ INSERT INTO `innodb_table`(`name`,`age`) VALUES( rand_string(20), CEIL(RAND() * 110) ); SET insertCount = insertCount + 1; IF (insertCount >= rowCount) THEN LEAVE loopHandler; END IF; END LOOP loopHandler; COMMIT; END // delimiter ; -- 插入数据 call mock_innodb(200); call mock_isam(200); select * from innodb_table limit 2; select * from isam_table limit 2; select * from mysql.proc where db = 'lhrdb' and `type` = 'PROCEDURE' ; -- 存储过程 select * from mysql.proc where db = 'lhrdb' and `type` = 'FUNCTION' ; -- 函数 -- 触发器 create table lhrdb.time (time varchar(100)); CREATE TRIGGER lhrdb.trig1 AFTER INSERT ON isam_table FOR EACH ROW INSERT INTO time VALUES(NOW()); SHOW TRIGGERS from lhrdb; select * from information_schema.`TRIGGERS` where trigger_schema='lhrdb' ; -- event事件 drop table if exists lhrdb.events_list; create table lhrdb.events_list(id int PRIMARY key auto_increment, event_name varchar(20) not null, event_started timestamp not null); drop event lhrdb.event_minute; create event lhrdb.event_minute on schedule every 10 minute do insert into lhrdb.events_list(event_name,event_started) values('event_now', now()); set global event_scheduler =1; show processlist; show events; select * from information_schema.`EVENTS` where event_schema='lhrdb' ; select * from events_list; MySQL [lhrdb]> select * from lhrdb.vw_ob_lhr ; +-------+-----------+-----+ | db | type | cnt | +-------+-----------+-----+ | lhrdb | Events | 1 | | lhrdb | Function | 1 | | lhrdb | Procedure | 2 | | lhrdb | Table | 4 | | lhrdb | Trigger | 1 | | lhrdb | View | 1 | +-------+-----------+-----+ |
