เขียน Store Procedure เพื่อ Insert Random Coupon ลง Mysql

เขียน Store Procedure เพื่อ Insert Random Coupon ลง Mysql

เริ่มจากข้าพเจ้าไป pull mysql จาก docker ลงมาก่อน

$docker pull mysql
$docker run --name mysql -e MYSQL_ROOT_PASSWORD=password -d -p 3306:3306 mysql:latest

จากนั้นเราจะสร้าง Database ขึ้นมาตัวนึงเพื่อทดสอบ นั่นก็คือ Coupon และทำการสร้าง Table ขึ้นมา 1 ตัว เพื่อใช้ทดสอบการ Gen Coupon ให้ชื่อว่า coupon_code ละกัน

CREATE TABLE `coupon_code` (
  `code_id` int NOT NULL AUTO_INCREMENT,
  `coupon` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`code_id`),
  UNIQUE KEY `coupon` (`coupon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ต่อมาเราจะใช้คำสั่ง RPAD function ใน mysql ในการสร้าง string coupon code ให้ครบ 10 ตัว

https://www.w3schools.com/sql/func_mysql_rpad.asp

SELECT RPAD("PREFIX", 10, "ABC");

Parameter Values

Syntax RPAD(string, length, lpad_string)

เมื่อเราลอง run sql จะได้ผลลัพทธ์เป็น

PREFIXABCA

ทีนี้เราได้ function การทำ PREFIX code บน sql มาแล้ว ต่อไปเราจะทำการ Random string มาต่อท้าย PREFIX กัน ซึ่งหากเราไม่ต้องการ PREFIX เราก็แค่ทำการลบ Parameter ตัวแรกออกให้เป็น Empty string แทน

จากนั้นเราจะใช้ UUID function ในการ Generate Random String ขึ้นมาในการทดสอบ ซึ่งจะได้ผลลัพท์เป็น string ยาว 36 ตัวอักษร มี - คั่น 41c4d2dd-2ebf-11eb-8297-0242ac110002

แต่เราต้องการแค่ 10 ตัวอักษร และไม่เอาขีดคั่นกลาง ดังนั้นเราจะใช้คำสั่ง REPLACE เข้าช่วย และใช้คำสั่ง LEFT เพื่อดึง 10 ตัวจากซ้ายมือมาใช้งานแทน

SELECT RPAD("", 10, LEFT(REPLACE(UUID(),"-",""), 10));

หลังจาก run เราจะได้ random string มาแล้วแบบง่ายๆ

ทีนี้เราจะมา Generate มันทีเดียว 5000 ตัว โดยที่ต้องไม่ซ้ำกับใน Database ที่มีอยู่แล้วด้วย

จากรอบที่แล้วน้องในทีมได้ทำการ สร้างมา 1 ตัว แล้ว วิ่งเข้าไปเช็คใน Database 1 ครั้งว่ามีหรือยัง ถ้ายังให้ทำการ Insert ทำแบบนี้ไป N ครั้ง ซึ่งทำให้ระบบเกิด Timeout อันนี้ เราไม่ได้ไปไล่ถึง Big O Notation ตั้งแต่ใน API ก่อนเข้ามาที่ Database นะ เราจะแค่มาทดสอบ ทำ Insert ตรงๆผ่าน SQL ดูว่าเป็นยังไงบ้าง

ต่อมาเราจะมาเขียน Store Procedure ใน mysql กัน

DELIMITER//
CREATE PROCEDURE createCoupons(IN param INT)
BEGIN
SELECT RPAD("", param, LEFT(REPLACE(UUID(),"-",""), 10));
END//
DELIMITER ;

จาก Store Procedure ข้างบน คือเราจะสร้าง function ชื่อ createCoupons และรับ Parameter เป็น int เข้ามา นั่นก็คือ จำนวนคูปองที่เราต้องการ Generate Random นั่นเอง

เมื่อเรา Run SQL ข้างต้นแล้ว Mysql จะทำการ Save Store Procedure เราลงไว้ที่ Database ของเรา ในขั้นตอนนี้เราต้องระวังการแก้ไข Store Procedure ของเรา เพราะถ้ามีการ Run ชุดคำสั่ง CREATE PROCEDURE ชื่อ function ของเราซ้ำ ระบบจะฟ้องว่า 1304 - PROCEDURE createCoupons already exists, Time: 0.002000s

วิธีการเรียกใช้งาน เราก็แค่ ใช้คำสั่ง

CALL createCoupons(100);

ระบบก็จะแสดง String ออกมา 100 ตัวอักษรให้เรา

a574ec472ea574ec472ea574ec472ea574ec472ea574ec472ea574ec472ea574ec472ea574ec472ea574ec472ea574ec472e

ต่อมา เราจะมาสร้างตัวแปร i เพื่อวน loop random string ออกมา ตามจำนวน Parameter ที่เรา input ลงไปผ่าน Store Procedure ที่เราสร้างขึ้นมา

DELIMITER//
CREATE PROCEDURE createCoupons(IN param INT)
BEGIN
    DECLARE i int DEFAULT 0;
    WHILE i < param DO
    SELECT RPAD("", param, LEFT(REPLACE(UUID(),"-",""), 10));
END WHILE;  
END//
DELIMITER ;

จากนั้นเราจะมาแก้ไข ในส่วนของการ Random String

SELECT RPAD("", param, LEFT(REPLACE(UUID(),"-",""), 10));

ให้เป็นการ Insert Random string ลงไปใหม่ โดยพระเอกของงานนี้ก็คือ INSERT IGNORE

มันคือไรอ่ะ!

INSERT IGNORE มันก็คือ เราจะ Insert ข้อมูลลงไปใน Table ใน Field ที่เรามีการ SET UNIQUE KEY ไว้ นั่นก็คือ Field ที่ชื่อว่า coupon ของเรานั่นเอง ซึ่งปรกติแล้ว เมื่อมันเจอ Duplicate Key มันก็จะดีด ตัวเองออกจากการทำงาน แล้วฟ้องว่า เฮ้ยยย เจอข้อมูลซ้ำนะ

ทีนี้ เราต้องการ Generate Coupon ทีละ 5000 - 10000 อันเนี่ย มันก็คงไม่งามใช่ป่ะ ถ้าต้อง ดีดออกเมื่อเจอซ้ำ งั้นก็ IGNORE มันซะเลยเป็นไง ก็จะได้ Store Procedure ที่เสร็จสมบูรณ์ ขึ้นมาตามด้านล่าง แต่สุดท้ายต้องหาความน่าจะเป็นที่ code ทั้ง 10 หลักจะไม่ซ้ำกันเลย ว่ามีกี่รูปแบบ เพราะสุดท้าย code มันก็จะวนมาถึงที่ซ้ำกันอยู่ดี

ไม่รู้กูตกเลข คำนวณเอง

DELIMITER//
CREATE PROCEDURE createCoupons(IN param INT)
BEGIN
    DECLARE i int DEFAULT 0;
    WHILE i < param DO
    INSERT IGNORE INTO coupon_code (`coupon`) VALUES (RPAD("", 10, LEFT(REPLACE(UUID(),"-",""), 10)));
    SET i = i+1;
END WHILE;  
END//
DELIMITER ;

ให้ระวังตรง SET i = i+1; เพราะ mysql ไม่รู้จัก i = i++; เน้อ หลังจากนั้นให้เราลอง call Store Procedure ที่เราสร้างขึ้นมา ดู จะพบว่า

CALL createCoupons(100);

การสร้าง Random String 100 Record ใช้เวลาประมาณ OK, Time: 0.388000s เดี๋ยวเราจะมาลองบนขนาด 5000 Record ดูบ้างบน Localhost ของเรา ใช้เวลาประมาณ OK, Time: 19.097000s หลังจากทดสอบบนเครื่องแล้ว จะต้องนำไปทดสอบบน Develop Environment จริงดู ไม่ขิง ไปยิงบน Production นะ แสรดดดด

แต่ละฐานข้อมูล Syntax ไม่เหมือนกันนะ ไปหาอ่านเอาเอง

เบื่อโว้ยยยยย เบื่อโค้ดๆ

Edit มีคนสงสัยว่า ถ้า code ซ้ำ มันจะครบ 5000 ไหม?

บอกเลยว่าไม่ครบครับ

แม้เราจะใส่ ON DUPLICATE KEY UPDATE ลงไป ถ้าความน่าจะเป็นของชุด string ที่เป็นไปได้มันครบหมดแล้ว ก็ขึ้นอยู่ว่าเราจะ design ชุด random code logic เพิ่มเติมยังไงครับ

ทดสอบได้จาก

INSERT IGNORE INTO coupon_code (`coupon`) VALUES (RPAD("", 10, LEFT(REPLACE(UUID(),"-",""), 10))) ON DUPLICATE KEY UPDATE coupon = RPAD("", 10, LEFT(REPLACE(UUID(),"-",""), 10));