Mysql变量插入中文失败

当给变量赋值中文时，报错：

mysql> create procedure pro_test4()
    -> begin
    -> declare height int default 175;
    -> declare description varchar(50) default '';
    -> if height>=180 then
    ->   set description='高挑';
    -> elseif height>=170 then
    ->   set descriptioin='标准';
    -> else
    ->   set description='一般';
    -> end if;
    -> select concat('身高',height,'对应的身材类型为：',description);
    -> end$

　　

ERROR 1366 (HY000): Incorrect string value: '\xE6\xA0\x87\xE5\x87\x86' for column 'description' at row 1

 

修改方法：

1、查询当前数据库字符集类型：

mysql> show variables like 'character%'$
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

 

 

2、修改当前数据库字符集类型：

mysql> alter database db1 character set utf8;
    -> $
Query OK, 1 row affected (0.00 sec)

mysql> show variables like 'character%'$
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

 

3、重新执行存储过程成功：

mysql> call pro_test4()$
+-------------------------------------------------------------------+
| concat('身高',height,'对应的身材类型为：',description)            |
+-------------------------------------------------------------------+
| 身高175对应的身材类型为：标准                                     |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)