MySQL中无法输入中文解决办法

最新推荐文章于 2024-03-04 14:46:21 发布

夜舞精狼

最新推荐文章于 2024-03-04 14:46:21 发布

阅读量3.1k

点赞数 2

分类专栏：数据库

本文链接：https://blog.csdn.net/a913909626/article/details/119821604

版权

数据库专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍了如何在MySQL中解决由于字符集设置不当导致无法输入中文的问题，包括修改数据库、表和列的编码，并给出了从创建数据库开始设置utf8编码的建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

1. 无法输入中文的原因
2. 解决办法

1. 无法输入中文的原因

我们来查看之前创建的表的详细信息

MariaDB [lxr]> show create table class;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `class_name` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |  
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

最后一行，CHARSET=latin1 没有设置编码格式，所以默认为Latin1

查了很多资料说将数据库编码改成utf8即可，但是我试了很多次还是报错

最终我尝试不仅将数据库的编码改成utf8，将数据内的表也改成uft8，而且还要改表里面需要输入中文的列

2. 解决办法

//修改数据库的编码
MariaDB [lxr]> alter database lxr character set utf8;
Query OK, 1 row affected (0.001 sec)

//修改表的编码
MariaDB [lxr]> alter table class character set utf8;
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

//修改表内某一列的编码
MariaDB [lxr]> alter table class change name name varchar(100) character set utf8;
Query OK, 2 rows affected (0.723 sec)              
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [lxr]> select * from class;
+----+------+------------+
| id | name | class_name |
+----+------+------------+
|  1 | lisi |          8 |
|  2 | cym  |         10 |
+----+------+------------+
2 rows in set (0.000 sec)

MariaDB [lxr]> update class set name = '李四' where id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [lxr]> select * from class;
+----+--------+------------+
| id | name   | class_name |
+----+--------+------------+
|  1 | 李四   |          8 |
|  2 | cym    |         10 |
+----+--------+------------+
2 rows in set (0.001 sec)

其实我们可以在创建数据库的时候就将编码设置成utf8就可以中文输入了

MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> alter database school character set utf8;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use school
Database changed
MariaDB [school]> show variables like'%char%';
+--------------------------+------------------------------+
| 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/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)

MariaDB [school]> create table student(name varchar(100),age tinyint,addr varchar(100));
Query OK, 0 rows affected (0.716 sec)

MariaDB [school]> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                          |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `name` varchar(100) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `addr` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [school]> insert student (name,age,addr) values ('李四',20,'武汉'),('张三',1,'武汉');
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [school]> select * from student;
+-----------+------+--------+
| name      | age  | addr   |
+-----------+------+--------+
| 李四    |   20 | 武汉   |
| 张三    |    1 | 武汉   |
+-----------+------+--------+
2 rows in set (0.000 sec)