转载请注明,靴靴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/f里面添加相应的以下的值才可以
顺序:停止mysql的服务 - 修改值 - 开启mysql的服务
root@Mendy:/etc/mysql#service mysql stop
root@Mendy:/etc/mysql#sudo gedit f
/*以下值加入f中*/[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)
(说明在修改f相关参数之前,所有已建好的数据库编码方式不会再改变了,只有重新建立新的库才会应用新的参数,改变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了!吼吼开森!~~
总结:修改字符集参数后,已定义好的库及表的字符集不会再修改,只有重新建库,建表新修改的参数才会生效。
本人拙笔,敬请赐教
如果觉得《ubuntu16.04下mysql建表无法输入中文解决方案》对你有帮助,请点赞、收藏,并留下你的观点哦!