Mysql存储过程中字符串分割

来源：互联网发布：海口百度最seo 编辑：程序博客网时间：2024/04/30 10:54

今在项目中碰到了要把字符串分割，记录下来，以后可能还用的到

首先想上我的存储过程代码

DELIMITER $$USE `bplate`$$DROP PROCEDURE IF EXISTS `lp_plate_insertplateinfo`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `lp_plate_insertplateinfo`(plate_user INT,plate_title VARCHAR(60) ,plate_type INT,plate_img VARCHAR(500),plate_content MEDIUMTEXT,plate_sku_name VARCHAR(60),plate_sku_price VARCHAR(60)    )BEGINDECLARE i INT DEFAULT 0; SET @sql1=CONCAT("INSERT INTO bp_plate(`plate_user`,`plate_title`,`plate_type`,`plate_img`,`plate_sales`,`plate_content`,`plate_level`,`plate_time`,`plate_state`) VALUE(",plate_user,",'",plate_title,"',",plate_type,",'",plate_img,"',",0,",'",plate_content,"',0,NOW(),1)");PREPARE sql1 FROM @sql1;EXECUTE sql1;SET @plate_id='';SELECT LAST_INSERT_ID() INTO @plate_id FROM bp_plate LIMIT 0,1;SET @arraylength=1+(LENGTH(plate_sku_name) - LENGTH(REPLACE(plate_sku_name,',','')));WHILE i<@arraylengthDOSET i=i+1;SET @result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(plate_sku_name,',',i)),',',1)); SET @resultprice = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(plate_sku_price,',',i)),',',1));INSERT INTO bp_plate_sku(`plate_id`,`sku_name`,`sku_price`,`sku_time`,`sku_state`) VALUE(@plate_id,@result,@resultprice,NOW(),1);END WHILE;    END$$DELIMITER ;

这是我整个存储过程，分割的是传进来的

plate_sku_name VARCHAR(60),plate_sku_price VARCHAR(60)

变量，注意类型不能是int，不然不能分割

然后只要分割方法在于

SET @arraylength=1+(LENGTH(plate_sku_name) - LENGTH(REPLACE(plate_sku_name,',','')));WHILE i<@arraylengthDOSET i=i+1;SET @result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(plate_sku_name,',',i)),',',1)); SET @resultprice = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(plate_sku_price,',',i)),',',1));INSERT INTO bp_plate_sku(`plate_id`,`sku_name`,`sku_price`,`sku_time`,`sku_state`) VALUE(@plate_id,@result,@resultprice,NOW(),1);END WHILE;

@arraylength获取要分割字符串根据符号分割后的数组长度

之后便是一个while循环

中间用

 REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(plate_sku_name,',',i)),',',1));

方式来分割

里面的‘，’是根据要分割的字符，plate_sku_name是要分割的字符串，变量i要从1开始

之后便能分割了

0 0