แยกสตริงคั่นจุลภาคและแทรกเป็นแถวแยกกันใน MySQL

ใช้ Prepared Statement ร่วมกับการจัดรูปแบบสตริง เพื่อแยกค่าที่คั่นด้วยจุลภาคและ INSERT เป็นหลายแถว

ขั้นตอนการทำงาน

1. กำหนดค่าสตริงต้นทางในตัวแปร
2. แทนที่จุลภาคด้วยรูปแบบ VALUES ของ INSERT
3. สร้างคำสั่ง INSERT สมบูรณ์
4. Prepare และ Execute

ตัวอย่างเต็ม

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

mysql> create table University (
    -> UserId int,
    -> UniversityId int
    -> );

2. กำหนดค่าและสร้าง Query

mysql> SET @userId = '8,9,10';
mysql> SET @UniversityId = 100;
mysql> SET @myValues = REPLACE(@userId, ',', CONCAT(', ', @UniversityId, '),('));
mysql> SET @myValues = CONCAT('(', @myValues, ', ', @UniversityId, ')');
mysql> SET @insertQuery1 = CONCAT('INSERT INTO University VALUES', @myValues);

3. Execute Prepared Statement

mysql> PREPARE st FROM @insertQuery1;
mysql> EXECUTE st;
Query OK, 3 rows affected (0.18 sec)

4. ตรวจสอบผลลัพธ์

mysql> select * from University;

+--------+--------------+
| UserId | UniversityId |
+--------+--------------+
| 8      | 100          |
| 9      | 100          |
| 10     | 100          |
+--------+--------------+

5. ปล่อย Prepared Statement

mysql> DEALLOCATE PREPARE st;

วิธีสมัยใหม่ (MySQL 8.0+): ใช้ JSON_TABLE

INSERT INTO University (UserId, UniversityId)
SELECT CAST(val AS UNSIGNED), 100
FROM JSON_TABLE(CONCAT('[\"', REPLACE('8,9,10', ',', '\",\"'), '\"]'), '$[*]' COLUMNS (val VARCHAR(20) PATH '$')) AS jt;

วิธีอื่น: Stored Procedure กับ Loop

DELIMITER //
CREATE PROCEDURE SplitAndInsert(IN csv_str VARCHAR(1000), IN univ_id INT)
BEGIN
    DECLARE pos INT DEFAULT 1;
    DECLARE len INT;
    DECLARE val VARCHAR(20);
    
    SET csv_str = TRIM(csv_str);
    SET len = LENGTH(csv_str) - LENGTH(REPLACE(csv_str, ',', '')) + 1;
    
    WHILE pos <= len DO
        SET val = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(csv_str, ',', pos), ',', -1));
        INSERT INTO University (UserId, UniversityId) VALUES (val, univ_id);
        SET pos = pos + 1;
    END WHILE;
END //
DELIMITER ;

CALL SplitAndInsert('8,9,10', 100);