Mysql变量插入中文失败

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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)