分享一下工作中常见的mysql脚本,此次分享的内容如下:
- 列
- 视图
一、专栏
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 - 列的默认表达式
3. 列出 MySQL 数据库中所有包含详细信息的表列(PKs、UKs、FKs、Default、Computed 等)
select col.table_schema as database_name,
col.table_name,
col.column_name,
col.data_type,
case when col.data_type in ('datetime', 'timestamp', 'time')
then col.datetime_precision
else col.numeric_precision end as 'precision',
col.numeric_scale,
col.character_maximum_length as char_length,
col.column_default,
col.generation_expression,
case when (group_concat(constraint_type separator ', '))
like '%PRIMARY KEY%'
then 'YES' else 'NO' end as PK,
case when (group_concat(constraint_type separator ', '))
like '%UNIQUE%'
then 'YES' else 'NO' end as UQ,
case when (group_concat(constraint_type separator ', '))
like '%FOREIGN KEY%'
then 'YES' else 'NO' end as FK,
col.is_nullable
from information_schema.columns col
join information_schema.tables tab
on col.table_schema = tab.table_schema
and col.table_name = tab.table_name
and tab.table_type = 'base TABLE'
left join information_schema.key_column_usage kcu
on col.table_schema = kcu.table_schema
and col.table_name = kcu.table_name
and col.column_name = kcu.column_name
left join information_schema.table_constraints tco
on kcu.constraint_schema = tco.constraint_schema
and kcu.constraint_name = tco.constraint_name
and kcu.table_name = tco.table_name
where col.table_schema not in('information_schema', 'sys',
'performance_schema', 'mysql')
group by 1,2,3,4,5,6,7,8,9,13
order by col.table_schema,
col.table_name,
col.column_name;
说明:
- database_name - 数据库(模式)名称
- table_name - 表名
- column_name - 列名
- data_type - 数据列的类型包含
- 精度- 数字类型的精度或日期时间类型的小数位数
- numeric_scale - 数字的比例数据类型
- char_length - 最大字符长度
- column_default - 列的默认值
- PK - 指示列是否为主键
- FK - 指示列是否为外键
- UQ - 指示列是否必须在表中具有唯一值
- is_nullable - 指示列是否可以为空
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 - 计算公式
二、查看情况
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 - 视图名称
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 - 视图的名称
- 定义- 视图的定义脚本
- 描述- 视图的描述
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 数据库中视图使用的表
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- 引用对象的类型:基表;视图
免责声明:本平台仅供信息发布交流之途,请谨慎判断信息真伪。如遇虚假诈骗信息,请立即举报
举报