mysql使用substring分割参数
代码中常常应到字符串的分割,其实有时数据库中的数据也需要进行字符串分割,比如按用户名查找用户信息,如果是传入一个用户名当然好实现,若是想要一次传入多个用户名则需要考虑一下,传入多个参数当然是不现实的,如果把多个用户名合并到一个字符串中,用都好分割开来,是一个不错的选择,所以可以使用CONCAT,substring,INSTR等函数配合使用来达到目的,实例代码如下:
DROP PROCEDURE IF EXISTS TestSubString;
CREATE PROCEDURE TestSubString (IN paramAccount VARBINARY(512))
BEGIN
SET @result = 1;
SET @paramAccountNames = paramAccount;
SET @run = 1;
SET @querySql ='';
WHILE( @run = 1) DO
SET @dotIndex= INSTR(@paramAccountNames,',');
IF(@dotIndex = 0) THEN
BEGIN
SET @userId = @paramAccountNames;
SET @run=0;
SET @querySql = CONCAT(@querySql,' AccountName=\'', @userId, '\'');
END;
ELSE
BEGIN
SET @userId = substring(@paramAccountNames,1,@dotIndex-1);
SET @paramAccountNames=substring(@paramAccountNames,@dotIndex+1,LENGTH(@paramAccountNames));
SET @querySql = CONCAT(@querySql,' AccountName=\'', @userId, '\'', ' or');
END;
END IF;
END WHILE;
SET @prepareStatement = CONCAT('SELECT AccountName as id, Level FROM accountinfo WHERE ', @querySql, ' order by id;');
PREPARE stmt FROM @prepareStatement;
EXECUTE stmt ;
SELECT @prepareStatement;
SELECT @querySql;
SET @result = 1;
END