wcur LOCATE +
w字符串处理
1 DROP PROCEDURE IF EXISTS w_unique; 2 DELIMITER /w/ 3 CREATE PROCEDURE w_unique() 4 BEGIN 5 DECLARE done INT DEFAULT FALSE; 6 DECLARE w_wmax INT; 7 DECLARE w_grab_review_url VARCHAR(256); 8 DECLARE wcur CURSOR FOR SELECT MAX(grab_tab_review_pk) AS wmax, grab_review_url FROM grab_tab_review WHERE LENGTH(grab_review_url)>0 GROUP BY grab_review_url; 9 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 10 11 OPEN wcur; 12 read_loop: LOOP 13 FETCH wcur INTO w_wmax, w_grab_review_url; 14 IF done THEN LEAVE read_loop; 15 END IF; 16 SET @wtmp = CONCAT('DELETE FROM grab_tab_review WHERE grab_tab_review_pk!=',w_wmax,' AND grab_review_url="',w_grab_review_url,'"'); 17 PREPARE stmt FROM @wtmp ; 18 EXECUTE stmt ; 19 DROP PREPARE stmt; 20 END LOOP; 21 CLOSE wcur; 22 END/w/ 23 DELIMITER; 24 CALL w_unique();
1 DROP PROCEDURE IF EXISTS w_self_update; 2 DELIMITER /w/ 3 CREATE PROCEDURE w_self_update(w_arr VARCHAR(36)) 4 BEGIN 5 DECLARE done INT DEFAULT FALSE; 6 DECLARE w_grab_tab_review_pk INT; 7 DECLARE w_grab_review_url VARCHAR(256); 8 DECLARE wcur CURSOR FOR SELECT grab_tab_review_pk, grab_review_url FROM grab_tab_review WHERE LENGTH(grab_review_url)>0; 9 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 10 11 OPEN wcur; 12 read_loop: LOOP 13 FETCH wcur INTO w_grab_tab_review_pk, w_grab_review_url,w_countrycode; 14 IF done THEN LEAVE read_loop; 15 END IF; 16 SET @w=LOCATE(w_arr,w_grab_review_url); 17 SET @wb=LOCATE('/ref=',w_grab_review_url); 18 SET @wstr=SUBSTR(w_grab_review_url,@w + LENGTH(w_arr),@wb - @w - LENGTH(w_arr)); 19 SET @wtmp = CONCAT('UPDATE grab_tab_review ',' SET amz_review_id="',@wstr,'" WHERE grab_tab_review_pk=',w_grab_tab_review_pk); 20 PREPARE stmt FROM @wtmp ; 21 IF @w>0 THEN EXECUTE stmt ; 22 END IF; 23 DROP PREPARE stmt; 24 END LOOP; 25 CLOSE wcur; 26 END/w/ 27 DELIMITER; 28 CALL w_self_update('-reviews/'); 29 CALL w_self_update('-review/');
1 https://www.amazon.com.mx/review/R1OR2BGFHA44LE/ref=cm_cr_dp_title?ie=UTF8&ASIN=B0196IK0OM&channel=detail-glance&nodeID=9482690011&store=software 2 https://www.amazon.com.mx/gp/customer-reviews/R1OR2BGFHA44LE/ref=cm_cr_dp_title?ie=UTF8&ASIN=B0196IK0OM&channel=detail-glance&nodeID=9482690011&store=software 3 ===> 4 R1OR2BGFHA44LE
1 2 SELECT grab_review_url FROM grab_tab_review WHERE grab_tab_review_pk=123456; 3 4 SELECT 5 SUBSTR( 6 ( 7 SELECT 8 grab_review_url 9 FROM 10 grab_tab_review 11 WHERE 12 grab_tab_review_pk = 123456 13 ), 14 LOCATE( 15 '-reviews/', 16 ( 17 SELECT 18 grab_review_url 19 FROM 20 grab_tab_review 21 WHERE 22 grab_tab_review_pk = 123456 23 ) 24 ) + LENGTH('-reviews/'), 25 LOCATE( 26 '/ref=', 27 ( 28 SELECT 29 grab_review_url 30 FROM 31 grab_tab_review 32 WHERE 33 grab_tab_review_pk = 123456 34 ) 35 ) - LOCATE( 36 '-reviews/', 37 ( 38 SELECT 39 grab_review_url 40 FROM 41 grab_tab_review 42 WHERE 43 grab_tab_review_pk = 123456 44 ) 45 ) - LENGTH('-reviews/') 46 );
