社区编辑申请
注册/登录
MySQL数据库中查找最常用的数据类型
数据库 MySQL
今天给大家分享几个关于MySQL数据类型的查询,下面,我们一起来看。

一、概述

今天分享几个关于MySQL数据类型的查询,具体如下:

  • 在 MySQL 数据库中查找最常用的数据类型
  • 查找 MySQL 数据库中的所有数字列
  • 查找 MySQL 数据库中的所有字符串(字符)列
  • 查找 MySQL 数据库中的所有日期和时间列
  • 查找 MySQL 数据库中的所有枚举列
  • 查找 MySQL 数据库中的所有空间数据列
  • 查找 MySQL 数据库中的所有 JSON 数据列
  • 在 MySQL 数据库中查找大对象 (LOB) 数据类型列
  • 在 MySQL 数据库中查找具有大对象 (LOB) 数据类型列的表

二、相关SQL

1. 在 MySQL 数据库中查找最常用的数据类型

select data_type,
count(*) as columns,
cast(100*count(*)/sum_all.columns as decimal(36,2))
as percent_columns,
count(distinct concat(col.table_schema, '.', col.table_name))
as tables,
cast(100*count(distinct concat(col.table_schema,'.',col.table_name))
/ sum_all.tables as decimal(36,2)) as percent_tables
from information_schema.columns col
join (select count(distinct concat(c.table_schema, '.', c.table_name))
as tables,
count(*) as columns
from information_schema.columns c
join information_schema.tables t
on c.table_schema = t.table_schema
and c.table_name = t.table_name
where t.table_schema not in ('information_schema', 'mysql',
'performance_schema', 'sys')
and t.table_type = 'BASE TABLE'
) sum_all on true
join information_schema.tables tab
on col.table_schema = tab.table_schema
and col.table_name = tab.table_name
where tab.table_schema not in ('information_schema', 'mysql',
'performance_schema', 'sys')
and tab.table_type = 'BASE TABLE'
group by data_type,
sum_all.columns,
sum_all.tables
order by columns desc;

说明:

  • data_type - 没有长度或精度的内置或用户数据类型,例如 int、varchar 或 datetime
  • columns - 具有此数据类型的数据库(模式)中的列数
  • percent_columns - 具有此数据类型的列的百分比。行总数为 100%
  • tables- 数据库(模式)中具有此数据类型的表数
  • percent_tables - 具有此数据类型的列的表的百分比。

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

2. 查找 MySQL 数据库中的所有数字列

select col.table_schema as database_name,
col.table_name,
col.ordinal_position as col_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
and tab.table_name = col.table_name
and tab.table_type = 'BASE TABLE'
where col.data_type in ('tinyint', 'smallint', 'mediumint',
'int', 'bigint', 'decimal', 'bit',
'float', 'double')
and col.table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
--and col.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
col.table_name,
col.ordinal_position;

说明:

  • database_name - 数据库的名称(模式)
  • table_name - 表的名称
  • column_id - 表中的列位置
  • column_name - 列的名称
  • data_type - 数据类型
  • numeric_precision - 列的精度
  • numeric_scale - 列的比例

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

2. 查找 MySQL 数据库中的所有字符串(字符)列

select col.table_schema as database_name,
col.table_name,
col.ordinal_position as column_id,
col.column_name,
col.data_type,
col.character_maximum_length as maximum_length,
col.character_set_name
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
and tab.table_name = col.table_name
and tab.table_type = 'BASE TABLE'
where col.data_type in ('char', 'varchar', 'binary', 'varbinary',
'blob', 'tinyblob', 'mediumblob', 'longblob',
'text', 'tinytext', 'mediumtext', 'longtext'
'enum', 'set')
and col.table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
--and col.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
col.table_name,
col.ordinal_position;

说明:

  • database_name - 数据库的名称(模式)
  • table_name - 表的名称
  • column_id - 表中的列位置
  • column_name - 列的名称
  • data_type - 数据类型
  • maximum_length - 字符的最大长度
  • character_set_name - 字符集名称

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

4. 查找 MySQL 数据库中的所有日期和时间列

select col.table_schema as database_name,
col.table_name,
col.ordinal_position as column_id,
col.column_name,
col.data_type,
col.datetime_precision
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
and tab.table_name = col.table_name
and tab.table_type = 'BASE TABLE'
where col.data_type in ('date', 'time', 'datetime', 'year', 'timestamp')
and col.table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
--and col.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
col.table_name,
col.ordinal_position;

