MariaDB插入中文出现???情况

MariaDB插入中文出现???情况

本来打算创建一个测试表进行一个简单的实验，发现创建完python_test表后插入数据后，select发现所有中文都变成问号了，这一看就是出现了乱码
复制代码

MariaDB [lhc]> create table python_test(
-> id int(11),
-> name varchar(255),
-> class_time int(11),
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.03 sec)

MariaDB [lhc]>

复制代码
复制代码

/插入数据/
INSERT INTO python_test(id,name,class_time) value(1,‘字典’,3);
INSERT INTO python_test(id,name,class_time) value(2,‘列表’,2);
INSERT INTO python_test(id,name,class_time) value(3,‘函数’,5);
INSERT INTO python_test(id,name,class_time) value(4,‘装饰器’,2);
INSERT INTO python_test(id,name,class_time) value(5,‘迭代器’,2);

复制代码
复制代码

/查看数据/

MariaDB [lhc]> select * from python_test;
±—±----------±-----------+
| id | name | class_time |
±—±----------±-----------+
| 1 | ?? | 3 |
| 2 | ?? | 2 |
| 3 | ?? | 5 |
| 4 | ??? | 2 |
| 5 | ??? | 2 |
±—±----------±-----------+
5 rows in set (0.00 sec)

复制代码

首先查看下MariaDB的默认编码格式
复制代码

MariaDB [lhc]> show variables like “character_set_%”
-> ;
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | utf8 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+

复制代码

发现上面的数据库的字符编码不是为utf8
复制代码

/修改为utf8编码格式/

MariaDB [lhc]> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)

/查看/
MariaDB [lhc]> show variables like “character_set_%”;
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | utf8 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
8 rows in set (0.00 sec)

复制代码

查看数据还是问号，说明问题并不出现在数据库中，查看一下连接编码
复制代码

MariaDB [lhc]> show variables like “collation_%”;
±---------------------±------------------+
| Variable_name | Value |
±---------------------±------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
±---------------------±------------------+
3 rows in set (0.00 sec)

复制代码

可以发现问题就是出现在连接层。

解决方法
复制代码

/1/
SET NAMES ‘utf8’;
/*
它相当于下面的三句指令：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
*/

/2/
/创建数据库的时候指定编码格式/
MariaDB [lhc]> create database python_test character set utf8;

/3/
/创建表的时候指定编码格式/
MariaDB [lhc]> create table python_test(
-> id int(11),
-> name varchar(50) character set utf8,
-> class_time int(11),
-> PRIMARY KEY(id)
-> )DEFAULT CHARSET=utf8;

复制代码

如果已经创建完成的表或者数据库通过ALTER进行修改

/修改某一个数据库/
MariaDB [lhc]> ALTER database python_test character set utf8;

/修改某一个表/
MariaDB [lhc]> ALTER table python_test character set utf8;

再次查看原乱码表就不乱码了
复制代码

MariaDB [lhc]> select * from python_test;
±—±----------±-----------+
| id | name | class_time |
±—±----------±-----------+
| 1 | 字典 | 3 |
| 2 | 列表 | 2 |
| 3 | 函数 | 5 |
| 4 | 装饰器 | 2 |
| 5 | 迭代器 | 2 |
±—±----------±-----------+

复制代码