How can I trigger an event in the database when the column has a specific value?

Question

I'm working on creating a Twitter-like social network application where users have news feeds, followers, ect messages ...

I'm trying to implement a function that will make messages (a message in my application is equivalent to a Facebook message) EXPIRE after a certain amount of time .

What can I say upon expiration?
 1. The message disappears from the news

 2. The user whose expiration date releases a notification warning that the message has expired. At the program level, this is just an insert statement that executes when the expiration date.

What have i done so far? Removing messages from the news feed was simple, I just adjusted the query that returns the news by checking the date_of_expiration column and compare it to NOW ().

Creating notifications when expiration was more difficult.
My initial approach was to execute a mysql CRON job that ran every 2 minutes, raising an event that would select all the entries where NOW ()> date_of_expiration, and use the selected data to insert the notification entry into my notification table.

This works, however, I do not want to use the CRON work. A 2-minute delay means that the user will have to wait a full 2 ​​minutes after the message has actually expired before receiving a notification informing the user that their message has expired. I assume that if there were many records in the table, this timeout may be even longer depending on how long it takes to execute the select and insert statements.

What am I looking for?
Another solution is to insert a notification into the notification table when the mail expires.

, - , , ( ) , NOW(), . - ? ?

FYI : MYSQL, JAVA Android + IOS, ,

Answer 1

, . , , .

, script . script / .

, , , .

EDITED

, , ?

;

INSERT INTO posts (comment, createdBy....) (' ', 123);

SELECT @lastID: = LAST_INSERT_ID();

-- Create temporary table with all the friends to notify
-- "this will help you with performance" Hint then engine type
-- Make sure the final userId list is unique otherwise you will be
-- inserting duplicate notifications which I am sure you want to avoid
CREATE TEMPORARY TABLE myFriends (KEY(user_id)) ENGINE=MEMORY
SELECT 1 FROM users AS s
INNER JOIN friends AS f ON f.friedId = s.userId
WHERE s.userID = su.userID

-- insert the notifications all at once
-- This will also help you with the performance a little
INSERT INTO notifications(userID, postId, isRead)
SELECT userID, @lastID AS postId,'0' AS isRead
FROM users AS su
INNER JOIN myFriends AS f ON f.userId = su.userId;


-- commit the transaction if everything passed
commit; 
-- if something fails
rollback;

, ,

• , . 64 + , SSD , ,
• , GTID, .

Answer 2

, . , :

, "expired"? , , , , . , , , (NOW() > date_of_expiration), . java . , , , . Java.

Cron . , . JOIN .

, .

Answer 3

, x ?

MySQL. , , .