CODEWITHSUNDEEP
mysqlsqlphpmyadmin
magdmartin
magdmartin

Reputation: 1787

Determine max number of character in a field

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

Upvotes: 3

Views: 4195

Answers (2)

John Woo
John Woo

Reputation: 263703

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;

Upvotes: 8

SaidbakR
SaidbakR

Reputation: 13534

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

Upvotes: -1

Related Questions