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

一、概述

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

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

二、相关SQL

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

selectdata_type,
count(*) ascolumns,
cast(100*count(*)/sum_all.columnsasdecimal(36,2)) 
aspercent_columns,
count(distinctconcat(col.table_schema, '.', col.table_name))
astables,
cast(100*count(distinctconcat(col.table_schema,'.',col.table_name)) 
/sum_all.tablesasdecimal(36,2)) aspercent_tables
frominformation_schema.columnscol
join (selectcount(distinctconcat(c.table_schema, '.', c.table_name))
astables,
count(*) ascolumns
frominformation_schema.columnsc
joininformation_schema.tablest
onc.table_schema=t.table_schema
andc.table_name=t.table_name
wheret.table_schemanotin ('information_schema', 'mysql',
'performance_schema', 'sys')
andt.table_type='BASE TABLE'
      ) sum_allontrue
joininformation_schema.tablestab
oncol.table_schema=tab.table_schema
andcol.table_name=tab.table_name
wheretab.table_schemanotin ('information_schema', 'mysql',
'performance_schema', 'sys')
andtab.table_type='BASE TABLE'
groupbydata_type,
sum_all.columns,
sum_all.tables
orderbycolumnsdesc;

说明：

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

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

selectcol.table_schemaasdatabase_name,
col.table_name,
col.ordinal_positionascol_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
frominformation_schema.columnscol
joininformation_schema.tablestabontab.table_schema=col.table_schema
andtab.table_name=col.table_name
andtab.table_type='BASE TABLE'
wherecol.data_typein ('tinyint', 'smallint', 'mediumint', 
'int', 'bigint', 'decimal', 'bit',
'float', 'double')
andcol.table_schemanotin ('information_schema', 'sys',
'performance_schema', 'mysql')
--andcol.table_schema='database_name'--putyourdatabasenamehere
orderbycol.table_schema,
col.table_name,
col.ordinal_position;

说明：

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

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

selectcol.table_schemaasdatabase_name,
col.table_name,
col.ordinal_positionascolumn_id,
col.column_name,
col.data_type,
col.character_maximum_lengthasmaximum_length,
col.character_set_name
frominformation_schema.columnscol
joininformation_schema.tablestabontab.table_schema=col.table_schema
andtab.table_name=col.table_name
andtab.table_type='BASE TABLE'
wherecol.data_typein ('char', 'varchar', 'binary', 'varbinary', 
'blob', 'tinyblob', 'mediumblob', 'longblob',
'text', 'tinytext', 'mediumtext', 'longtext'
'enum', 'set')
andcol.table_schemanotin ('information_schema', 'sys',
'performance_schema', 'mysql')
--andcol.table_schema='database_name'--putyourdatabasenamehere
orderbycol.table_schema,
col.table_name,
col.ordinal_position;

说明：

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

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

selectcol.table_schemaasdatabase_name,
col.table_name,
col.ordinal_positionascolumn_id,
col.column_name,
col.data_type,
col.datetime_precision
frominformation_schema.columnscol
joininformation_schema.tablestabontab.table_schema=col.table_schema
andtab.table_name=col.table_name
andtab.table_type='BASE TABLE'
wherecol.data_typein ('date', 'time', 'datetime', 'year', 'timestamp')
andcol.table_schemanotin ('information_schema', 'sys',
'performance_schema', 'mysql')
--andcol.table_schema='database_name'--putyourdatabasenamehere
orderbycol.table_schema,
col.table_name,
col.ordinal_position;

说明：

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

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

selectcol.table_schemaasdatabase_name,
col.table_name,
col.ordinal_positionascolumn_id,
col.column_name,
col.data_type,
trim(leading'enum'fromcol.column_type) asenum_values
frominformation_schema.columnscol
joininformation_schema.tablestabontab.table_schema=col.table_schema
andtab.table_name=col.table_name
andtab.table_type='BASE TABLE'
wherecol.data_typein ('enum')
andcol.table_schemanotin ('information_schema', 'sys',
'performance_schema', 'mysql')
--andcol.table_schema='database_name'--putyourdatabasenamehere
orderbycol.table_schema,
col.table_name,
col.ordinal_position;

说明：

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

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

selectcol.table_schemaasdatabase_name,
col.table_name,
col.ordinal_positionascolumn_id,
col.column_name,
col.data_type,
col.is_nullable
frominformation_schema.columnscol
joininformation_schema.tablestab
oncol.table_schema=tab.table_schema
andcol.table_name=tab.table_name
andtable_type='BASE TABLE'
wherecol.data_typein ('geometry', 'point', 'linestring', 'polygon',
'multipoint', 'multilinestring', 'multipolygon',
'geometrycollection')
andcol.table_schemanotin ('information_schema', 'sys',
'performance_schema', 'mysql')
--andtable_schema='database_name'--putyourdatabasenamehere
orderbycol.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 – 指示列是否可以包含空值

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

selectcol.table_schemaasdatabase_name,
col.table_name,
col.ordinal_positionascolumn_id,
col.column_name,
col.data_type
frominformation_schema.columnscol
joininformation_schema.tablestabontab.table_schema=col.table_schema
andtab.table_name=col.table_name
andtab.table_type='BASE TABLE'
wherecol.data_typein ('json')
andcol.table_schemanotin ('information_schema', 'sys',
'performance_schema', 'mysql')
--andcol.table_schema='database_name'--putyourdatabasenamehere
orderbycol.table_schema,
col.table_name,
col.ordinal_position;

说明：

• database_name – 数据库的名称（模式）
• table_name – 表的名称
• column_id – 表中的列位置
• column_name – 列的名称
• data_type – 数据类型

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

selecttab.table_schemaasdatabase_name,
tab.table_name,
col.column_name,
col.data_type
frominformation_schema.tablesastab
innerjoininformation_schema.columnsascol
oncol.table_schema=tab.table_schema
andcol.table_name=tab.table_name
wheretab.table_schema='your database name'
andtab.table_type='BASE TABLE'
andcol.data_typein ('blob', 'mediumblob', 'longblob',
'text','mediumtext','longtext')
orderbytab.table_name,
col.column_name;

说明：

• schema_name – 数据库的名称（模式）
• table_name – 表的名称
• column_name – 列的名称
• data_type – 数据类型

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

selecttab.table_name,
count(*) ascolumns
frominformation_schema.tablesastab
innerjoininformation_schema.columnsascol
oncol.table_schema=tab.table_schema
andcol.table_name=tab.table_name
andcol.data_typein ('blob', 'mediumblob', 'longblob', 
'text', 'mediumtext', 'longtext')
wheretab.table_schema='your database name'
andtab.table_type='BASE TABLE'
groupbytab.table_name
orderbytab.table_name;

说明：

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