MySQL解决插入数据乱码问题

首先配置

my.ini   

如果没有将原来的 my-default.ini 备份出一个

修改my.ini

 

     【1】在[client]节点下添加 （这个如果是另一种character_set_server=utf8 这样写我的会报错1067【mysql服务无法启动】，大家可以试试)  （这个是客户端的编码配置）
        default-character-set=utf8 
     【2】在[mysqld]节点下添加 （这个是服务器端编码的配置）（注：collation是排序方式）
       character-set-server=utf8 
      collation-server=utf8_general_ci 

 

修改后文件：

 

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir =D:/software/mysql-5.6.41-winx64
datadir =D:/software/mysql-5.6.41-winx64/data.
port = 3306
# server_id = .....

character-set-server=utf8
collation-server=utf8_general_ci

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
default-character-set=utf8

 

重启后查看：

 

 都修改完毕了，but对于表：

 

 修改方法：

                   ALTER DATABASE `数据库` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci  

                 ALTER TABLE `数据表` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci   如果失效 请看下面的实际操作

 比如：     ALTER DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

                 ALTER TABLE `category` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 如果不能成功则用这个指令：

                ALTER TABLE category CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

 也就是：要更改数据库的默认字符集为utf8，更改表的字符集为utf8，更改列的字符集为utf8。

 

重启Mysql 即可！

 计算机管理——》

 

 

关于mysql修改表、字段、库的字符集

 参考：http://fatkun.com/2011/05/mysql-alter-charset.html

 

修改数据库字符集：

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默认字符集：

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

查看数据库编码：

SHOW CREATE DATABASE db_name;

查看表编码：

SHOW CREATE TABLE tbl_name;

查看字段编码：

SHOW FULL COLUMNS FROM tbl_name;