Fork me on GitHub

mysql数据库存储过程参数接收表情符号

 1 DROP PROCEDURE IF EXISTS `Ai_Promote`.`APWXUserAdd`
 2 DELIMITER ;;
 3 CREATE DEFINER=`root`@`%` PROCEDURE `Ai_Promote`.`APWXUserAdd`(_openid VARCHAR(50), _shopid VARCHAR(20), _shopname VARCHAR(100), _photo VARCHAR(200), _nickname VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, _phone VARCHAR(20), _city VARCHAR(50), _country VARCHAR(50), _province varchar(50), _session_Key VARCHAR(200))
 4 BEGIN
 5     DECLARE allCount INTEGER DEFAULT 0;
 6     DECLARE t_error INTEGER DEFAULT 0;
 7     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
 8     START TRANSACTION;
 9       SELECT COUNT(*) INTO allCount FROM `t_wx_user` WHERE shopid = _shopid and shopname = _shopname and openid = _openid;
10       IF allCount = 0 THEN
11             INSERT INTO `Ai_Promote`.`t_wx_user`(openid, shopid, shopname, photo, nickname, phone, phoneNumber, countryCode, role, city, country, province, session_key, add_date, unionid, gender, update_date) 
VALUES(_openid, _shopid, _shopname, _photo, _nickname, _phone, _phone, '86', '', _city, _country, _province, _session_key, NOW(), '', '', NOW()); 12 END IF; 13 IF t_error=1 THEN 14 SELECT 0; 15 ROLLBACK; 16 ELSE 17 SELECT 1; 18 COMMIT; 19 END IF; 20 END 21 ;; 22 DELIMITER ;

上面是个存储过程的例子,据说是往数据库表里面写微信小程序用户的信息的

重点看这个_nickName参数

_nickname VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,

因为有些用户的昵称是个表情,像我遇到的,一个昵称就是🍓

如果只是_nickname VARCHAR(200) 这样,参数会因接收到特殊字符而报错

另外nickname这个数据库字段也要设置一下

 

posted @ 2021-08-20 13:34  雪山玉龙  阅读(126)  评论(0编辑  收藏  举报