一、概述

今天分享几个关于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 或 datetimecolumns - 具有此数据类型的数据库（模式）中的列数percent_columns - 具有此数据类型的列的百分比。行总数为 100%tables- 数据库（模式）中具有此数据类型的表数percent_tables - 具有此数据类型的列的表的百分比。

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 - 列的比例

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 - 字符集名称

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 - 小数秒精度

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 - 声明可能的枚举值

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 - 空间数据的类型：

GEOMETRYPOINTLINESTRINGPOLYGONMULTIPOINTMULTILINESTRINGMULTIPOLYGONGEOMETRYCOLLECTION

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

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 - 数据类型

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 - 数据类型

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 列数