MySQL设置字符编码

一、8.0设置字符集

# vim /etc/mysql/my.cnf
[mysqld]
port=3306                            
character-set-client-handshake=FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci   #相对应的排序规则
init_connect='SET NAMES utf8mb4'


[client]    
default-character-set=utf8mb4


[mysql]    
default-character-set = utf8mb4  

修改后，重启mysql：sudo systemctl restart mysql.service

如果数据库已经创建好了，需要把修改编码。

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'

二、5.6设置字符编码配置

设置字符集

# vim /etc/mysql/my.cnf

# 对本地的mysql客户端的配置
[client]
default-character-set = utf8mb4

# 对其他远程连接的mysql客户端的配置
[mysql]
default-character-set = utf8mb4

# mysql服务器的配置
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

character-set-client-handshake = FALSE

修改后，重启mysql：sudo systemctl restart mysql.service

如果数据库已经创建好了，需要把修改编码。

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

三、设置字段字符编码

django迁移做外键关联会失败

1.自动生成sql语句

修改要查的字符编码：COLLATION_NAME = 'utf8_general_ci';执行mysql.sql语句，然后执行命令

mysql -uroot -p < mysql.sql > test.sql 生产sql文件

vim mysql.sql
# 输入一下内容
use devops;                                                                                                      
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', ';') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'devops'   and DATA_TYPE = 'varchar'   and COLLATION_NAME = 'utf8_general_ci';

2执行sql语句

source ./test.sql # 断关联执行SQL语句