查看数据库编码
# 需要指定数据库
USE db_name;
SELECT @@character_set_database, @@collation_database;
# 不需要指定数据库
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';
查看表编码
# 查看单个表方法1
USE db_name;
SHOW CREATE TABLE table_name;
# 查看单个表方法2
SELECT T.table_name , CCSA.character_set_name FROM information_schema.tables T,information_schema.collation_character_set_applicability
CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "db_name" AND T.table_name = "table_name";
# 查看指定的数据库所有表
SELECT T.table_name , CCSA.character_set_name FROM information_schema.tables T,information_schema.collation_character_set_applicability
CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "db_name";
# 查看所有的数据库所有表
SELECT T.table_schema, T.table_name , CCSA.character_set_name FROM information_schema.tables T,information_schema.collation_character_set_applicability
CCSA WHERE CCSA.collation_name = T.table_collation;
## 查看表中某个字段编码
SELECT table_schema, table_name, character_set_name FROM information_schema.columns WHERE table_schema = "db_name" AND table_name = "table_name" AND column_name = "column_name";
## 查看表中所有字段编码,查看数据库中所有表所有字段编码
## 依次去除上面语句中查询条件
修改数据库编码到UTF8
ALTER DATABASE db_name CHARACTER SET utf8;
转换表和表的列编码到UTF8
USE db_name;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;
批量转换表和表的列编码到UTF8
#!/bin/bash
echo "输入Mysql管理员密码"
read -s dbpwd
echo "输入要修改的数据库"
read db
# 修改DB编码
mysql -uroot -p${dbpwd} -e "ALTER DATABASE ${db} CHARACTER SET utf8;"
# 导出表到临时文件
mysql -uroot -p${dbpwd} -e "USE ${db};SHOW TABLES" > _tmp_${db}_to_utf8
# 处理每个表
for line in `cat _tmp_${db}_to_utf8`
do
# 跳过导出结果中的表头
if [ "$line" == "Tables_in_${db}" ]
then
continue
fi
mysql -uroot -p${dbpwd} ${db} -e "ALTER TABLE ${line} CONVERT TO CHARACTER SET utf8;"
done
修改Mysql默认编码
/etc/mysql/mysql.conf.d/mysqld.cnf
# 在[mysqld]节下面添加一行
character-set-server=utf8
/etc/mysql/conf.d/mysql.cnf
# 在[mysql]节下面添加一行
default-character-set=utf8
重启mysql