创建了类似以下的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_clientutf8
character_set_connectionutf8
character_set_databaselatin1
character_set_filesystembinary
character_set_resultsutf8
character_set_serverutf8mb4
character_set_systemutf8
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;
重建存储过程,插入中文字符串成功。