mysql 存储过程 字符串分割字符串_Mysql存储过程中字符串分割

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

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

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)

)

BEGIN

DECLARE 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

DO

SET 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

DO

SET 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开始

之后便能分割了