MySQL 常用腳本之查看視圖、存儲(chǔ)過(guò)程/函數(shù)、觸發(fā)器、計(jì)劃任務(wù)等信息

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專(zhuān)欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        視圖
            列出數(shù)據(jù)庫(kù)中的所有視圖
            查看視圖的定義語(yǔ)句
            列出視圖中的字段信息
        存儲(chǔ)過(guò)程和函數(shù)
            列出數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程和函數(shù)
            查看存儲(chǔ)過(guò)程和函數(shù)的參數(shù)列表
            查看存儲(chǔ)過(guò)程和函數(shù)的定義
        觸發(fā)器
            列出數(shù)據(jù)庫(kù)中的觸發(fā)器
            查看指定表上的觸發(fā)器
            查看觸發(fā)器的定義
        計(jì)劃任務(wù)/事件
            列出服務(wù)器中的計(jì)劃事件
        對(duì)象依賴(lài)
            查找視圖的底層基表或視圖
            查找存儲(chǔ)函數(shù)被引用的視圖

大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。上一篇文章介紹了如何在 MySQL 查看數(shù)據(jù)庫(kù)、表結(jié)構(gòu)、約束、索引以及它們占用的磁盤(pán)空間。

今天我們繼續(xù)分享其他常用的 MySQL查詢(xún)腳本，包括查看視圖、存儲(chǔ)過(guò)程/函數(shù)、觸發(fā)器、計(jì)劃任務(wù)/事件等信息。
視圖
列出數(shù)據(jù)庫(kù)中的所有視圖

視圖（View）是虛擬的表，因此許多查看表的方式也會(huì)返回視圖信息：

-- 方法一
show full tables where table_type = 'VIEW';

-- 方法二
select table_schema as database_name,
       table_name as view_name
from information_schema.views
where table_schema not in ('sys','information_schema', 'mysql', 'performance_schema')
-- and table_schema = 'database_name'
order by table_schema, table_name;

-- 方法三
select table_schema as database_name,
       table_name as view_name
from information_schema.tables
where table_schema not in ('sys','information_schema', 'mysql', 'performance_schema')
and table_type = 'VIEW'
-- and table_schema = 'database_name'
order by table_schema, table_name;



查看視圖的定義語(yǔ)句

-- 方法一
select vw.table_schema as database_name,
       vw.table_name as view_name,
       vw.view_definition as definition,
       tb.table_comment as description
from information_schema.views as vw
join information_schema.tables as tb
on tb.table_name = vw.table_name
-- where vw.table_schema = 'database_name'
-- and vw.table_name = 'view_name'
order by database_name, view_name;

-- 方法二
show create view view_name;


列出視圖中的字段信息

select col.table_schema as database_name,
       col.table_name as view_name,
       col.ordinal_position,
       col.column_name,
       col.data_type,
       case when col.character_maximum_length is not null then col.character_maximum_length
            else col.numeric_precision
       end as max_length,
       col.is_nullable
from information_schema.columns col
join information_schema.views v
on v.table_schema = col.table_schema and v.table_name = col.table_name
where col.table_schema not in ('sys','information_schema','mysql', 'performance_schema')
-- and v.table_schema = 'database_name'
-- and v.table_name = 'view_name'
order by col.table_schema,col.table_name,col.ordinal_position;



存儲(chǔ)過(guò)程和函數(shù)
列出數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程和函數(shù)

-- 方法一
select routine_schema as database_name,
       routine_name,
       routine_type as type,
       data_type as return_type,
       routine_definition as definition
from information_schema.routines
where routine_schema not in ('sys', 'information_schema', 'mysql', 'performance_schema')
-- and r.routine_schema = 'database_name'
order by routine_schema,routine_name;

-- 方法二
SHOW PROCEDURE STATUS
    [LIKE 'pattern' | WHERE expr];

SHOW FUNCTION STATUS
    [LIKE 'pattern' | WHERE expr];



