合 MySQL数据库对象类型和个数统计
👉 本文共约130个字，系统预计阅读时间或需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) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
