CODEWITHSUNDEEP
mysql
jeyraof
jeyraof

Reputation: 893

Every columns' max length of contents

I have planing to migrate utf8 varchar(255) columns to utf8mb4. I know utf8 column having index must be reduced to varchar(191), so I have to check every columns' current maximum value of it's contents.

SELECT s.TABLE_SCHEMA             as table_schema,
       s.TABLE_NAME               as table_name,
       s.COLUMN_NAME              as column_name,
       s.INDEX_TYPE               as index_type,
       c.DATA_TYPE                as data_type,
       c.CHARACTER_MAXIMUM_LENGTH as max_length
FROM information_schema.STATISTICS s
JOIN information_schema.COLUMNS c
ON
    s.TABLE_SCHEMA = c.TABLE_SCHEMA AND
    s.TABLE_SCHEMA = 'schema_name' AND
    s.TABLE_NAME = c.TABLE_NAME AND
    s.COLUMN_NAME = c.COLUMN_NAME AND
    c.DATA_TYPE = 'varchar' AND
    c.CHARACTER_MAXIMUM_LENGTH > 191;

This is every varchar columns having index and it's length is higher than 191.

Now, I expect that max contents length of every columns.

I tried to this:

SELECT s.TABLE_SCHEMA             as table_schema,
       s.TABLE_NAME               as table_name,
       s.COLUMN_NAME              as column_name,
       s.INDEX_TYPE               as index_type,
       c.DATA_TYPE                as data_type,
       c.CHARACTER_MAXIMUM_LENGTH as max_length,
       (select max(length(s.COLUMN_NAME)) from concat(s.TABLE_SCHEMA, '.', s.TABLE_NAME))
FROM information_schema.STATISTICS s
JOIN information_schema.COLUMNS c
ON
    s.TABLE_SCHEMA = c.TABLE_SCHEMA AND
    s.TABLE_SCHEMA = 'schema_name' AND
    s.TABLE_NAME = c.TABLE_NAME AND
    s.COLUMN_NAME = c.COLUMN_NAME AND
    c.DATA_TYPE = 'varchar' AND
    c.CHARACTER_MAXIMUM_LENGTH > 191;

but it doesn't works.

How can i get that result?

Upvotes: 1

Views: 333

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562280

The following does not do what you intend it to:

select max(length(s.COLUMN_NAME)) from concat(s.TABLE_SCHEMA, '.', s.TABLE_NAME))

The result of the concat() is not a table identifier. It's a string. You can't SELECT from a string.

One of the basic principles of SQL is that identifiers must be fixed at the time the query is parsed. You can't make any query that selects from dynamic columns or tables whose names are determined from the results of the query itself.

Instead, you must do this in two steps. First, run a query to get the list of schema/table/column that you wish to query. Second, execute new queries formatted using the names you got from the results of the first query. Only this way can the identifiers be fixed in your new queries at the time they are parsed.

Another tip: current versions of MySQL and InnoDB allow indexes on columns larger than you think. In MySQL 5.6 and earlier, the default max index size was 768 bytes, but could be configured to be 3072 bytes with the following changes:

  • set innodb_large_prefix=1
  • alter table <name> row_format=dynamic

In MySQL 5.7, these are the defaults, so you shouldn't need to do anything to get the greater max index size. But if you created tables before you upgraded to 5.7, you may have to ALTER TABLE to get the new row format.

In MySQL 8.0, the innodb_large_prefix option is removed because it is always enabled anyway.

Upvotes: 2

Related Questions