Changing collation of all tables and columns in MySQL

Question

I tried to do this: https://serverfault.com/a/65572/603515

But I changed it slightly to target only the database I want to change.

mysql -B -N --host=localhost --user=root --password=secret \
      -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',
          TABLE_NAME,' charset=utf8mb4_bin;')
        from information_schema.TABLES WHERE TABLE_SCHEMA = 'usda_nndsr'; 
     select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',
         COLUMN_NAME,' charset=utf8mb4_bin;')
         from information_schema.COLUMNS WHERE TABLE_SCHEMA ='usda_nndsr';" |
     mysql --host=localhost --user=root --password=secret

but it's telling me:

mysql: mysql: [Warning] Using a password on the command line interface can be insecure.[Warning] Using a password on the command line interface can be insecure.
ERROR 1115 (42000) at line 1: Unknown character set: 'utf8mb4_bin'

I want utf8mb4_bin because I heard MySQL utf8 is not real utf8 using only 3 bytes.

How can I edit my query or command to make the change. Currently it is the default swedish collation.

MySQL version:

$ mysql -V
mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper

Rick James · Accepted Answer · 2020-12-07T00:05:59.460

2

utf8mb4_bin is a "Collation", not a "character set". The corresponding charset is simply utf8mb4.

Versions before 5.5 did not have charset utf8mb4.

There is no simply to convert all text columns of all table in an entire database.

This will convert all text (VARCHAR and TEXT) columns of a single table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

I would not trust the complex mysql that you propose, even after fixing the charset. There are several issues that I am unclear on when using -e. Instead, I would run the SELECT to generate a list of ALTERs, then copy&paste those into mysql.

Changing the "table" only changes the default charset for any future columns you might add.

CONVERT TO changes the columns -- both altering the encoding in the data and changing the definition of the column. Are the columns currently utf8? What version of MySQL are you running?

edited Dec 07 '20 at 00:05

answered Dec 06 '20 at 22:25

Rick James

2,558

You mean just run this query and copy and paste the resulting SQL queries?SELECT CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,'charset=utf8mb4_bin;') from information_schema.TABLES WHERE TABLE_SCHEMA = 'usda_nndsr'; select CONCAT('alter table', TABLE_SCHEMA, '.', TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8mb4_bin;') from information_schema.COLUMNS WHERE TABLE_SCHEMA = 'usda_nndsr'; – seizouki Dec 06 '20 at 22:40
Change the charset. Add two more schemas to the exclusion list. Are the two SELECTs different? – Rick James Dec 06 '20 at 22:45
I'm sorry but as a novice I don't even know what you mean by add two more schemas so I'm going to do it manually. – seizouki Dec 06 '20 at 22:49
@seizouki - Be sure not to change any tables in the databases named mysql. – Rick James Dec 08 '20 at 00:12
There were some issues with not being able to change collation of columns used as foreign keys. – seizouki Dec 08 '20 at 05:24
@seizouki - Drop the FKs; make the charset changes; then recreate the FKs. While you are at it, make sure you end up with 'identical' specifications for any columns involved in a JOIN. – Rick James Dec 08 '20 at 17:40
by specifications, do you mean things like character set and collation? – seizouki Dec 09 '20 at 00:11
@seizouki - Yes. Also numeric (INT, etc) versus text (VARCHAR, etc). That is don't JOIN and INT in one table to a VARCHAR in another table. (Sizes and NULL are not critical.) – Rick James Dec 09 '20 at 01:52

Changing collation of all tables and columns in MySQL

1 Answers1