修改MySQL数据库字符集
2024-09-29 23:32:08
Preface
I've demonstrated how to change character set in Oracle database in my previous blog.Now,I'm gonna do the similar operation in MySQL database,Let's see the difference of details.
Example
Create a test table.
root@localhost:mysql3306.sock [zlm]>create table charset(
-> id int,
-> name varchar()
-> ) engine=innodb charset=utf8;
Query OK, rows affected (0.01 sec)
Check the character set.
root@localhost:mysql3306.sock [zlm]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database: zlm
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: 2 min sec
Insert a record contains Chinese characters into test table.
root@localhost:mysql3306.sock [zlm]>insert into charset values(,'黎明');
Query OK, row affected (0.00 sec) root@localhost:mysql3306.sock [zlm]>select * from charset;
+------+--------+
| id | name |
+------+--------+
| | 黎明 |
+------+--------+
row in set (0.00 sec)
Change the character from utf8 to to gbk.
root@localhost:mysql3306.sock [zlm]>set @@global.character_set_database=gbk;
Query OK, rows affected, warning (0.00 sec) root@localhost:mysql3306.sock [zlm]>set @@global.character_set_server=gbk;
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [zlm]>show global variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.-linux-glibc2.-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]>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.-linux-glibc2.-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
rows in set (0.00 sec)
Start a new mysql client and check the data in test table.
[root@zlm1 :: ~]
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost:mysql3306.sock [(none)]>show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.-linux-glibc2.-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [(none)]>select * from charset;
ERROR (3D000): No database selected
root@localhost:mysql3306.sock [(none)]>use zlm //After execute "use database",the character set of database will turn into utf8 again.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
root@localhost:mysql3306.sock [zlm]>select * from charset;
+------+--------+
| id | name |
+------+--------+
| | 黎明 |
+------+--------+
row in set (0.00 sec) root@localhost:mysql3306.sock [zlm]>select length('黎明') from dual;
+------------------+
| length('黎明') |
+------------------+
| | //The length of one Chinese character occupys three bytes.It depends on the character set of table.
+------------------+
row in set (0.00 sec) //The data still shows correct result after change the database and server character set to gbk. root@localhost:mysql3306.sock [zlm]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database: zlm
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: gbk
Db characterset: utf8 //The character set of database turns back to utf8.Therefore,no messy code appears.
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.052
-------------- root@localhost:mysql3306.sock [zlm]>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 | gbk |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.-linux-glibc2.-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
rows in set (0.01 sec)
Set the character set again in curren session to gbk.
root@localhost:mysql3306.sock [zlm]>set character_set_database=gbk;
Query OK, rows affected, warning (0.00 sec) root@localhost:mysql3306.sock [zlm]>show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.-linux-glibc2.-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]>select * from charset;
+------+--------+
| id | name |
+------+--------+
| | 黎明 |
+------+--------+
row in set (0.00 sec) //Change the character set of client tool(mine is Xshell) to gbk. root@localhost:mysql3306.sock [zlm]>select * from charset;
+------+--------+
| id | name |
+------+--------+
| | 榛庢槑 | //After changing the character set of client tool,the messy code occurs.
+------+--------+
row in set (0.00 sec)
Change the character set of client tool back to utf8 and insert another record into test table.
root@localhost:mysql3306.sock [zlm]>select * from charset;
+------+--------+
| id | name |
+------+--------+
| | 黎明 |
+------+--------+
row in set (0.00 sec) root@localhost:mysql3306.sock [zlm]>insert into charset values(,'上海');
Query OK, row affected (0.00 sec) root@localhost:mysql3306.sock [zlm]>select * from charset;
+------+--------+
| id | name |
+------+--------+
| | 黎明 |
| | 上海 |
+------+--------+
rows in set (0.00 sec) //The changing of character set from utf8 to gbk does not influence the result of Chinese characters.
Change the character set of database & server to utf8 again.Then,change the character set of client & connection to gbk.
root@localhost:mysql3306.sock [zlm]>set character_set_database=utf8;
Query OK, rows affected, warning (0.01 sec) root@localhost:mysql3306.sock [zlm]>set character_set_server=utf8;
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [zlm]>set names gbk;
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [zlm]>\s
--------------
mysql Ver 14.14 Distrib 5.7., for linux-glibc2. (x86_64) using EditLine wrapper Connection id:
Current database: zlm
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: gbk
Conn. characterset: gbk
UNIX socket: /tmp/mysql3306.sock
Uptime: hour min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.038
-------------- root@localhost:mysql3306.sock [zlm]>root@localhost:mysql3306.sock [zlm]>select * from charset;
+------+------+
| id | name |
+------+------+
| | hķ |
| | ʏº£ |
+------+------+
rows in set (0.00 sec) //The messy code occured after I've changed the character of my client tool to utf8.
Insert the third record with Chinese characters.
root@localhost:mysql3306.sock [zlm]>insert into charset values(,'中国');
ERROR (HY000): Incorrect string value: '\xAD\xE5\x9B\xBD' for column 'name' at row //It doesn't permit your insertion operation now 'cause they'll be messy code again.
Summary
- Cheracter set in MySQL does not make a large influence even though it has so many variables which may confuse us.
- We can specify character set in a single table or even a column of the table which oracle cannot support.
- In order to avoid messy code,make sure to keep character set of connection is bigger or equal with the one of our client tool.
- It's reccomended to use utf8 even utf8mb4 as the character set of MySQL database because it can support almost all the languages
- Notice that the character set of database may change after you execute "use xxx" to choose a target database.
最新文章
- mysql根据汉字拼音排序查询
- db2实例、数据库、表空间
- elasticsearch开启脚本及使用
- 我的javascript学习之路（一）对象之基础
- iOS－default.png启动图片
- 如何将SQL Server运行到Windows Azure上
- web service1简单的例子用jdk自带的类
- 打造比Dictionary还要快2倍以上的字查找类
- NancyFx 2.0的开源框架的使用-HosingOwin
- ORA-00001: unique constraint (...) violated并不一定是数据冲突
- Linux文件管理和编辑常用命令
- kafka服务自动关闭
- mysql国内镜像下载网址
- es6 javascript对象方法Object.assign()
- C#中释放数据库连接资源
- 43.oracle同义词
- Python学习---Django的基础操作180116
- 51nod 1190 最小公倍数之和 V2
- EBS登陆界面IE显示异常
- copy deepcopy辨析