mysql默认的编码方式是latin1，通过以下命令查看
show variables like 'char%';
或者：
show variables like 'character%';
mysql> show variables like "character%";
+--------------------------+----------------------------+
| Variable_name| Value |
+--------------------------+----------------------------+
| character_set_client |latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system |latin1 |
| character_sets_dir | /opt/lampp/share/charsets/ |
+--------------------------+----------------------------+
从以上信息可知数据库的编码为latin1，需要修改为gbk或者是utf8；
其中，character_set_client为客户端编码方式；
character_set_connection为建立连接使用的编码；
character_set_database数据库的编码；
character_set_results结果集的编码；
character_set_server数据库服务器的编码；
修改mysql默认编码方式：
修改mysql的配置文件my.ini，该文件目录一般为/etc/my.ini
找到客户端配置[client]在下面添加
default-character-set=utf8默认字符集为utf8
在找到[mysqld]添加
default-character-set=utf8默认字符集为utf8
init_connect='SET NAMES utf8'（设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行）
修改后大致如下：
[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockdefault-character-set=utf8init_connect='SET NAMES utf8'character_set_server=utf8user=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[client]default-character-set=utf8
修改好后，重新启动mysql即可，重新查询数据库编码可发现编码方式的改变：
show variables like 'char%';
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name| Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
查看mysql数据库编码方式：
show create database mydatabase;
show create database redmine;
+----------+------------------------------------------------------------------+
| Database | Create Database|
+----------+------------------------------------------------------------------+
| redmine | CREATE DATABASE `redmine` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
从以上看出数据库的默认编码方式为latin1
修改mysql数据库编码方式：
alter database mydatabase character set utf8;
show create database redmine;
+----------+------------------------------------------------------------------+
| Database | Create Database|
+----------+------------------------------------------------------------------+
| redmine | CREATE DATABASE `redmine` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
可以看到数据库的编码方式变为了utf8
查看mysql数据表编码方式
show create table mydatabase；
| projects | CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`description` text,
`homepage` varchar(255) DEFAULT '',
`is_public` tinyint(1) NOT NULL DEFAULT '1',
`parent_id` int(11) DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`updated_on` datetime DEFAULT NULL,
`identifier` varchar(255) DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '1',
`lft` int(11) DEFAULT NULL,
`rgt` int(11) DEFAULT NULL,
`inherit_members` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_projects_on_lft` (`lft`),
KEY `index_projects_on_rgt` (`rgt`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
可以看到projects表的编码方式为latin1
修改mysql数据表编码方式
alter table projects convert to character set utf8 collate utf8_general_ci;
以上projects为我要查看的表
| projects | CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`description` text,
`homepage` varchar(255) DEFAULT '',
`is_public` tinyint(1) NOT NULL DEFAULT '1',
`parent_id` int(11) DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`updated_on` datetime DEFAULT NULL,
`identifier` varchar(255) DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '1',
`lft` int(11) DEFAULT NULL,
`rgt` int(11) DEFAULT NULL,
`inherit_members` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_projects_on_lft` (`lft`),
KEY `index_projects_on_rgt` (`rgt`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
此时projects表的编码方式已经变为utf8
参考地址：
/frinder/article/details/7041723
/db/18042.html
/topics/390728070?page=1
