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_tablesfrominformation_schema.columnscoljoin (selectcount(distinctconcat(c.table_schema, '.', c.table_name))astables,count(*) ascolumnsfrominformation_schema.columnscjoininformation_schema.tablestonc.table_schema=t.table_schemaandc.table_name=t.table_namewheret.table_schemanotin ('information_schema', 'mysql','performance_schema', 'sys')andt.table_type='BASE TABLE'      ) sum_allontruejoininformation_schema.tablestaboncol.table_schema=tab.table_schemaandcol.table_name=tab.table_namewheretab.table_schemanotin ('information_schema', 'mysql','performance_schema', 'sys')andtab.table_type='BASE TABLE'groupbydata_type,sum_all.columns,sum_all.tablesorderbycolumnsdesc;

说明：

data_type– 没有长度或精度的内置或用户数据类型，例如 int、varchar 或 datetimecolumns– 具有此数据类型的数据库（模式）中的列数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_scalefrominformation_schema.columnscoljoininformation_schema.tablestabontab.table_schema=col.table_schemaandtab.table_name=col.table_nameandtab.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'--putyourdatabasenamehereorderbycol.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_namefrominformation_schema.columnscoljoininformation_schema.tablestabontab.table_schema=col.table_schemaandtab.table_name=col.table_nameandtab.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'--putyourdatabasenamehereorderbycol.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_precisionfrominformation_schema.columnscoljoininformation_schema.tablestabontab.table_schema=col.table_schemaandtab.table_name=col.table_nameandtab.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'--putyourdatabasenamehereorderbycol.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_valuesfrominformation_schema.columnscoljoininformation_schema.tablestabontab.table_schema=col.table_schemaandtab.table_name=col.table_nameandtab.table_type='BASE TABLE'wherecol.data_typein ('enum')andcol.table_schemanotin ('information_schema', 'sys','performance_schema', 'mysql')--andcol.table_schema='database_name'--putyourdatabasenamehereorderbycol.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_nullablefrominformation_schema.columnscoljoininformation_schema.tablestaboncol.table_schema=tab.table_schemaandcol.table_name=tab.table_nameandtable_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'--putyourdatabasenamehereorderbycol.table_schema,col.table_name;

说明：

(1)database_name– 数据库的名称（模式）

(2)table_name– 表的名称

(3)column_id– 表中的列位置

(4)column_name– 列的名称

(5)data_type– 空间数据的类型：

GEOMETRYPOINTLINESTRINGPOLYGONMULTIPOINTMULTILINESTRINGMULTIPOLYGONGEOMETRYCOLLECTION

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

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

selectcol.table_schemaasdatabase_name,col.table_name,col.ordinal_positionascolumn_id,col.column_name,col.data_typefrominformation_schema.columnscoljoininformation_schema.tablestabontab.table_schema=col.table_schemaandtab.table_name=col.table_nameandtab.table_type='BASE TABLE'wherecol.data_typein ('json')andcol.table_schemanotin ('information_schema', 'sys','performance_schema', 'mysql')--andcol.table_schema='database_name'--putyourdatabasenamehereorderbycol.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_typefrominformation_schema.tablesastabinnerjoininformation_schema.columnsascoloncol.table_schema=tab.table_schemaandcol.table_name=tab.table_namewheretab.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(*) ascolumnsfrominformation_schema.tablesastabinnerjoininformation_schema.columnsascoloncol.table_schema=tab.table_schemaandcol.table_name=tab.table_nameandcol.data_typein ('blob', 'mediumblob', 'longblob', 'text', 'mediumtext', 'longtext')wheretab.table_schema='your database name'andtab.table_type='BASE TABLE'groupbytab.table_nameorderbytab.table_name;

说明：

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