MySql – filter bad words in multiple tables using triggers

MySQLtrigger

I have for example two tables blog_posts and image and each record in these tables have a text field, that may contain a bad word in it.

so the idea is, I want to check these records and change these bad words with a couple of stars (like ****) the number of stars is fixed and not dependant on words length.

and I have a bad words table called slang.

so what I have so far is like this:

I have two trigger for each table (blog_post and image), one for update and one for insert. so the bad words will filtered out on insert or update. the content of each trigger is the same as below:

BEGIN
  DECLARE i INT;
  DECLARE s VARCHAR(1000);
  DECLARE r VARCHAR(1000);
  SET i = 1;
  SET s = '';
  REPEAT
    SET s = (
      SELECT REPLACE(split, COALESCE(slang, ''), '***') FROM
        (SELECT strSplit(new.text, ' ', i) AS split) s
        LEFT JOIN slang w ON s.split = w.slang
      LIMIT 1
      );
    SET r = CONCAT_WS(' ', r, s);
    SET i = i + 1;
    UNTIL s IS NULL
  END REPEAT;
  SET new.text = r;
END

the strSplit procedure is defined like so (based on a StackOverflow or maybe a StackExchange question I don't remember it):

BEGIN
  DECLARE output VARCHAR(1000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END

so I want to make a trigger to update the blog posts and images text field each time we add or edit a record from slang table. so if I add someSlang into slang table, all images and blog posts that contain someSlang in their text field should get updated and place **** for each instance of someSlang.

I'll appreciate any help and hints.

Best Answer

The slangs table might have a few hundred entries. Whereas the blog_posts and image table might have a lot more data(in the range of millions depending on the traffic). So if an insert or update triggers an update in the other two tables, it might create an unwanted spike in database activity. A better approach would be to plan a Scheduler to do this task.

Related Question