MySQL修改字符编码

Linux 修改字符编码：

/etc/my.cnf配置文件修改：

设置默认字符集
[client]
其他客户端，比如 pymysql
default-character-set=utf8
prompt="MySQL [\d]> "
no-auto-rehash
[mysql]
mysql 客户端
default-character-set=utf8
[mysqld]
服务器端的字符集
character-set-server = utf8
服务器端的排序规则
collation-server = utf8_unicode_ci

/etc/my.cnf

查看数据库各层字符编码：

show variables like '%char%';
+--------------------------+-------------------------------------+------
| Variable_name | Value |......
+--------------------------+-------------------------------------+------
| character_set_client | utf8 |...... -- 客户端字符集
| character_set_connection | utf8 |......
| character_set_database | latin1|...... -- 数据库字符集
| character_set_filesystem | binary |......
| character_set_results | utf8 |......
| character_set_server | latin1|...... -- 服务器字符集
| character_set_system | utf8 |......
| character_sets_dir | D:\MySQL Server 5.0\share\charsets\ |......
+--------------------------+-------------------------------------+------

在创建数据库时指定默认的编码格式

create database 数据库名 CHARACTER SET utf8 COLLATE utf8_general_ci;

修改已创建的数据库的编码

>alter database 数据库表名 CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> alter database x_t character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

修改表的字符编码

mysql> alter table student default character set utf8;

修改字段的字符编码：

mysql> alter table student change sname sname char(20) character set utf8;  

mysql> alter table student change sname sname char(10);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table student modify column sname char(20);
Query OK, 4 rows affected (0.06 sec)
Records![图片.png](https://img.haomeiwen.com/i18938642/4881ceff0598cf50.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
: 4  Duplicates: 0  Warnings: 0