MySQL常用查询Columns和Views-mysql查询所有子节点

分享一下工作中常见的mysql脚本，此次分享的内容如下：

Columns
Views

一、Columns

1. 列出 MySQL 数据库中的表列

select tab.table_schema as database_schema,
    tab.table_name as table_name,
    col.ordinal_position as column_id,
    col.column_name as column_name,
    col.data_type as data_type,
    case when col.numeric_precision is not null
        then col.numeric_precision
        else col.character_maximum_length end as max_length,
    case when col.datetime_precision is not null
        then col.datetime_precision
        when col.numeric_scale is not null
        then col.numeric_scale
            else 0 end as 'precision'
from information_schema.tables as tab
    inner join information_schema.columns as col
        on col.table_schema = tab.table_schema
        and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
    and tab.table_schema not in ('information_schema','mysql',
        'performance_schema','sys')
    -- uncomment line below for current database only
    -- and tab.table_schema = database() 
    -- uncomment line below and provide specific database name
    -- and tab.table_schema = 'your_database_name' 
order by tab.table_name,
    col.ordinal_position;

注意：要查看特定数据库中的列，请取消注释上述子句之一。

说明：

schema_name - 数据库名称
table_name - 表名
column_id - 表列 id，每个表从 1 开始
column_name - 列的名称
data_type - 列数据类型
max_length - 数据类型最大长度
precision- 数据类型精度

2. 列出 MySQL 数据库中特定表中的所有列

select ordinal_position as column_id,
    column_name as column_name,
    data_type as data_type,
    case when numeric_precision is not null
              then numeric_precision
        else character_maximum_length end as max_length,
    case when datetime_precision is not null
              then datetime_precision
        when numeric_scale is not null
             then numeric_scale
        else 0 end as data_precision,
    is_nullable,
    column_default
from information_schema.columns
where table_name = 'table name' -- put table name here
--    and table_schema = 'schema name' -- put schema name here
order by ordinal_position;

说明：

column_id - 表中的列位置，从 1 开始
column_name - 表中列的名称
data_type - 列数据类型
max_length - 数据类型最大长度
data_precision - 数据类型精度
is_nullable - 如果列可以为空，则为 YES，否则为 NO
column_default - 列的默认表达式

DBA技术分享（二）-MYSQL常用查询Columns和Views

3. 列出 MySQL 数据库中所有包含详细信息的表列（PKs、UKs、FKs、Default、Computed 等）

说明：

database_name - 数据库（模式）名称
table_name - 表名
column_name - 列名
data_type - 数据列的类型包含
精度- 数字类型的精度或日期时间类型的小数位数
numeric_scale - 数字数据类型的比例
char_length - 最大字符长度
column_default - 列的默认值
PK - 指示列是否为主键
FK - 指示列是否为外键
UQ - 指示列是否必须在表中具有唯一值
is_nullable - 指示列是否可以为空

DBA技术分享（二）-MYSQL常用查询Columns和Views

4. 列出 MySQL 数据库中所有计算（生成）的列

select table_schema as database_name,
       table_name,
       column_name,
       data_type,
       generation_expression
from information_schema.columns 
where length(generation_expression) > 0
      and table_schema not in ('information_schema', 'sys',
                               'performance_schema', 'mysql')
order by table_schema,
         table_name,
         column_name;

说明：

database_name - 包含表的数据库（模式）名称
table_name - 表名
column_name - 列的名称
data_type - 列的数据类型
generation_expression - 计算公式

DBA技术分享（二）-MYSQL常用查询Columns和Views

二、Views

1. 列出 MySQL 数据库中的视图

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' -- put your database name here
order by table_schema,
         table_name;

说明：

database_name - 包含视图的数据库（模式）的名称
view_name - 视图名称

DBA技术分享（二）-MYSQL常用查询Columns和Views

2. 列出 MySQL 中的视图及其定义

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
    inner join information_schema.tables as tb
        on tb.table_name = vw.table_name
-- where vw.table_schema = 'your database name'
order by database_name, view_name;

注意：如果您需要特定数据库（模式）的信息，请取消注释 table_schema 行并提供您的数据库名称。

说明：

schema_name - 视图的数据库（模式）名称
view_name - 视图的名称
定义- 视图的定义脚本
描述- 视图的描述

DBA技术分享（二）-MYSQL常用查询Columns和Views

3. 列出 MySQL 数据库中的视图中的所有列

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 vie on vie.table_schema = col.table_schema
                                  and vie.table_name = col.table_name
where col.table_schema not in ('sys','information_schema',
                               'mysql', 'performance_schema')
    -- and vie.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

说明：

database_name - 数据库（模式）名称
view_name - 视图名称
column_name - 列名
data_type - 列数据类型
max_length- 列长度：对于字符串列，以字符为单位的最大长度；对于数值列，数值精度。
is_nullable - 指示列是否允许空值的标志

4. 列出 MySQL 数据库中视图使用的表

查询在8.0.13 MySQL 版本下执行。

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 tab.table_schema = 'database_name' -- put your database name here
order by vtu.view_schema,
         vtu.view_name;

说明：

database_name - 查看数据库（模式）名称
view_name - 视图名称
referenced_database_name - 视图引用的表数据库名称
referenced_object_name - 视图引用的表名
object_type- 引用对象的类型：BASE TABLE；VIEW

DBA技术分享（二）-MYSQL常用查询Columns和Views