mysql字符集修改 Incorrect string value 1366 13919

小熊 2022年9月9日MySQL评论134 views1148字阅读3分49秒阅读模式

修改数据库字符集:

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];如:ALTER DATABASE test DEFAULT CHARACTER SET utf8mb4;

把表默认的字符集和所有字符列改为新的字符集:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]如:ALTER TABLE logtest CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

只是修改表的默认字符集:

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];如:ALTER TABLE logtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

修改字段的字符集:

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

查看数据库编码:

SHOW CREATE DATABASE db_name;

查看表编码:

SHOW CREATE TABLE tbl_name;

查看字段编码:

SHOW FULL COLUMNS FROM tbl_name;

查看当前数据库编码:

SHOW VARIABLES LIKE 'character_set_%';

修改配置文件:

临时,重启失效

set global character_set_server=utf8mb4;
set global character_set_database=utf8mb4;
set global character_set_client=utf8mb4;
set global character_set_connection=utf8mb4;

修改mysql的my.cnf文件中的字符集,永久,重启生效

default-character-set = utf8mb4
character_set_server = utf8mb4
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

参考 https://cloud.tencent.com/developer/article/1642652

weinxin
公众号
在号内与我交流,回复【资源】获取技术大礼包
小熊