MySQL select min and max length of all table columns

If you want to query min and max length of all columns of a single table you can do it in two steps:

• help query to collect column data
• aggregated query which returns the final result

This will work also in other DB like Oracle with few modifications.

MySQL select max length all columns

The first example will be for selecting the size of the longest records in a table. This is useful when you want to optimize a given table according to the data inside.

prepare help query

The first step is to get column information for a given table. After that we are using the column names and concatenate them with select parts in order to get:

select MAX(CHAR_LENGTH(id)), 'id' FROM test

note that table name is test and column name is id

The first query looks like:

SET sql_mode='PIPES_AS_CONCAT';
 
SELECT 'select MAX(LENGTH('||`COLUMN_NAME` || ')), ''' ||COLUMN_NAME  ||  ''' FROM test UNION'
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='test' 
    AND `TABLE_NAME`='test';

Some points to consider:

• In order to enable concatenation with pipes in MySQL we run:

SET sql_mode='PIPES_AS_CONCAT';

You are free to rewrite it with concat functions

• You need to modify:

  • TABLE_SCHEMA - with your own - 1 place
  • TABLE_NAME - with your table - 2 places

• CHAR_LENGTH vs LENGTH - the first in measuring the character length while the second is getting the size in bytes. Which one you will use depends on your needs.

• For null columns - with only NULL records inside you will get NULL value for max, min and average

Select max length per columns in MySQL

For the final query you need to be connected to your schema otherwise you will need to add the schema in your output. You will need to delete the final UNION from the result of previous query. This would give you this query for getting max length

select MAX(LENGTH(id)), 'id' FROM test UNION
select MAX(LENGTH(year)), 'year' FROM test UNION
select MAX(LENGTH(fiscaltEnd)), 'testEnd' FROM test UNION
select MAX(LENGTH(flag1)), 'flag1' FROM test UNION
select MAX(LENGTH(flag2)), 'flag2' FROM test UNION
select MAX(LENGTH(flag3)), 'flag3' FROM test UNION
select MAX(LENGTH(result)), 'result' FROM test UNION
select MAX(LENGTH(fisRes)), 'fisRes' FROM test UNION
select MAX(LENGTH(startDate)), 'startDate' FROM test UNION
select MAX(LENGTH(endTime)), 'endTime' FROM test 

result:

MAX(CHAR_LENGTH(id)),id
1,id
4,year
\N,fiscalEnd
4,flag1
...

MySQL select min length all columns

Getting the minimum length of all columns can be done in similar way as the max one.

prepare help query to get min length

SET sql_mode='PIPES_AS_CONCAT';
 
SELECT 'select MIN(LENGTH('||`COLUMN_NAME` || ')), ''' ||COLUMN_NAME  ||  ''' FROM test UNION'
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='test' 
    AND `TABLE_NAME`='test';

Select min lenght per columns in MySQL

Of course you can get the same result in many different ways. For example you can use excel file to do concatenation and get this final query - once you have the column names:

select MIN(CHAR_LENGTH(id)), 'id' FROM test UNION
select MIN(CHAR_LENGTH(year)), 'year' FROM test UNION
select MIN(CHAR_LENGTH(fiscalEnd)), 'testEnd' FROM test UNION
select MIN(CHAR_LENGTH(flag1)), 'flag1' FROM test UNION
select MIN(CHAR_LENGTH(flag2)), 'flag2' FROM test UNION
select MIN(CHAR_LENGTH(flag3)), 'flag3' FROM test UNION
select MIN(CHAR_LENGTH(result)), 'result' FROM test UNION
select MIN(CHAR_LENGTH(fisRes)), 'fisRes' FROM test UNION
select MIN(CHAR_LENGTH(startDate)), 'startDate' FROM test UNION
select MIN(CHAR_LENGTH(endTime)), 'endTime' FROM test

MAX(CHAR_LENGTH(id)),id
1,id
4,year
\N,fiscalEnd
2,flag1
...

MySQL select average length all columns

The selection for average length is almost identical to previous examples:

SET sql_mode='PIPES_AS_CONCAT';
 
SELECT 'select AVG(LENGTH('||`COLUMN_NAME` || ')), ''' ||COLUMN_NAME  ||  ''' FROM test UNION'
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='test' 
    AND `TABLE_NAME`='test';

and the final result:

select AVG(CHAR_LENGTH(id)), 'id' FROM fiscal UNION
select AVG(CHAR_LENGTH(year)), 'year' FROM fiscal UNION
select AVG(CHAR_LENGTH(fiscalEnd)), 'fiscalEnd' FROM fiscal UNION
select AVG(CHAR_LENGTH(flag1)), 'flag1' FROM fiscal UNION
select AVG(CHAR_LENGTH(flag2)), 'flag2' FROM fiscal UNION
select AVG(CHAR_LENGTH(flag3)), 'flag3' FROM fiscal UNION
select AVG(CHAR_LENGTH(result)), 'result' FROM fiscal UNION
select AVG(CHAR_LENGTH(fisRes)), 'fisRes' FROM fiscal UNION
select AVG(CHAR_LENGTH(startDate)), 'startDate' FROM fiscal UNION
select AVG(CHAR_LENGTH(endTime)), 'endTime' FROM fiscal

Table used in the model