mysql安装后中文插入不成功或者插入后乱码的问题 -

worldly

浏览: 43724 次
性别:
来自: 北京

最近访客更多访客>>

ave2010

jinchengwu

gps32251070

yetugeng

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

mysql安装后中文插入不成功或者插入后乱码的问题

博客分类：

MySQL

mysql插入中文乱码修改mysql字符集

1.在安装完MySQL后，首先应该修改字符编码统一，保证中文不会乱码：

查看字符集：

mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| 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/local/mysql-5.7.12-osx10.11-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.01 sec)
如上，除了character_set_filesystem和character_sets_dir 外，需要将其他修改统一为utf8，命令如下：
mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_system=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_connection=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_database=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_server=utf8;
Query OK, 0 rows affected (0.01 sec)

设置database和table字符集编码为utf8：
mysql>use Young_db
mysql>alter database Young_db character set utf8;
mysql>alter table user character set utf8;

创建时候指定编码集：
mysql>create database Young_db character set utf8;

2.当忘记修改就创建了database 或者 table 后修改命令如下：
查看数据库编码:

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

修改编码为utf-8:
mysql> alter database Young_db character set utf8;
Query OK, 1 row affected (0.00 sec)

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

查看数据表的编码：
mysql> use Young_db;
mysql> show create table users;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`userId` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`passwod` varchar(45) NOT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表的编码：
mysql> alter table users character set utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table users;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`userId` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
`passwod` varchar(45) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

总结：安装了MySQL后，建议首先统一字符集编码，可按照上文1中所述操作（不修改默认是Latin1），这样创建table时自动使用修改后的编码，不会出现中文乱码的问题；如果创建表时没有统一字符集编码，每次创建database和table时可以指定字符集编码，否则会出现中文乱码；如果上述两种均未遵循，insert操作会出现ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\x80\xE5\xA7\x8B...' for column 'name' at row 1的错误，需要按照2中描述修改database和table的字符集编码，后可以解决插入中文不成功及插入后乱码的问题。

分享到：