Mysql不能插入中文和中文显示乱码问题

来源：互联网发布：昆山数据库培训编辑：程序博客网时间：2024/05/01 15:21

1、my.ini文件

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/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:\\App Data\\mysql-5.7.10-winx64datadir = D:\\App Data\\mysql-5.7.10-winx64\\dataport = 3306skip-grant-tables# 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 = 128Msort_buffer_size = 2Mread_rnd_buffer_size = 2M character-set-server=utf8collation-server=utf8_general_ci#STRICT_TRANS_TABLES: may lead unable to write chinese datas into db.sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER[client]# set default port and character set.port = 3306default-character-set=utf8# Add user and password.user = adminpassword = admin

2、Mysql不能插入中文数据

修改sql_mode的值如下，关键是去掉STRICT_TRANS_TABLES：

sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

3、Mysql中文显示乱码

在my.ini配置文件中已经将default-character-set和character-set-server改成了utf8，这会使新建的数据库和表默认使用utf8编码，但并不能解决原有数据表的中文乱码问题。因为在你创建的时候，它们已经使用了别的编码格式（也许是latin1）。所以要把它们使用的编码格式改为utf8或者gbk，乱码问题才能解决。

方法一：

mysql> SET NAMES utf8;

这种方法在某些机器上有效，在我的电脑上并不生效，但也可以一试。

方法二：

1、修改数据库的编码格式

使用下面命令查看数据库编码格式：
```
show create database db_name;
```

若编码格式不是utf8，修改编码格式：

alter database db_name default character set utf8 collate utf8_general_ci;

2、修改表的编码格式

查看表的编码格式：
```
show create table table_name;
```

修改表编码格式：

alter table table_name default character set utf8 collate utf8_general_ci;

3、修改表的编码格式时，列的编码格式会跟着改变。若列的编码格式没有变，则需要手动修改列的编码格式：

修改列编码格式：

alter table table_name change old_column_name new_column_name varchar(20) character set utf8 not null;

0 0