首先在数据库里面输入

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/share/mysql/charsets/ |

| validate_password_special_char_count | 1                          |

+--------------------------------------+----------------------------+

9 rows in set (0.00 sec)

mysql> set character_set_database=utf8;                   #更改编码属性

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_server=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_client=gbk;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_connection=gbk;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_client=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_connection=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> 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/mysql/charsets/ |

| validate_password_special_char_count | 1                          |

+--------------------------------------+----------------------------+

9 rows in set (0.00 sec)

mysql> use test1

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

mysql> showtables

    -> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'showtables' at line 1

mysql> show tables;

+-----------------+

| Tables_in_test1 |

+-----------------+

| address         |

| authors         |

| book_m2m_author |

| books           |

| customer        |

| student         |

| study_record    |

+-----------------+

7 rows in set (0.00 sec)

mysql> insert into books (name) value('我')；

    -> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '；' at line 1

mysql> insert into books (name) value('我');

ERROR 1366 (HY000): Incorrect string value: '\xE6\x88\x91' for column 'name' at row 1

mysql> alter table books convert to character set utf8;           #将数据表的编码方式改成utf8

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into books (name) value('我');

Query OK, 1 row affected (0.01 sec)

mysql> select * from books;

+----+------+----------+

| id | name | pub_date |

+----+------+----------+

|  1 | bbu1 | NULL     |

|  2 | bbu2 | NULL     |

|  3 | bbu3 | NULL     |

|  4 | 我   | NULL     |

+----+------+----------+

4 rows in set (0.00 sec)

mysql> select * from books;

+----+--------------+----------+

| id | name         | pub_date |

+----+--------------+----------+

|  1 | bbu1         | NULL     |

|  2 | bbu2         | NULL     |

|  3 | bbu3         | NULL     |

|  4 | 我           | NULL     |

|  5 | 我的世界     | NULL     |

+----+--------------+----------+

5 rows in set (0.00 sec)

mysql>

设置MYSQL数据库编码为UTF-8

1、编辑MySql的配置文件

MySql的配置文件Windows下一般在系统目录下或者在MySql的安装目录下名字叫my.ini，可以搜索，Linux下一般是/etc/my.cnf

--在 [mysqld] 标签下加上以下内容：

default-character-set = utf8

character_set_server = utf8

注意：如果此标签下已经存在“default-character-set=GBK”类似的内容，只需修改即可。

--在 [mysql] 标签下加上一行

default-character-set = utf8

--在 [mysql.server]标签下加上一行

default-character-set = utf8

--在 [mysqld_safe]标签下加上一行

default-character-set = utf8

--在 [client]标签下加上一行

default-character-set = utf8

2、重新启动MySql服务

Windows可在服务管理器中操作，也可使用命令行：

net stop mysql 回车

net start mysql 回车

服务名可能不一定为mysql，请按自己的设置

Linux下面可是用 service mysql restart

如果出现启动失败，请检查配置文件有没有设置错误

3、查看设置结果
登录MySql命令行客户端：打开命令行

mysql –uroot –p 回车

输入密码

进入mysql后执行：show variables like "% character %";
显示结果应该类似如下：

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

如果仍有编码不是utf8的，请检查配置文件，也可使用mysql命令设置：

set character_set_client = utf8;

set character_set_server = utf8;

set character_set_connection = utf8;

set character_set_database = utf8;

set character_set_results = utf8;

set collation_connection = utf8_general_ci;

set collation_database = utf8_general_ci;

set collation_server = utf8_general_ci;

另外：

建立数据库时可以使用以下命令：

create database app_relation character set utf8;

use app_relation;

source app_relation.sql;

修改数据库编码的命令为：

alter database app_relation character set utf8;

巴特西

centos7中mysql不能输入中文问题的解决

设置MYSQL数据库编码为UTF-8

最新文章

热门文章