find all the columns of a database with %latin1% value

Question

How could I do this SQL query?

Name Database: database_1
Column: Collation
Value: latin1_swedish_ci

I need the list of ALL "database_1" tables that have columns whose value contains the text %latin1%.

My final goal: I need to change these values in all the tables that contain this data, for example:

CHARACTER SET latin1 COLLATE latin1_swedish_ci

Change to:

CHARACTER SET utf8 COLLATE utf8_unicode_ci

I already have the consult ready for replacement:

ALTER TABLE `blog_sitemapconf` CHANGE `value` `value` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_english_ci NOT NULL DEFAULT '';

But I need to get the name of all tables that contain columns with

CHARACTER SET latin1 COLLATE latin1_swedish_ci

Answer 1

You can query this information from the information_schema.columns table:

SELECT table_name, column_name
FROM   information_schema.columns
WHERE  collation_name LIKE '%latin1%'

Answer 2

First, determine how you will be doing the charset conversion. If the table is correctly encoded in latin1, then this is the desired conversion:

 ALTER TABLE tbl CONVERT TO CHARACTER SET utf8;

If, instead, you have utf8 bytes stored in latin1 columns, then the 'fix' is more complex.

However, that changes all CHAR/TEXT columns in the table. Is that OK?

If that is OK, then this will generate all the ALTERs.

SELECT DISTINCT
       CONCAT("ALTER TABLE ", table_schema, ".", table_name,
              " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;")
    FROM   information_schema.columns
    WHERE  character_set = 'latin1'
      AND  table_schema NOT IN ('mysql', 'information_schema', 'performance_schema');

You can then copy/paste them into the mysql commandline tool.

If you need to change only individual columns, then the fix is difficult because the rest of the attributes of each column must be repeated. This is not easy to do in a query.