linux mysql字符集_linux中mysql的字符集问题总结

在mysql无法插入中文一文中所遇到的问题，在其他的博客上看到很多解决办法，但是很多试了一下并不能解决问题，这里进行总结一下方法和可能出现的情况：

1.修改my.cnf改变编码

shell> vim /etc/mysql/my.cnf

5.5以前系统，在【client】下面加入 default-character-set=utf8

[client]

default-character-set=utf8

5.5以后系统，如下修改：

[client]

default-character-set=utf8

[mysqld]

default-storage-engine=INNODB

character-set-server=utf8

collation-server=utf8_general_ci

修改结束后，重启mysql，查看如下

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

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

8 rows in set (0.00 sec)

mysql> use test;

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> 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 | utf8 |

| character_set_system | utf8 |

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

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

8 rows in set (0.00 sec)

全局的character_set_database编码确实是已经修改了，但是具体到之前已经建好的数据库，他的编码并没有修改，插入依旧是有问题的。

重新创建数据库，建表，发现创建出来的数据库和表默认编码都修改过来了

mysql> create database coding;

Query OK, 1 row 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/soft/mysql/share/charsets/ |

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

8 rows in set (0.00 sec)

//创建数据库表，不设置编码，即使用默认编码

mysql> create table t1(id int not null, name varchar(100));

Query OK, 0 rows affected (0.02 sec)

//查看数据库表的编码

mysql> show table status like 't1'\G

*************************** 1. row ***************************

Name: t1

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2019-07-28 16:36:26

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

//查看数据库表中的字段编码

mysql> show full columns from t1\G

*************************** 1. row ***************************

Field: id

Type: int(11)

Collation: NULL

Null: NO

Key:

Default: NULL

Extra:

Privileges: select,insert,update,references

Comment:

*************************** 2. row ***************************

Field: name

Type: varchar(100)

Collation: utf8_general_ci

Null: YES

Key:

Default: NULL

Extra:

Privileges: select,insert,update,references

Comment:

2 rows in set (0.00 sec)

这样看来，修改my.cnf是对今后所创建的数据库起作用，修改的是mysql默认的字符集，但是不能修改已经创建好的数据库编码，已经创建好的需要重新进行修改。这里建议在修改了单个数据库之后，最好是将全局的默认编码也一并改过来，省的以后在出现相似的问题

2.通过set方法修改数据库编码后，重启服务编码又变回原来的编码

通过SET character_set_database = utf8;修改了字符集之后，重新建表发现表的Collation还是latin1_swedish_ci，重启mysql服务发现数据库的编码又变回原来的编码了

以下通过四种方式进行查验：

mysql> set character_set_database = utf8;

Query OK, 0 rows affected (0.00 sec)

通过系统参数查看数据库编码方式

//第一种方式：查看数据库字符集、排序规则。(需要切换默认数据库)

mysql> use test;

Database changed

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

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

8 rows in set (0.01 sec)

//第二种查看方式

mysql> show create database test\G

*************************** 1. row ***************************

Database: test

Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */

1 row in set (0.00 sec)

//第三种查看方式

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name="test"\G

*************************** 1. row ***************************

SCHEMA_NAME: test

DEFAULT_CHARACTER_SET_NAME: latin1

DEFAULT_COLLATION_NAME: latin1_swedish_ci

1 row in set (0.00 sec)

//第四种查看方式

mysql> SELECT @@character_set_database, @@collation_database;

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

| @@character_set_database | @@collation_database |

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

| utf8 | utf8_general_ci |

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

1 row in set (0.00 sec)

通过上面可以发现，通过set character_set_database可以将系统数据中的编码信息修改过来，但是创建数据库语句和information_schema中的信息并没有修改，编码依旧是latin1；

完全修改数据库编码可以通过下面的方式修改：

//修改数据库编码

mysql> alter database test default character set utf8 collate utf8_general_ci;

Query OK, 1 row 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/soft/mysql/share/charsets/ |

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

8 rows in set (0.01 sec)

//第二种查验方式

mysql> show create database test\G

*************************** 1. row ***************************

Database: test

Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.00 sec)

//第三种查看方式

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name="test"\G

*************************** 1. row ***************************

SCHEMA_NAME: test

DEFAULT_CHARACTER_SET_NAME: utf8

DEFAULT_COLLATION_NAME: utf8_general_ci

1 row in set (0.00 sec)

//第四种查看方式

mysql> SELECT @@character_set_database, @@collation_database;

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

| @@character_set_database | @@collation_database |

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

| utf8 | utf8_general_ci |

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

1 row in set (0.00 sec)

//创建表进行查看，发现新创建的表的编码正确

mysql> create table t4(id int,name varchar(100));

Query OK, 0 rows affected (0.02 sec)

mysql> show table status like 't4'\G

*************************** 1. row ***************************

Name: t4

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2019-07-28 17:08:09

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

从上面可以看出，通过这种方式进行修改可以同时将数据库字符集完全修改过来

3.数据库的字符集等都没有问题，但还是插入失败

此时可以考虑一下是否是本地终端的问题，通过env指令查看，找到LANG字段，如果不是en_US.UTF-8可能也会造成无法输入中文：

LANG=en_US.UTF-8

通过一下方式修改

export LANG=en_US.UTF-8