大网数据广告活动
服务器运维问答

Changing collation of all tables and columns in MySQL

· 52CE 我爱测

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 ',TABL…

问题原文

原帖链接 · seizouki · CC BY-SA 4.0

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 · CC BY-SA 4.0

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?

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2020-12-06)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。