百度360必应搜狗淘宝本站头条
当前位置:网站首页 > IT技术 > 正文

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

wptr33 2024-12-29 06:24 30 浏览

一、概述

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

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

二、相关SQL

2.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.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.3 查找 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 - 字符集名称


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

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

2.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;

说明:

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

(1)GEOMETRY

(2)POINT

(3)LINESTRING

(4)POLYGON

(5)MULTIPOINT

(6)MULTILINESTRING

(7)MULTIPOLYGON

(8)GEOMETRYCOLLECTION

  • is_nullable - 指示列是否可以包含空值

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

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

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

小结

后面会分享更多Linux和DBA方面内容,感兴趣的朋友可以关注下!

相关推荐

[常用工具] git基础学习笔记_git工具有哪些

添加推送信息,-m=messagegitcommit-m“添加注释”查看状态...

centos7安装部署gitlab_centos7安装git服务器

一、Gitlab介1.1gitlab信息GitLab是利用RubyonRails一个开源的版本管理系统,实现一个自托管的Git项目仓库,可通过Web界面进行访问公开的或者私人项目。...

太高效了!玩了这么久的Linux,居然不知道这7个终端快捷键

作为Linux用户,大家肯定在Linux终端下敲过无数的命令。有的命令很短,比如:ls、cd、pwd之类,这种命令大家毫无压力。但是,有些命令就比较长了,比如:...

提高开发速度还能保证质量的10个小窍门

养成坏习惯真是分分钟的事儿,而养成好习惯却很难。我发现,把那些对我有用的习惯写下来,能让我坚持住已经花心思养成的好习惯。...

版本管理最好用的工具,你懂多少?

版本控制(Revisioncontrol)是一种在开发的过程中用于管理我们对文件、目录或工程等内容的修改历史,方便查看更改历史记录,备份以便恢复以前的版本的软件工程技术。...

Git回退到某个版本_git回退到某个版本详细步骤

在开发过程,有时会遇到合并代码或者合并主分支代码导致自己分支代码冲突等问题,这时我们需要回退到某个commit_id版本1,查看所有历史版本,获取git的某个历史版本id...

Kubernetes + Jenkins + Harbor 全景实战手册

Kubernetes+Jenkins+Harbor全景实战手册在现代企业级DevOps体系中,Kubernetes(K8s)、Jenkins和Harbor组成的CI/CD流水...

git常用命令整理_git常见命令

一、Git仓库完整迁移完整迁移,就是指,不仅将所有代码移植到新的仓库,而且要保留所有的commit记录1.随便找个文件夹,从原地址克隆一份裸版本库...

第三章:Git分支管理(多人协作基础)

3.1分支基本概念分支是Git最强大的功能之一,它允许你在主线之外创建独立的开发线路,互不干扰。理解分支的工作原理是掌握Git的关键。核心概念:HEAD:指向当前分支的指针...

云效Codeup怎么创建分支并进行分支管理

云效Codeup怎么创建分支并进行分支管理,分支是为了将修改记录分叉备份保存,不受其他分支的影响,所以在同一个代码库里可以同时进行多个修改。创建仓库时,会自动创建Master分支作为默认分支,后续...

git 如何删除本地和远程分支?_git怎么删除远程仓库

Git分支对于开发人员来说是一项强大的功能,但要维护干净的存储库,就需要知道如何删除过时的分支。本指南涵盖了您需要了解的有关本地和远程删除Git分支的所有信息。了解Git分支...

git 实现一份代码push到两个git地址上

一直以来想把自己的博客代码托管到github和coding上想一次更改一次push两个地址一起更新今天有空查资料实践了下本博客的github地址coding的git地址如果是Gi...

git操作:cherry-pick和rebase_git cherry-pick bad object

在编码中经常涉及到分支之间的代码同步问题,那就需要cherry-pick和rebase命令问题:如何将某个分支的多个commit合并到另一个分支,并在另一个分支只保留一个commit记录解答:假设有两...

模型文件硬塞进 Git,GitHub 直接打回原形:使用Git-LFS管理大文件

前言最近接手了一个计算机视觉项目代码是屎山就不说了,反正我也不看代码主要就是构建一下docker镜像,测试一下部署的兼容性这本来不难但是,国内服务器的网络环境实在是恶劣,需要配置各种镜像(dock...

防弹少年团田柾国《Euphoria》2周年 获世界实时趋势榜1位 恭喜呀

当天韩国时间凌晨3时左右,该曲在Twitter上以“2YearsWithEuphoria”的HashTag登上了世界趋势1位。在韩国推特实时趋势中,从上午开始到现在“Euphoria2岁”的Has...