Building a public website with user registration and email verification is typical. It will require users to validate their email by clicking a unique generated URL link.
Sometimes, user registration with pending verification will grow exponentially and needs an automated cleanup of records.
In this example, I created a simple MySQL stored procedure to delete users with “pending-email-verification” status for more than five days.
TIMESTAMPDIFF(unit, datetime1, datetime2)
CREATE DEFINER=`dbuser`@`localhost` PROCEDURE `delete_unverified_users`() BEGIN DELETE FROM user WHERE user.status='pending-email-verification' AND TIMESTAMPDIFF(DAY,user.modifiedDateTime,NOW()) > 5; SELECT ROW_COUNT() AS 'count'; END