MySQL存储过程报错 [err] 1366 - incorrect string value: '\xe7\x9f\xa5\xe8\xaf\x86...' for column 'v_name' at row 1

创建了类似以下的MySQL存储过程：

CREATE PROCEDURE test_procedure()
BEGIN
DECLARE i int;
DECLARE v_name VARCHAR(100);
SET i = 0;
WHILE i < 1024 DO
SET v_name = CONCAT('中文字符_', i);
INSERT INTO mytable(name, parent_id,parent_name,ancestor_ids,level, create_uid, create_time, update_uid,update_time,status)
VALUES (v_name,-1, '', '', 1, 2, '2020-11-30 14:44:29', 2, '2020-12-10 20:21:47', 1);
SET i = i+1;
END WHILE;
END;

插入中文字符，调用此存储过程时报错：

[err] 1366 - incorrect string value: '\xe7\x9f\xa5\xe8\xaf\x86...' for column 'v_name' at row 1

查看数据默认的字符编码：

> SHOW VARIABLES LIKE 'character%';
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server utf8mb4
character_set_system utf8
character_sets_dir /usr/local/mysql/share/charsets/

原因是character_set_database默认是latin1。

方法一：修改character_set_database的值为utf8或者utf8mb4，然后重建存储过程。

方法二：在建表或者存储过程显示指定字符的编码。

DECLARE v_name VARCHAR(100) character set utf8;

重建存储过程，插入中文字符串成功。