说明:

  • database_name - 数据库的名称(模式)
  • table_name - 表的名称
  • column_id - 表中的列位置
  • column_name - 列的名称
  • data_type - 数据类型
  • datetime_precision - 小数秒精度

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

5. 查找 MySQL 数据库中的所有枚举列

select col.table_schema as database_name,
col.table_name,
col.ordinal_position as column_id,
col.column_name,
col.data_type,
trim(leading 'enum' from col.column_type) as enum_values
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
and tab.table_name = col.table_name
and tab.table_type = 'BASE TABLE'
where col.data_type in ('enum')
and col.table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
--and col.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
col.table_name,
col.ordinal_position;

说明:

  • database_name - 数据库的名称(模式)
  • table_name - 表的名称
  • column_id - 表中的列位置
  • column_name - 列的名称
  • data_type - 数据类型
  • enum_values - 声明可能的枚举值

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

6. 查找 MySQL 数据库中的所有空间数据列

select col.table_schema as database_name,
col.table_name,
col.ordinal_position as column_id,
col.column_name,
col.data_type,
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 table_type = 'BASE TABLE'
where col.data_type in ('geometry', 'point', 'linestring', 'polygon',
'multipoint', 'multilinestring', 'multipolygon',
'geometrycollection')
and col.table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
-- and table_schema = 'database_name' -- put your database name here
order by col.table_schema,
col.table_name;

说明:

(1)database_name - 数据库的名称(模式)

(2)table_name - 表的名称

(3)column_id - 表中的列位置

(4)column_name - 列的名称

(5)data_type - 空间数据的类型:

  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON
  • MULTIPOINT
  • MULTILINESTRING
  • MULTIPOLYGON
  • GEOMETRYCOLLECTION

(6)is_nullable - 指示列是否可以包含空值

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

7. 查找 MySQL 数据库中的所有 JSON 数据列

select col.table_schema as database_name,
col.table_name,
col.ordinal_position as column_id,
col.column_name,
col.data_type
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
and tab.table_name = col.table_name
and tab.table_type = 'BASE TABLE'
where col.data_type in ('json')
and col.table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
--and col.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
col.table_name,
col.ordinal_position;

说明:

  • database_name - 数据库的名称(模式)
  • table_name - 表的名称
  • column_id - 表中的列位置
  • column_name - 列的名称
  • data_type - 数据类型

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

8. 在 MySQL 数据库中查找大对象 (LOB) 数据类型列

select tab.table_schema as database_name,
tab.table_name,
col.column_name,
col.data_type
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_schema = 'your database name'
and tab.table_type = 'BASE TABLE'
and col.data_type in ('blob', 'mediumblob', 'longblob',
'text','mediumtext','longtext')
order by tab.table_name,
col.column_name;

说明:

  • schema_name - 数据库的名称(模式)
  • table_name - 表的名称
  • column_name - 列的名称
  • data_type - 数据类型

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

9. 在 MySQL 数据库中查找具有大对象 (LOB) 数据类型列的表

select tab.table_name,
count(*) as columns
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
and col.data_type in ('blob', 'mediumblob', 'longblob',
'text', 'mediumtext', 'longtext')
where tab.table_schema = 'your database name'
and tab.table_type = 'BASE TABLE'
group by tab.table_name
order by tab.table_name;

说明:

  • table_name - 表的名称
  • columns - 表中的 LOB 列数

DBA技术分享(九)- MySQL数据库中查找最常用的数据类型

责任编辑:赵宁宁 来源: 今日头条
相关推荐

2010-05-26 17:05:48

2010-06-04 15:32:18

2010-05-21 16:33:02

MySQL数据库

2010-05-25 14:05:52

MySQL数据库

2010-06-10 10:06:01

2010-05-31 10:35:12

MySQL数据类型

2010-05-11 14:08:50

2010-10-08 14:04:44

MySQL数值数据类型

2010-11-23 13:29:36

MySQL数据列类型

2018-10-23 10:15:03

2015-05-20 13:48:26

MySQL索引

2022-02-21 08:41:50

Redis

2018-07-11 13:33:56

数据库MySQL改善经验

2017-08-30 17:47:35

2010-11-22 13:53:46

MySQL数据表

2017-07-10 13:38:07

MySQL数据类型整数类型

2010-05-24 09:02:52

同话题下的热门内容

明明加了唯一索引,为什么还是产生重复数据?亿流量大考:日增上亿数据,把MySQL直接搞宕机了字节二面,两个事务执行 SQL 语句的过程中,导致死锁MySQL源码解析之执行计划推荐几款好用的MySQL开源客户端,建议收藏

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号