MySQL常用查询Columns和Views

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

• Columns
• Views

一、Columns

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

selecttab.table_schemaasdatabase_schema,
tab.table_nameastable_name,
col.ordinal_positionascolumn_id,
col.column_nameascolumn_name,
col.data_typeasdata_type,
casewhencol.numeric_precisionisnotnull
thencol.numeric_precision
elsecol.character_maximum_lengthendasmax_length,
casewhencol.datetime_precisionisnotnull
thencol.datetime_precision
whencol.numeric_scaleisnotnull
thencol.numeric_scale
else0endas'precision'
frominformation_schema.tablesastab
innerjoininformation_schema.columnsascol
oncol.table_schema=tab.table_schema
andcol.table_name=tab.table_name
wheretab.table_type='BASE TABLE'
andtab.table_schemanotin ('information_schema','mysql',
'performance_schema','sys')
--uncommentlinebelowforcurrentdatabaseonly
--andtab.table_schema=database() 
--uncommentlinebelowandprovidespecificdatabasename
--andtab.table_schema='your_database_name'
orderbytab.table_name,
col.ordinal_position;

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

说明：

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

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

selectordinal_positionascolumn_id,
column_nameascolumn_name,
data_typeasdata_type,
casewhennumeric_precisionisnotnull
thennumeric_precision
elsecharacter_maximum_lengthendasmax_length,
casewhendatetime_precisionisnotnull
thendatetime_precision
whennumeric_scaleisnotnull
thennumeric_scale
else0endasdata_precision,
is_nullable,
column_default
frominformation_schema.columns
wheretable_name='table name'--puttablenamehere
--andtable_schema='schema name'--putschemanamehere
orderbyordinal_position;

说明：

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

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 – 指示列是否可以为空

​​

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

selecttable_schemaasdatabase_name,
table_name,
column_name,
data_type,
generation_expression
frominformation_schema.columns
wherelength(generation_expression) >0
andtable_schemanotin ('information_schema', 'sys',
'performance_schema', 'mysql')
orderbytable_schema,
table_name,
column_name;

说明：

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

二、Views

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

selecttable_schemaasdatabase_name,
table_nameasview_name
frominformation_schema.views
wheretable_schemanotin ('sys','information_schema',
'mysql', 'performance_schema')
--andtable_schema='database_name'--putyourdatabasenamehere
orderbytable_schema,
table_name;

说明：

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

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

selectvw.table_schemaasdatabase_name,
vw.table_nameasview_name,
vw.view_definitionasdefinition,
tb.table_commentasdescription
frominformation_schema.viewsasvw
innerjoininformation_schema.tablesastb
ontb.table_name=vw.table_name
--wherevw.table_schema='your database name'
orderbydatabase_name, view_name;

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

说明：

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

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

selectcol.table_schemaasdatabase_name,
col.table_nameasview_name,
col.ordinal_position,
col.column_name,
col.data_type,
casewhencol.character_maximum_lengthisnotnull
thencol.character_maximum_length
elsecol.numeric_precisionendasmax_length,
col.is_nullable
frominformation_schema.columnscol
joininformation_schema.viewsvieonvie.table_schema=col.table_schema
andvie.table_name=col.table_name
wherecol.table_schemanotin ('sys','information_schema',
'mysql', 'performance_schema')
--andvie.table_schema='database_name'--putyourdatabasenamehere
orderbycol.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 版本下执行。

selectvtu.view_schemaasdatabase_name,
vtu.view_nameasview_name,
vtu.table_schemaasreferenced_database_name,
vtu.table_nameasreferenced_object_name,
tab.table_typeasobject_type
frominformation_schema.view_table_usagevtu
joininformation_schema.tablestabonvtu.table_schema=tab.table_schema
andvtu.table_name=tab.table_name
whereview_schemanotin ('sys','information_schema',
'mysql', 'performance_schema')
--andtab.table_schema='database_name'--putyourdatabasenamehere
orderbyvtu.view_schema,
vtu.view_name;

说明：

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

​​