MYSQL 存储过程 转换格式/字符串查询

查询结果中,将INT格式转为VARCHAR格式

-- Name : selectBuildUserInfo
-- Description : 会员标示LOG查询
-- Explain : 从服务器
-- IN : userid BIGINT(30) - PID
-- OUT : username,usernameType,addDatetime,sysId,isVip 结果集

DELIMITER $$

DROP PROCEDURE IF EXISTS `center`.`selectBuildUserInfo` $$
CREATE PROCEDURE `selectBuildUserInfo`(IN userid BIGINT(30))
BEGIN

-- CAST将数据转为VARCHAR格式


  SELECT `userName`,CAST(`usernameType` as char(10)) as usernameType,`addDatetime`,`sysId`,`isVip` FROM `member_log` WHERE `pid`=userid;
END $$

DELIMITER ;

在做查询的时候,依然要用引号把变量引起来,否则查不出来结果

SET @tempcountsql = CONCAT('SELECT COUNT(`id`) INTO @ssscount_all FROM ',@tab_name,' AS t2 WHERE t2.username="',username,'"');

SELECT COUNT(`id`) AS `rowcount` FROM `member` WHERE `username`=CONCAT("'",username,"'") AND `usernameType`=usernameType;

但是在插入的时候,不需要用引号

INSERT INTO `gold_coins` (`pid`,`username`,`goldCoinsUpdateDatetime`) VALUE (userid,username,CONCAT(CURRENT_DATE(),' ',CURRENT_TIME()));