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

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

Thanks

回答1:

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'
                     );