MySQL乱码问题及字符集实战

 

mysql> create database oldboy;
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> show create database oldboy\G;
*************************** 1. row ***************************
Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> create table students (id int(4) not null auto_increment,name char(20) not null,primary key (`id`));
Query OK, 0 rows affected (0.06 sec)

mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | alex |
| 2 | simon |
| 3 | ys |
| 5 | ?? |
| 6 | ??? |
+----+-------+
5 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from students;
+----+-----------+
| id | name |
+----+-----------+
| 1 | alex |
| 2 | simon |
| 3 | ys |
| 5 | 永三 |
| 6 | 李永三 |
+----+-----------+
5 rows in set (0.00 sec)

mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | alex |
| 2 | simon |
| 3 | ys |
| 5 | ?? |
| 6 | ??? |
+----+-------+
5 rows in set (0.00 sec)

mysql> insert into students values(7,'李永三');
Query OK, 1 row affected (0.05 sec)

mysql> select * from students;
+----+-----------+
| id | name |
+----+-----------+
| 1 | alex |
| 2 | simon |
| 3 | ys |
| 5 | ?? |
| 6 | ??? |
| 7 | 李永三 |
+----+-----------+
6 rows in set (0.00 sec)

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

mysql> show create table students\G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

不乱码的思想：建议中英文混合的环境选择utf8

Linux：
[yongsan@yz3110 mysqldump]$ cat /etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

客户端
临时：
mysql> set names utf8;
或更改mysql.cnf的配置参数，永久生效：
[yongsan@yz3110 ~]$ cat /usr/local/mysql55/start/my.cnf |grep set
character-set-server = utf8
character_set_client = utf8

服务器
更改mysql.cnf的参数
[mysqld]
character-set-server = utf8
character_set_client = utf8

库、表、程序字符集
mysql> create database ys default character set utf8;
Query OK, 1 row affected (0.03 sec)