确定一个字段中的最大字符数

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.

我正在处理的MySQL daabase的字段大小是非常任意的，我想根据它的当前数据来检查它们。因此，我想确定每个字段的最大字符数，因此我确信在更新字段大小时不会丢失任何数据。

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

phpmyadmin或SQL语句中有什么特性可以帮助我吗?

Thanks

谢谢

2 个解决方案

#1

use CHAR_LENGTH, ex

使用CHAR_LENGTH,例

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

• SQLFiddle Demo
• SQLFiddle演示

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,

如果列数未知，也可以使用动态SQL。您需要提供的是数据库的名称和表的名称，

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;

• SQLFiddle Demo
• SQLFiddle演示

#2

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

在phpadmin myadmin中，您可以通过表的结构选项卡了解这些数据。请看附件图片: