Alter charset and collation in all columns in all tables in MySQL

Question

I need to execute these statements in all tables for all columns.

alter table table_name charset=utf8;
alter table table_name alter column column_name charset=utf8;

Is it possible to automate this in any way inside MySQL? I would prefer to avoid mysqldump

Update: Richard Bronosky showed me the way :-)

The query I needed to execute in every table:

alter table DBname.DBfield CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Crazy query to generate all other queries:

SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';

I only wanted to execute it in one database. It was taking too long to execute all in one pass. It turned out that it was generating one query per field per table. And only one query per table was necessary (distinct to the rescue). Getting the output on a file was how I realized it.

How to generate the output to a file:

mysql -B -N --user=user --password=secret -e "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';" > alter.sql

And finally to execute all the queries:

mysql --user=user --password=secret < alter.sql

Thanks Richard. You're the man!

score 20 · Accepted Answer · edited Apr 13 '17 at 12:14

First of all, don't just take my word for it! Test my suggestion out with this:

select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10;

If you feel good with the outcome of that, remove the limit clauses and save the output to an SQL script or, get fancy and pipe the output directly to mysql similar to what I demonstrate here. That would look like this:

mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema'; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" | mysql --host=prod-db1 --user=admin --password=secret

When you start thinking about using valid SQL to generate valid SQL, it changes the whole game. You will be amazed by how many uses you find for it.

ok I've got it. I'm adding the correct sintax to the answer. Your idea rules — The Disintegrator, Sep 16 '09 at 15:17

score 8 · Answer 2 · 2010-08-20T21:01:57.823

8

Actually, you can use CONVERT TO on a table to have it convert all columns within that table to the charset and collation.

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename';

Also, it makes more sense to me to select the actual database you want to do this on. So this:

... WHERE TABLE_SCHEMA = 'databasename';

rather than this:

... WHERE TABLE_SCHEMA != 'information_schema';

But I guess if you really wanted to do it on all tables, you could use the former. Seems a bit heavy handed to me though. :)

edited Aug 20 '10 at 21:01

answered Aug 20 '10 at 20:56

For me, that outputs a list of commands that will change the collation etc, but doesn't actually run them. Is it supposed to? – Max Williams Apr 16 '20 at 15:31
You also need to filter on `TABLE_TYPE = 'BASE TABLE'` to exclude views – cowls Jun 22 '20 at 08:38

score 2 · Answer 3 · answered Mar 15 '14 at 18:15

To change collation on all columns i used

SELECT CONCAT(  'ALTER TABLE ',  `TABLE_NAME` ,  ' CHANGE `',  `COLUMN_NAME` ,  '` `',`COLUMN_NAME` ,  '` ',  `DATA_TYPE` ,  '(',  `CHARACTER_MAXIMUM_LENGTH` ,  ') CHARACTER SET utf8 COLLATE utf8_swedish_ci ;' ) FROM  `COLUMNS` WHERE  `TABLE_SCHEMA` =  <schema> AND  `COLLATION_NAME` !=  'utf8_swedish_ci' ORDER BY  `TABLE_NAME` ,  `ORDINAL_POSITION` ;

score 0 · Answer 4 · answered May 06 '22 at 03:42

Alter all columns in all tables where character set = utf8mb3

select concat('ALTER TABLE ',TABLE_Name, ' MODIFY ',COLUMN_NAME, ' ' , COLUMN_TYPE, ' CHARACTER SET utf8mb4;') command from information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DB NAME HERE' and CHARACTER_SET_NAME = 'utf8mb3';

Then Run result.

score 0 · Answer 5 · edited May 04 '17 at 13:53

0

You can use the information_schema database to find the column and table you have to alter. You can find them with:

SELECT table_name, column_name FROM information_schema.`COLUMNS`
WHERE table_schema='your database' AND collation_name LIKE 'latin%';

Then you can automate the alter with a SQL script, store procedure or with your preferred development language.

edited May 04 '17 at 13:53

Pau Ruŀlan Ferragut

105
4

answered Sep 14 '09 at 08:34

lg.

4,579
3
20
20

score 0 · Answer 6 · answered Jan 12 '20 at 10:31

I ended up doing the following:

dumped the database to a sql file, using mysqldump db_name > db_name.sql
replace the charset in the sql file using awk sed -i -e 's/old_charset/new_charset/g' db_name.sql
import this new database file mysql < db_name

I wouldn't use this in production, but I needed it in a local dev environment.

Alternativelly, I could have dumped the SQL structure and data separately, and then went into the SQL file to change the charsets there.

Keeping in mind that if you're converting between incompatible charsets, this might mess up your data.

Alter charset and collation in all columns in all tables in MySQL

6 Answers6

Linked