查看存儲(chǔ)過(guò)程和函數(shù)的參數(shù)列表

select r.routine_schema as database_name,
       r.specific_name as routine_name,
       r.routine_type AS type,
       p.parameter_name,
       p.data_type,
       case when p.parameter_mode is null and p.data_type is not null
            then 'RETURN'
            else parameter_mode end as parameter_mode,
       p.character_maximum_length as char_length,
       p.numeric_precision,
       p.numeric_scale
from information_schema.routines r
left join information_schema.parameters p
on p.specific_schema = r.routine_schema and p.specific_name = r.specific_name
where r.routine_schema not in ('sys', 'information_schema', 'mysql', 'performance_schema')
-- and r.routine_schema = 'database_name'
-- and r.specific_name = 'routine_name'
order by r.routine_schema,r.specific_name,p.ordinal_position;



查看存儲(chǔ)過(guò)程和函數(shù)的定義

SHOW CREATE PROCEDURE proc_name;
SHOW CREATE FUNCTION func_name;



觸發(fā)器
列出數(shù)據(jù)庫(kù)中的觸發(fā)器

-- 方法一
SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr];

-- 方法二
select trigger_schema as trigger_database,
       trigger_name,
       concat(event_object_schema, '.', event_object_table)  as trigger_table,
       action_order,
       action_timing,
       event_manipulation as trigger_event,
       action_statement as 'definition'
from information_schema.triggers
where trigger_schema not in ('sys','mysql')
-- and trigger_schema = 'database_name'
order by trigger_schema,trigger_name;



查看指定表上的觸發(fā)器

select event_object_schema as database_name,
       event_object_table as table_name,
       trigger_name,
       action_order,
       action_timing,
       event_manipulation as trigger_event,
       action_statement as 'definition'
from information_schema.triggers
where trigger_schema not in ('sys','mysql')
and event_object_schema = 'database_name'
and event_object_table = 'table_name'
order by database_name,table_name;



查看觸發(fā)器的定義

show create trigger trigger_name;



計(jì)劃任務(wù)/事件
列出服務(wù)器中的計(jì)劃事件

-- 方法一
SHOW EVENTS
    [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE expr];

-- 方法二
select event_schema as database_name,
       event_name,
       event_definition,
       event_type,
       concat(interval_value, ' ', interval_field) as 'interval',
       case when event_type = 'RECURRING' then starts
            else execute_at
       end as execute_time,
       ends,
       status
from information_schema.events
-- where event_schema  = 'database_name'
order by event_schema,event_name;



對(duì)象依賴(lài)
查找視圖的底層基表或視圖

select vtu.view_schema as database_name,
       vtu.view_name as view_name,
       vtu.table_schema as referenced_database_name,
       vtu.table_name as referenced_object_name,
       tab.table_type as object_type
from information_schema.view_table_usage vtu
join information_schema.tables tab on vtu.table_schema = tab.table_schema
                                   and vtu.table_name = tab.table_name
where view_schema not in ('sys','information_schema', 'mysql', 'performance_schema')
-- and vtu.view_schema = 'database_name'
-- and vtu.view_name = 'view_name'
order by vtu.view_schema,vtu.view_name;



查找存儲(chǔ)函數(shù)被引用的視圖

以下查詢(xún)可以找出存儲(chǔ)函數(shù)被哪些視圖所使用，不支持內(nèi)置函數(shù)和用戶(hù)自定義函數(shù)：

select table_schema as database_name,
       table_name as view_name,
       specific_schema as routine_database,
       specific_name as routine_name
from information_schema.view_routine_usage
where table_schema not in ('performance_schema', 'sys', 'information_schema', 'mysql')
-- and specific_name = 'function_name'
order by database_name, view_name;



寫(xiě)作不易，如果你點(diǎn)擊了收藏?，請(qǐng)不要忘了關(guān)注??、評(píng)論??、點(diǎn)贊??！