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     );