在MySQL的表中插入中文字符的时候,就会发生一些错误, 或者显示乱码
插入之后显示的并不是中文字符,而是乱码。
- 首先看一下我的MySQL的字符集设置
mysql> show variables like '%character%'
-> ;
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
- 系统变量中英文对应
character_set_server 默认的内部操作字符集
character_set_client 客户端来源数据使用的字符集
character_set_connection 连接层字符集
character_set_results 查询结果字符集
character_set_database 当前选中数据库的默认字符集
character_set_system 系统元数据(字段名等)字符集
库、表、列与系统变量之间的关系
1.建库若没有指定字符集,则采用character_set_server 所指定字符集
2.建表若未指定字符集,则采用所属库字符集
3.建列若未指定字符集,则采用所属表字符集
ps:也就是说,如果没有特殊设置,库中所有表和列的字符集应与默认字符集相同,例如:我的就是 latin1。字符集转换流程
从图中我们可以看出字符集的转换如下:
character_set_client–>character_set_connection–>内部操作字符集–>character_set_results
在这个转换中,我的character_set_client,character_set_connection,character_set_results都为utf8格式,可就是数据库的格式为latin1 ,所以才会出现转换的乱码。所以我们只需要修改数据库的字符集就好了,建议也为utf8。- 修改方法
1 .在创建表是添加一段语句
create table salary(
id int,
username varchaer(124),
dept varchar(124),
sal double,
cretime timestamp
) CHARSET=utf8;
2 .用命令行修改
(1)查看字符集编码设置
SHOW VARIABLES LIKE ‘%CHARACTER%’
mysql> show variables like '%character%'
-> ;
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
(2)查看数据库编码
SHOW CREATE DATABASE db_name;
mysql> show create database wl;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| wl | CREATE DATABASE `wl` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
(3)查看表字符集编码
SHOW CREATE TABLE tbl_name;
mysql> show create table emp;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE `emp` (
`empno` decimal(4,0) NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`job` varchar(9) CHARACTER SET latin1 DEFAULT NULL,
`mgr` decimal(4,0) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
`sal` decimal(7,2) DEFAULT NULL,
`comm` decimal(7,2) DEFAULT NULL,
`deptno` decimal(2,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(4)查看字段字符集编码
SHOW FULL COLUMNS FROM tbl_name;
mysql> show full columns from emp;
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| empno | decimal(4,0) | NULL | NO | | NULL | | select,insert,update,references | |
| ename | varchar(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| job | varchar(9) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| mgr | decimal(4,0) | NULL | YES | | NULL | | select,insert,update,references | |
| hiredate | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| sal | decimal(7,2) | NULL | YES | | NULL | | select,insert,update,references | |
| comm | decimal(7,2) | NULL | YES | | NULL | | select,insert,update,references | |
| deptno | decimal(2,0) | NULL | YES | | NULL | | select,insert,update,references | |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
8 rows in set (0.00 sec)
(5)设置字符集编码(设置character_set_client、character_set_connection、character_set_results)
SET NAMES xxx;
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
(6)修改数据库字符集(只能改变后续创建表,无法改变已创建表)
ALTER DATABASE DATABASENAME CHARACTER SET XXX;
mysql> show create database wl;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| wl | CREATE DATABASE `wl` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database wl character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database wl;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| wl | CREATE DATABASE `wl` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
(7)修改表的字符集
ALTER TABLE TABLENAME CHARACTER SET XXX;(只能改变后续添加数据)
ALTER TABLE TABLENAME CONVERT TO CHARACTER SET XXX;(全部重新写入)
mysql> show create table emp;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE `emp` (
`empno` decimal(4,0) NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`job` varchar(9) DEFAULT NULL,
`mgr` decimal(4,0) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
`sal` decimal(7,2) DEFAULT NULL,
`comm` decimal(7,2) DEFAULT NULL,
`deptno` decimal(2,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table emp character set utf8;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table emp;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE `emp` (
`empno` decimal(4,0) NOT NULL,
`ename` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`job` varchar(9) CHARACTER SET latin1 DEFAULT NULL,
`mgr` decimal(4,0) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
`sal` decimal(7,2) DEFAULT NULL,
`comm` decimal(7,2) DEFAULT NULL,
`deptno` decimal(2,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(8)修改列字符集
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];
mysql> SHOW FULL COLUMNS FROM emp;
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| empno | decimal(4,0) | NULL | NO | | NULL | | select,insert,update,references | |
| ename | varchar(10) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| job | varchar(9) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| mgr | decimal(4,0) | NULL | YES | | NULL | | select,insert,update,references | |
| hiredate | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| sal | decimal(7,2) | NULL | YES | | NULL | | select,insert,update,references | |
| comm | decimal(7,2) | NULL | YES | | NULL | | select,insert,update,references | |
| deptno | decimal(2,0) | NULL | YES | | NULL | | select,insert,update,references | |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
8 rows in set (0.00 sec)
mysql> ALTER TABLE emp CHANGE ename ename VARCHAR(10) CHARACTER SET utf8;
Query OK, 14 rows affected (0.41 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> SHOW FULL COLUMNS FROM emp;
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| empno | decimal(4,0) | NULL | NO | | NULL | | select,insert,update,references | |
| ename | varchar(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| job | varchar(9) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| mgr | decimal(4,0) | NULL | YES | | NULL | | select,insert,update,references | |
| hiredate | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| sal | decimal(7,2) | NULL | YES | | NULL | | select,insert,update,references | |
| comm | decimal(7,2) | NULL | YES | | NULL | | select,insert,update,references | |
| deptno | decimal(2,0) | NULL | YES | | NULL | | select,insert,update,references | |
+----------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
8 rows in set (0.00 sec)
若泽大数据交流群:671914634