update table อัตโนมัติ

สามารถให้ mysql หรือ MariaDB update หรือแก้ไขข้อมูลได้โดยอัตโนมัติทุกครั้งที่ insert, update และ delete ได้โดยใช้ trigger ช่วย จะยกตัวอย่างโดยสมมุติตาราง users โดยถ้า มีการเพิ่มข้อมูลมาใหม่ให้สุ่ม salt และ hash รหัสผ่านให้โดยอัตโนมัติ และเพื่อความปลอดภัยให้เปลี่ยน salt และ hash ใหม่ทุกครั้งที่ password เปลี่ยนไป ถ้าไม่เข้าใจว่า salt คืออะไร ขอเชิญอ่านจากเรื่อง login แบบปลอดภัย

สร้างตาราง users ก่อนโดยใช้

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` char(32) NOT NULL,
  `salt` char(5) NOT NULL,
  `password_hash` char(32) NOT NULL,
  `email` varchar(254) NOT NULL,
  `date_create` datetime DEFAULT NULL,
  `date_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

สร้าง trigger โดยใช้โครงสร้าง

DELIMITER $$
CREATE TRIGGER `ชื่อ trigger ` AFERT หรือ BEFORE INSERT หรือ UPDATE หรือ DELETE ON `ตาราง ` FOR EACH ROW BEGIN
...
SET NEW.`ชื่อฟิลย์ ` = ค่าใหม่;
...
END
$$
DELIMITER ;
...

.OLD

ค่าใน record เดิมก่อน update

.NEW

ค่าใหม่ที่จะเข้ามา update เปลี่ยนค่าเดิม

trigger ของตาราง users ในแบบที่เราต้องการจะเขียนเป็น

--
-- Triggers `users`
--
DELIMITER $$
CREATE TRIGGER `users_passwordhash_insert` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
 SET @string := 'abcdefghijklmnopqrstuvwxyz0123456789';
    SET @i := 1;
    SET @random := '';

    WHILE (@i <= 5) DO
        SET @random := CONCAT(@random, SUBSTRING(@string, FLOOR(RAND() * 36 + 1), 1));
        SET @i := @i + 1;
    END WHILE;

SET NEW.`date_create` = NOW();
SET NEW.`salt` = @random;
SET NEW.`password_hash` = MD5(CONCAT(NEW.`password`, @random));
END
$$
DELIMITER ;
--
DELIMITER $$
CREATE TRIGGER `users_passwordhash_update` BEFORE UPDATE ON `users` FOR EACH ROW BEGIN
 SET @string := 'abcdefghijklmnopqrstuvwxyz0123456789';
    SET @i := 1;
    SET @random := '';

    WHILE (@i <= 5) DO
        SET @random := CONCAT(@random, SUBSTRING(@string, FLOOR(RAND() * 36 + 1), 1));
        SET @i := @i + 1;
    END WHILE;

SET NEW.`date_update` = NOW();
SET NEW.`salt` = @random;
SET NEW.`password_hash` = MD5(CONCAT(NEW.`password`, @random));
END
$$
DELIMITER ;

ทดลองเพิ่ม และเปลี่ยนข้อมูลดูครับตัว salt และ password_hash จะต้องเปลี่ยนทุกครั้ง ในการใช้งานจริง ให้ลบฟิลย์ password ออกและแก้ trigger ใหม่ เพราะไม่ควรเก็บ password เป็นข้อความธรรมดา (plain text)