mysql編碼修改

技術小胖子發表於2017-11-09
MySql

檢視mysql指定資料庫編碼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show create database php;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| php      | CREATE DATABASE `php` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

檢視資料庫服務和客戶端字符集，mysql預設編碼是gbk的：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.17, for Win32 (x86)
Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.17 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 6 min 13 sec
Threads: 1  Questions: 481  Slow queries: 0  Opens: 260  Flush tables: 1  Open t
ables: 66  Queries per second avg: 1.289
--------------
1
2
mysql> set names utf8;//臨時修改編碼，重啟資料庫還原為預設編碼
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.6.17, for Win32 (x86)
Connection id:          1
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.17 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 2 min 26 sec
Threads: 1  Questions: 10  Slow queries: 0  Opens: 70  Flush tables: 1  Open tab
les: 63  Queries per second avg: 0.068
--------------

永久生效，修改my.ini檔案

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

default-character-set=utf8 //加上這一行，重啟mysql服務

1
2
3
4
5
mysql> status
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8


修改某個資料庫編碼

1
2
3
4
5
6
7
8
mysql> alter database res_gul character set utf8;
 
mysql> use res_gul
mysql> status
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8


設定資料庫編碼 /etc/my.cnf

[mysqld]

default-character-set=utf8

…

[client]

default-character-set=utf8


重啟資料庫後生效

1
2
3
4
5
mysql> status
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8

      本文轉自ling118 51CTO部落格，原文連結：http://blog.51cto.com/meiling/1625441，如需轉載請自行聯絡原作者


相關文章