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 - 具有此数据类型的列的表的百分比。

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

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

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

 
版权声明 本文仅代表作者观点，不代表本站立场。
如遇本文系为网络转载到本站发表，图片或文章有版权问题的请联系客服确认后会立即删除文章。
如遇本文系作者授权本站发表，未经许可，不得转载。

--结束END--

有问题投稿请发送至: 邮箱/kf@guangfuqiang.com    QQ/162020580

本文标题: MySQL数据库中查找最常用的数据类型

本文链接: https://www.guangfuqiang.com/zobk/a68530.html (转载时请保留)

网站首页

返回栏目
今日推荐
新闻举报 返回首页
更多
友情链接
鄂ICP备19019357号-22