MySQL – Change Collation of All Tables and Columns

MySQL

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

Best Answer

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?

Related Topic