MySQL乱码及解决方案

发表于 更新于 分类于

Mysql 5.5 CharacterSet Configuration

Client Configuration

  • #1 No need to restart server for setting client

When

1
2
[client]
default-character-set = utf8

Then

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:		40
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.38-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
  • #2 It will change character_set_client & character_set_connection & character_set_results without client’s setting.

When

1
2
[client]
#default-character-set = utf8

Then

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Connection id:		37
Current database:	mall
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.38-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			14 min 4 sec

And Then

1
2
3
4
5
6
7
8
9
10
11
12
13
For a special DB.
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Server Configuration

  • #1 Restart mysql server without character-set-server=utf8

When

1
2
[mysqld]
#character-set-server=utf8

Then

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:		42
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.38-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			8 min 47 sec
  • #2 Create a new database newa without character-set-server=utf8

When

1
2
[mysqld]
# character-set-server=utf8

Then

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Connection id:		36
Current database:	newa
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.38-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			12 sec
  • #3 Restart server with character-set-server=utf8

When

1
2
[mysqld]
character-set-server=utf8

Then

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Connection id:		38
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.38-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			23 min 46 sec

But For newa DB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CConnection id:		38
Current database:	newa
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.38-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	***latin1***
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			22 min 36 sec

So That Means

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

conclusion

  • For client, connection, results, you should add
    [client] default-character-set = utf8

  • For server and DB, you should add
    [mysqld] character-set-server=utf8

  • You should drop original DB when created in error character set, just like newa DB