สร้าง Function รวมค่า Comma-separated String ใน MySQL

สถานการณ์

คอลัมน์ VARCHAR เก็บตัวเลขคั่นด้วย Comma เช่น '20,10,40,50,60' ต้องการคำนวณผลรวม (Sum) โดยไม่ต้อง Normalize ตาราง

วิธีแก้: สร้าง Stored Function

ใช้ Loop วนลูปตัดค่าทีละตัวด้วย SUBSTRING_INDEX และ SUBSTRING แล้วสะสมผลรวม

ตัวอย่างการสร้าง Function

1. สร้างตาราง

mysql> CREATE TABLE DemoTable (
    -> ListOfValues VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.56 sec)

2. เพิ่มข้อมูล

mysql> INSERT INTO DemoTable VALUES('20,10,40,50,60');
Query OK, 1 row affected (0.14 sec)

3. สร้าง Function

mysql> DELIMITER ??
mysql> CREATE FUNCTION totalSumInCommaSeparatedString(input VARCHAR(50))
    -> RETURNS INT
    -> DETERMINISTIC
    -> NO SQL
    -> BEGIN
    ->     DECLARE totalSum INT DEFAULT 0;
    ->     WHILE INSTR(input, ',') > 0 DO
    ->         SET totalSum = totalSum + SUBSTRING_INDEX(input, ',', 1);
    ->         SET input = SUBSTRING(input, INSTR(input, ',') + 1);
    ->     END WHILE;
    ->     RETURN totalSum + input;
    -> END ??
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;

4. ใช้งาน Function

mysql> SELECT totalSumInCommaSeparatedString(ListOfValues) AS TotalSum FROM DemoTable;

ผลลัพธ์:

+----------+
| TotalSum |
+----------+
| 180      |
+----------+
1 row in set (0.00 sec)

อธิบาย Logic ของ Function

1. INSTR(input, ',') > 0 - ตรวจสอบว่ายังมี Comma อยู่ไหม
2. SUBSTRING_INDEX(input, ',', 1) - ดึงค่าตัวแรก (ก่อน Comma แรก)
3. SUBSTRING(input, INSTR(input, ',') + 1) - ตัดทิ้งค่าตัวแรกและ Comma แรก
4. วนลูปจนไม่มี Comma เหลือ → บวกค่าสุดท้ายเข้าไป

เวอร์ชันที่รองรับทศนิยม (DECIMAL)

CREATE FUNCTION sum_csv_decimal(input VARCHAR(1000))
RETURNS DECIMAL(15,2)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE total DECIMAL(15,2) DEFAULT 0;
    DECLARE val VARCHAR(50);
    WHILE INSTR(input, ',') > 0 DO
        SET val = SUBSTRING_INDEX(input, ',', 1);
        SET total = total + CAST(val AS DECIMAL(15,2));
        SET input = SUBSTRING(input, INSTR(input, ',') + 1);
    END WHILE;
    RETURN total + CAST(input AS DECIMAL(15,2));
END

ข้อจำกัดและข้อควรระวัง

• Function เรียกทุกแถว → ช้าบนข้อมูลเยอะ (Row-by-row processing)
• ไม่รองรับ Negative numbers ที่มีเครื่องหมายลบ (ต้องปรับ Logic)
• ไม่รองรับ Space รอบ Comma (ต้อง TRIM หรือ REPLACE ก่อน)
• Max recursion depth / Loop iterations อาจจำกัดได้

แนวทางที่ดีกว่า (MySQL 8.0+)

ใช้ Recursive CTE

WITH RECURSIVE cte AS (
    SELECT 
        ListOfValues,
        CAST(SUBSTRING_INDEX(ListOfValues, ',', 1) AS DECIMAL(10,2)) AS val,
        SUBSTRING(ListOfValues, INSTR(ListOfValues, ',') + 1) AS rest
    FROM DemoTable
    UNION ALL
    SELECT 
        rest,
        CAST(SUBSTRING_INDEX(rest, ',', 1) AS DECIMAL(10,2)),
        SUBSTRING(rest, INSTR(rest, ',') + 1)
    FROM cte
    WHERE INSTR(rest, ',') > 0
)
SELECT SUM(val) FROM cte;

Best Practice

• Normalize ข้อมูล: แยกเป็นตารางความสัมพันธ์ (One-to-Many)
• ใช้ JSON Array (MySQL 5.7+) แทน CSV String
• ใช้ Generated Column คำนวณ Sum ไว้ล่วงหน้า