เพิ่ม/ลดค่าแถวใน MySQL ด้วย Stored Procedure (Increment/Decrement)

Stored Procedure สำหรับเพิ่ม-ลดค่าในหลายแถวพร้อมกัน

ใช้ตัวแปร (DECLARE) เก็บค่าเดิม แล้ว UPDATE ตามเงื่อนไข

ตัวอย่างสร้างตารางและข้อมูล

mysql> CREATE TABLE IncrementAndDecrementValue (
    -> UserId INT,
    -> UserScores INT
    -> );
Query OK, 0 rows affected (0.60 sec)

mysql> INSERT INTO IncrementAndDecrementValue VALUES(101,20000);
mysql> INSERT INTO IncrementAndDecrementValue VALUES(102,30000);
mysql> INSERT INTO IncrementAndDecrementValue VALUES(103,40000);

ข้อมูลเริ่มต้น

mysql> SELECT * FROM IncrementAndDecrementValue;

+--------+------------+
| UserId | UserScores |
+--------+------------+
| 101    | 20000      |
| 102    | 30000      |
| 103    | 40000      |
+--------+------------+
3 rows in set (0.00 sec)

สร้าง Procedure: ลด UserId=101 เพิ่ม UserId=102

mysql> DELIMITER //
mysql> CREATE PROCEDURE IncrementAndDecrementRowValueByOne()
    -> BEGIN
    -> DECLARE first INT;
    -> DECLARE second INT;
    -> SET first = (SELECT UserScores FROM IncrementAndDecrementValue WHERE UserId = 101);
    -> SET second = (SELECT UserScores FROM IncrementAndDecrementValue WHERE UserId = 102);
    -> UPDATE IncrementAndDecrementValue SET UserScores = first - 1 WHERE UserId = 101;
    -> UPDATE IncrementAndDecrementValue SET UserScores = second + 1 WHERE UserId = 102;
    -> END //
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;

เรียกใช้ Procedure

mysql> CALL IncrementAndDecrementRowValueByOne();
Query OK, 1 row affected (0.24 sec)

ผลลัพธ์หลังรัน

mysql> SELECT * FROM IncrementAndDecrementValue;

+--------+------------+
| UserId | UserScores |
+--------+------------+
| 101    | 19999      |
| 102    | 30001      |
| 103    | 40000      |
+--------+------------+
3 rows in set (0.00 sec)

การปรับปรุง: ใช้ Transaction เพื่อความปลอดภัย

CREATE PROCEDURE SafeTransfer()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
END;

ข้อควรระวัง: ตัวอย่างเดิมไม่ได้ใช้ Transaction หาก UPDATE ตัวที่ 2 fail ตัวที่ 1 จะยัง commit แล้ว (data inconsistency)