使用information_schema查找所有达到其极限的整数列 [英] Find all integer columns which are reaching its limits using information_schema

问题描述

我可以获得要验证可用空间的所有列的列表.

I can get a list of all columns I want to verify the space available.

SELECT 
  TABLE_NAME, COLUMN_NAME, COLUMN_TYPE 
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  COLUMN_TYPE = 'int(11)' AND 
  TABLE_NAME LIKE 'catalog_category_entity%';

考虑到int(11)最多为2147483648(不考虑无符号)，我想从这个范围内计算出我正在使用多少.

Considering that int(11) is up to 2147483648 (not considering unsigned) I would like to calculate how much I am using from this range.

我个人可以这样检查:

select 
  max(value_id)/2147483648 as usage 
from 
  catalog_product_entity_int;

但是我想以一种不错的方式对第一个查询中找到的所有列进行处理.

But I would like to do each on a nice way for all the columns found on the first query.

在这种情况下，我想知道递归CTE是否是正确的资源，以及如何做到这一点，或者是否有更优雅的检查方法.

I would like to know if recursive CTE is the right resource in this case and how to do it or if there is a more elegant way of checking it.

我希望有一种不错的快速检查方法，而无需任何外部工具.

I would like to have this nice quick way of checking without any external tools.

我已经找到了Postgres的解决方案，但是我想知道我是否真的需要该功能. postgres:查找包含其整数的所有整数列当前的最大值

I've found this solution for postgres but I was wondering if I really need the function. postgres: find all integer columns with its current max value in it

推荐答案

我为此任务编写了一个解决方案，但我并不是唯一一个做过这样的事情的人.

I wrote a solution for this task, but I'm hardly the only person to have done something like this.

select concat('`', table_schema, '`.`', table_name, '`.`', column_name, '`') as `column`,
  auto_increment as `current_int`, max_int, round((auto_increment/max_int)*100, 2) as `pct_max`
from (select table_schema, table_name, column_name, auto_increment,
  pow(2, case data_type
    when 'tinyint'   then 7
    when 'smallint'  then 15
    when 'mediumint' then 23
    when 'int'       then 31
    when 'bigint'    then 63
    end+(column_type like '% unsigned'))-1 as max_int
  from information_schema.tables t
  join information_schema.columns c using (table_schema,table_name)
  join information_schema.key_column_usage k using (table_schema,table_name,column_name)
  where t.table_schema in ('test')
    and k.constraint_name = 'PRIMARY'
    and k.ordinal_position = 1
    and t.auto_increment is not null
) as dt;

https://github.com/billkarwin/bk-tools/blob/master/pk-full-ratio.sql

该查询是针对test模式的硬编码，因此您需要针对自己的模式对其进行编辑.

That query is hard-coded for the test schema, so you need to edit it for your own schema.

我的主键会溢出吗?"这个问题的简短答案.现在将其更改为BIGINT UNSIGNED.这肯定会一直持续到文明瓦解.

The short answer to the question of "is my primary key going to overflow?" is to just alter it to BIGINT UNSIGNED now. That will surely last until the collapse of civilization.

在同一个git repo中，我还有另一个类似的脚本来检查 all 整数列，而不仅仅是自动递增主键.但这与其他专栏文章无关.

In the same git repo, I have another similar script to check all integer columns, not just auto-increment primary keys. But it's not as much of a concern for other columns.

这篇关于使用information_schema查找所有达到其极限的整数列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！