1  DROP FUNCTION IF EXISTS `ISBN10to13`;
 2  delimiter //
 3  CREATE FUNCTION `ISBN10to13`(isbn10 VARCHAR(50)) RETURNS varchar(50) CHARSET utf8
 4  BEGIN
 5      DECLARE isbn13 VARCHAR(13);
 6      DECLARE i   INT;
 7      DECLARE chk INT;
 8  
 9      IF (LENGTH(ISBN10) > 10) THEN 
10          RETURN ISBN10;
11      ELSE 
12          SET isbn10=SUBSTRING(ISBN10,1,10);
13      END IF;
14  
15      # set ISBN10    = '0123456479';
16      SET isbn13  = CONCAT('978' , LEFT(isbn10, 9));
17      SET i = 1, chk  = 0;
18  
19      # 9*1+7*3+8*1=38
20      SET chk = (38 + 3*LEFT(isbn10,1) 
21          + RIGHT(LEFT(isbn10,2),1)
22          + 3*RIGHT(LEFT(isbn10,3),1)
23          + RIGHT(LEFT(isbn10,4),1)
24          + 3*RIGHT(LEFT(isbn10,5),1)
25          + RIGHT(LEFT(isbn10,6),1)
26          + 3*RIGHT(LEFT(isbn10,7),1) 
27          + RIGHT(LEFT(isbn10,8),1) 
28          + 3*LEFT(RIGHT(isbn10,2),1));
29  
30      SET chk = 10 - (chk % 10);
31      IF (chk<>10) then
32          SET isbn13 = concat(isbn13 , CONVERT(chk, CHAR(1)));
33      ELSE
34          SET isbn13 = concat(isbn13 , '0');
35      END IF;
36      RETURN isbn13;
37  END //
38  delimiter ;
39  
40  -- ~37 mins
41  ALTER TABLE allthethings.ol_base ADD PRIMARY KEY(ol_key);
42  
43  -- Note that many books have only ISBN10.
44  -- ~20mins
45  DROP TABLE IF EXISTS allthethings.ol_isbn13;
46  CREATE TABLE allthethings.ol_isbn13 (isbn CHAR(13), ol_key CHAR(200), PRIMARY KEY(isbn, ol_key)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin IGNORE SELECT x.isbn AS isbn, ol_key FROM allthethings.ol_base b CROSS JOIN JSON_TABLE(b.json, '$.isbn_13[*]' COLUMNS (isbn CHAR(13) PATH '$')) x WHERE ol_key LIKE '/books/OL%' AND LENGTH(x.isbn) = 13 AND x.isbn REGEXP '[0-9]{12}[0-9X]';
47  -- ~60mins
48  INSERT IGNORE INTO allthethings.ol_isbn13 (isbn, ol_key) SELECT ISBN10to13(x.isbn) AS isbn, ol_key FROM allthethings.ol_base b CROSS JOIN JSON_TABLE(b.json, '$.isbn_10[*]' COLUMNS (isbn CHAR(10) PATH '$')) x WHERE ol_key LIKE '/books/OL%' AND LENGTH(x.isbn) = 10 AND x.isbn REGEXP '[0-9]{9}[0-9X]';
49  
50