ใช้ MYSQL สร้าง running number

เอกสารหลายอย่างในระบบต้องการให้มีเลขนับต่อไปเรื่อยๆ เช่น INV0001-INV9999 ถ้าเราใช้ MYSQL อยู่แล้วมาดูกันว่าจะเอามันมาช่วยสร้าง running number แบบนี้ได้ยังไงบ้าง

สมมติเราทำระบบบัญชี ซึ่งลูกค้าแต่ละคนก็จะมี Invoice Number ที่มี running number เป็นของตัวเองเช่น User A ล่าสุดออก INV0100 อันต่อไปก็ต้องเป็น INV0101 ส่วน User B ล่าสุดออก INV0222 ต่อไปก็ต้องเป็น INV0223

เรามีข้อมูลตาราง User คร่าวๆแบบนี้

CREATE TABLE users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(256)
);

เราให้ MYSQL ช่วยเก็บ running number ของแต่ละ user ได้โดยสร้างอีกตารางแบบนี้

CREATE TABLE inv_numbers (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE,
  number INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

เรากำหนดให้ user_id เป็น foreign key และ unique key เพื่อลิ้งกลับไปว่า record เป็น running number ของ user คนไหน แบบนี้คือแต่ละ user มี running number ได้แค่ record เดียวแน่นอน

ด้วยตารางแบบนี้ เราสามารถใช้ INSERT ... ON DUPLICATE KEY UPDATE Statement ช่วยเพิ่มค่าของ number ไปทีละ 1 และถ้ายังไม่เคยมี running number record มาก่อนก็ให้เริ่มที่ 1 ได้แบบนี้ โดยสมมติเป็น running number ของ user id เท่ากับ 1

INSERT INTO users (name) VALUES ("Weerasak");

mysql> SELECT * FROM users;
+----+----------+
| id | name     |
+----+----------+
|  1 | Weerasak |
+----+----------+

INSERT INTO inv_numbers (user_id, number) VALUES (1, 1)
  ON DUPLICATE KEY UPDATE number=number+1;

mysql> SELECT * FROM inv_numbers;
+----+---------+--------+
| id | user_id | number |
+----+---------+--------+
|  1 |       1 |      1 |
+----+---------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO inv_numbers (user_id, number) VALUES (1, 1)   ON DUPLICATE KEY UPDATE number=number+1;
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM inv_numbers;
+----+---------+--------+
| id | user_id | number |
+----+---------+--------+
|  1 |       1 |      2 |
+----+---------+--------+
1 row in set (0.00 sec)

จะเห็นว่าถ้าเรา insert แบบเดิมอีกครับ MYSQL จะเช็ค unique key ว่ามีแล้วหรือยัง ถ้ามีแล้วจะเลือกทำส่วน update คือ number=number+1 ให้เราเอง

วิธีนี้ทำให้เราไม่ต้องจัดการเรื่อง race condition ในการเพิ่มค่าของ running number เองถ้า user คนเดียวกันสั่งสร้าง invoice number พร้อมๆกัน เพราะ MYSQL จัดการเรื่อง consistency ของข้อมูลให้เราเองอยู่แล้ว