3

The field size of the MySQL daabase I'm working on have been determine quite arbitrary and I'd like to review them based on its current data. So I'd like to determine the maximum number of character per field so I'm sure I won't lose any data when I update the field size.

Is there a feature in phpmyadmin or a SQL statement that can help me?

Thanks

John Woo
  • 258,903
  • 69
  • 498
  • 492
magdmartin
  • 1,712
  • 3
  • 20
  • 43

2 Answers2

8

use CHAR_LENGTH, ex

SELECT MAX(CHAR_LENGTH(column1)) maxCol1,
       MAX(CHAR_LENGTH(column2)) maxCol2,
       MAX(CHAR_LENGTH(column3)) maxCol3
FROM tableName

You can also use Dynamic SQL if you have unknown number of columns. All you need to supply is the name of the database and the name of the table,

SET @db_Name = 'db_2_21a29';
SET @tb_name = 'TABLENAME';
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CHAR_LENGTH(', COLUMN_NAME, ')) AS `', COLUMN_NAME, '`')
  ) INTO @sql
FROM information_schema.columns
WHERE TABLE_NAME = @tb_name AND
      TABLE_SCHEMA = @db_Name;

SET @sql = CONCAT('SELECT ',@sql, 'FROM ', @tb_name);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
John Woo
  • 258,903
  • 69
  • 498
  • 492
-1

In phpmyadmin you are able to know this data from the Structure tab of your table. look at the attached image:

enter image description here

SaidbakR
  • 13,303
  • 20
  • 101
  • 195
  • I wonder! Why the down vote? The question is tagged [tag:phpmyadmin] and the OP is clear in the last line of the question: "Is there a feature in phpmyadmin or a SQL statement that can help me? " – SaidbakR Aug 21 '16 at 18:33
  • The question is how to find the longest string in each field while your answer indicates how to find the field max length set in the table schema. – magdmartin Oct 26 '16 at 13:11