Search code examples
mysqlvarchar

MySQL Determine longest VarChar length

I am trying to optimize my database. In order to do so, I need to be able to determine the longest data entry in a varchar column, and then trim the column definition to just above that.

How can I find out, using sql, the length of the longest varchar entry in my table?

CREATE TABLE `poller_output` (
  `local_data_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `rrd_name` varchar(19) NOT NULL DEFAULT '',
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `output` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`local_data_id`,`rrd_name`,`time`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8
Solution

  • If you want to know the max length of the field output, for example, you can use this query:

    SELECT Max(CHAR_LENGTH(`output`)) AS Max FROM `poller_output`