转载请注明,靴靴ubuntu16.04下mysql建表无法输入中文解决方案_MendyMeng的博客-CSDN博客
考研复试科目有数据库,于是在自己Ubuntu16.04.1环境下面安装了mysql5.7.21,开始了SQL相关增删改查等语法的练习。就在建表时发现表里的数据项无法输入中文。上网找了好一阵子最后解决了。
无法添加中文时对数据库,库内表,及相关配置查询的提示如下:
在自建的SPJ库下,建表S,插入相应的值,当插入中文时报错,查看此表属性不支持中文输入:
mysql> create table S(SNO CHAR(3), SNAME CHAR(10), STATUS CHAR(2), CITY CHAR(10));
mysql> insert into S value ('S1','精益','20','天津');
ERROR 1366 (HY000): Incorrect string value: '\xE7\xB2\xBE\xE7\x9B\x8A' for column 'SNAME' at row 1
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |//此处为latin
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.04 sec)
mysql> show create table S;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------+
| S | CREATE TABLE `S` (
`SNO` char(3) DEFAULT NULL,
`SNAME` char(10) DEFAULT NULL,
`STATUS` char(2) DEFAULT NULL,
`CITY` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |//此处为latin
+-------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
---------------------------------------以下为网络摘抄,加上自己理解 - - ------------------------------------
character_set_client:指定的是的的Sql语句的编码;
character_set_connection:指定了MySQL的用来运行SQL语句的时候使用的编码;
character_set_database:服务器数据库中某个库使用的字符集设置,如果建立时没有指明,用将安装服务器时指定的字符集设置;
character_set_results:指定了SQL语句执行完返回数据的时候,会把数据转换为此种编码;
character_set_server:服务器安装时指定的默认字符集设置。
---------------------------------------以上为网络摘抄,加上自己理解 - - ------------------------------------
赶紧上百度谷歌了一下,原来是因为|character_set_database|latin1|字符集不允许中文输入,需要在/etc/mysql/my.cnf里面添加相应的以下的值才可以
顺序:停止mysql的服务 - 修改值 - 开启mysql的服务
root@Mendy:/etc/mysql#service mysql stop
root@Mendy:/etc/mysql#sudo gedit my.cnf
/*以下值加入my.cnf中*/
[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8
collat ion-server = utf8_general_ci
添加上面一些配置参数后
root@Mendy:/etc/mysql#service mysql start
(或添加完参数后service mysql restart)
进入mysql中
root@Mendy:mysql -u root -p
不进入任何库中,查询如下
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |//此处为utf8
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.04 sec)
进入SPJ库中查询仍为latin1
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |//此处为latin1
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.04 sec)
此SPJ库下所有表的编码仍是LATIN1
mysql> show create table S;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------+
| S | CREATE TABLE `S` (
`SNO` char(3) DEFAULT NULL,
`SNAME` char(10) DEFAULT NULL,
`STATUS` char(2) DEFAULT NULL,
`CITY` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |//此处为latin1
+-------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(说明在修改my.cnf相关参数之前,所有已建好的数据库编码方式不会再改变了,只有重新建立新的库才会应用新的参数,改变character_set_database的值)
删除之前建立的库,重新建库建表都可以输入中文了。
------------------------------------------以下为重新建库,建表,插入,查询等一波操作----------------------------
(SPJ为要操作的数据库名字,自取)
查看所有数据库
mysql> show databases;
删除之前建的SPJ库
mysql> drop database SPJ;
重新创建数据库
mysql> create database SPJ;
选择数据库
mysql> use SPJ;
建表
mysql> create table S(SNO CHAR(3), SNAME CHAR(10), STATUS CHAR(2), CITY CHAR(10));
Query OK, 0 rows affected (0.44 sec)
插入数据
mysql> insert into S value('S1','精益','20','天津');
Query OK, 1 row affected (0.07 sec)
查询
mysql> select * from S;
+------+--------+--------+--------+
| SNO | SNAME | STATUS | CITY |
+------+--------+--------+--------+
| S1 | 精益 | 20 | 天津 |
+------+--------+--------+--------+
1 row in set (0.00 sec)
全部都OK了!吼吼开森!~~
总结:修改字符集参数后,已定义好的库及表的字符集不会再修改,只有重新建库,建表新修改的参数才会生效。
本人拙笔,敬请赐教