Changing collation of all tables and columns in 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
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?