0

I have a few tables in a database using MySQL (The engine is MyISAM). I am trying to find the lengths of the longest strings in each column (all of the data is text). Right now I am trying to run code that looks like: 

SELECT LENGTH(col) FROM schema.table ORDER BY LENGTH(col) DESC LIMIT 1;

I have been manually typing in the column names and table names. However, continuing this will take forever.

At the very least, is there a way for me to assign a variable and replace the two instances of col with that variable so I don't have to type it repeatedly?

It would be even better if I could, for a given table, iterate over all the columns and get back the length of the longest string in each column.

Thanks!

EDIT: Hybridzz, neither of those two links solves my problem; I can't seem to get MySQL to accept a variable assignment.

I also tried these four searches beforehand: MySQL: Assign column value to variable

Setting columns variably in Mysql

https://stackoverflow.com/questions/2642677/how-to-use-database-name-as-a-value

mysql, iterate through column names

Community
  • 1
  • 1
djsavvy
  • 106
  • 1
  • 2
  • 9
  • better search first before post. – Sin Jun 19 '14 at 01:47
  • http://stackoverflow.com/questions/5648420/get-all-columns-from-all-mysql-tables http://stackoverflow.com/questions/5416392/get-column-name-from-mysql I got these two when i did a quick search here – Sin Jun 19 '14 at 01:49
  • 1
    You can get column names from the `INFORMATION_SCHEMA` database. To use them, you need to write dynamic SQL. – Barmar Jun 19 '14 at 01:50

1 Answers1

0

The basic SQL that you want is:

select max(length(col1)), max(length(col2)), . . .
from table t;

You can get the expression for all columns in the table that are varchar by querying information_schema.columns:

select group_concat('max(length(', column_name, '))' separator ', ')
from information_schema.column
where data_type = 'varchar' and table_name = 'table' and table_schema = 'schema';

You can then copy the expressions into a select. Or put them into a string and use prepare.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786