检查表格列是否达到最大长度 [英] Check table columns reached max length

问题描述

只是想知道是否有一种方法可以检查表的列是否达到最大长度.

Just wondering whether there is a way to check columns of a table reached thire maximum length.

通过以下操作，我能够获取表列的长度

With following I was able to get the length of the table column

SELECT MAX(CHARACTER_MAXIMUM_LENGTH) AS LEN, COLUMN_NAME
FROM information_schema.columns
WHERE table_name IN ('TableA')
GROUP BY COLUMN_NAME 
ORDER BY LEN DESC

然后我想检查数据是否达到了该列的最大长度

And then I wanted to check whether those column reached its MAX length or not with data

下面给出了列的最大长度

Below gives the max length a column

SELECT MAX(LENGTH(Col1))
FROM TableA

由于不可能在WHERE子句中使用聚合函数，这怎么可能?

As it is not possible to use aggregate functions in WHERE clause, how it is possible?

谢谢

推荐答案

您可以使用join将数据整合在一起:

You can use join to bring the data together:

SELECT MaxLen, ActualLen
FROM (SELECT MAX(CHARACTER_MAXIMUM_LENGTH) AS MaxLen, COLUMN_NAME
      FROM information_schema.columns
      WHERE table_name IN ('TableA') AND COLUMN_NAME = 'COL1'
     ) m CROSS JOIN
     (SELECT MAX(LENGTH(Col1)) as ActualLen
      FROM TableA
     ) c;

我不确定您真正想要的是什么.如果要使行具有最大值:

I'm not sure what you actually want returned. If you want the rows with the maximum value:

select a.*
from tablea a
where length(col1) = (SELECT MAX(CHARACTER_MAXIMUM_LENGTH) AS MaxLen
                      FROM information_schema.columns
                      WHERE table_name IN ('TableA') AND COLUMN_NAME = 'COL1'
                     );

这篇关于检查表格列是否达到最大长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！