1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
| DELIMITER //
CREATE PROCEDURE extract_all_image_urls()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE product_id VARCHAR(255);
DECLARE model_num VARCHAR(255);
DECLARE html_fragment TEXT;
DECLARE cur CURSOR FOR
SELECT
t.pro_num_id,
t.model_number,
SUBSTRING(
t.description,
LOCATE('data-module-name="icbu-pc-detailManyImage"', t.description),
LOCATE('</div></div>',
SUBSTRING(
t.description,
LOCATE('data-module-name="icbu-pc-detailManyImage"', t.description)
)
) + 12
) AS html_fragment
FROM biz_product_info t
WHERE t.alibaba_user_id='2214940607886' AND t.display='N'
AND t.description LIKE '%data-module-name="icbu-pc-detailManyImage"%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE TEMPORARY TABLE temp_image_urls (
pro_num_id VARCHAR(255),
model_number VARCHAR(255),
image_url VARCHAR(255)
);
OPEN cur;
read_loop: LOOP
FETCH cur INTO product_id, model_num, html_fragment;
IF done THEN
LEAVE read_loop;
END IF;
SET @pos = 1;
WHILE LOCATE('<img src="', html_fragment, @pos) > 0 DO
SET @img_start = LOCATE('<img src="', html_fragment, @pos);
SET @url_start = @img_start + 10;
SET @url_end = LOCATE('"', html_fragment, @url_start);
IF @img_start > 0 AND @url_end > 0 THEN
INSERT INTO temp_image_urls VALUES (
product_id,
model_num,
CONCAT('https:', SUBSTRING(html_fragment, @url_start, @url_end - @url_start))
);
END IF;
SET @pos = @url_end + 1;
END WHILE;
END LOOP;
CLOSE cur;
SELECT * FROM temp_image_urls;
DROP TEMPORARY TABLE temp_image_urls;
END //
DELIMITER ;
CALL extract_all_image_urls();
|
