MySQL——修改数据库和表的字符编码

修改编码：

（1)先停止服务

（2）修改my.ini文件

（3）重新启动服务说明：

如果是在修改my.ini之前建的库和表，那么库和表的编码还是原来的Latin1，要么删了重建，要么使用

alter语句修改编码。

mysql> create database 0728db charset Latin1; Query OK, 1 row affected (0.00 sec)

mysql> use 0728db; Database changed

 

mysql> alter table student charset utf8; #修改表字符编码为UTF8 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table student\G *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL, #字段仍然是latin1编码PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> alter table student modify name varchar(20) charset utf8; #修改字段字符编码为UTF8 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table student\G *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

mysql> show create database 0728db;; +--------+-----------------------------------------------------------------+ |Database| Create Database                                                   | +------+-------------------------------------------------------------------+ |0728db| CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET latin1 */ | +------+-------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter database 0728db charset utf8; #修改数据库的字符编码为utf8 Query OK, 1 row affected (0.00 sec)

 

mysql> create table student (id int , name varchar(20)) charset Latin1; Query OK, 0 rows affected (0.02 sec) mysql> show create table student\G *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> show create database 0728db; +--------+-----------------------------------------------------------------+ |Database| Create Database                                                   | +--------+-----------------------------------------------------------------+ | 0728db | CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET utf8 */ | +--------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)