MYSQL:如果具有OR条件& REGEXP比赛 [英] MYSQL: IF having OR condition & REGEXP match

查看:114
本文介绍了MYSQL:如果具有OR条件& REGEXP比赛的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

目标:使用具有多个条件的触发器来阻止插入数据库表中

Goal: To block inserts into database table using trigger having multiple conditions

说明:尝试阻止联系人表中的许多无关条目.我创建了一个亵渎表,其中包含许多坏/脏话/脏话和一个正则表达式过滤URL.如果数据库中有任何包含这些错误词或URL的条目,则不应将其插入.

Description: Trying to block lots of irrelevant entries in contact table. I have created a profanity table having lot of bad/swear/dirty words and a regular expression filtering URL. If any entry comes to DB having these bad words or URL then it should not be inserted.

分析:在SO上搜索了许多不同的解决方案,它们可以重复,但是在单个触发条件中找不到任何包含多个条件和正则表达式的答案.同样,无法使用这些答案创建成功的触发器.创建时,它会引发错误.请帮忙,谢谢.

Analysis: Searched many different solution over SO and could be duplicate but didn't found any answer having multiple condition and regexp in a single trigger condition. Also, not able to create a successful trigger using those answers. While creation, it is throwing error. Please help, thanks in advance.

表格列 电子邮件,邮件

TRIGGER

DELIMITER $$
CREATE TRIGGER trg_contact_insert_profanity_check
BEFORE INSERT ON contacts.c
FOR EACH ROW
BEGIN
IF (c.email,c.message IN (SELECT * FROM profanity) OR (c.message REGEXP '/(http|https|ftp|ftps)?(\:\/\/)?[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(\/\S*)?/' ))
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Database Error: BAD REQUEST";
ENDIF;
END;$$

错误

    MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as c
FOR EACH ROW
BEGIN
IF (c.email,c.message IN (SELECT * FROM profanity) OR' at line 2

推荐答案

在调试了许多可能性并挖掘了SO答案后,最终获得了最终的正确版本的触发器.期待快速回应,因为不是其他开发人员无法破解的SCI-FI问题.

Finally got the final and correct version of trigger after debugging lot of possibilities and digging SO answers. Was expecting a quick response as it was not that SCI-FI question that other developers were unable to crack.

错误1 :ENDIF之间的空格.它应该是END IF.

Mistake 1: Space between ENDIF. It should be END IF.

错误2 :触发器定义中缺少new.column

Mistake 2: Missing new.column from trigger definition;

正确版本:

CREATE TRIGGER `trg_contact_insert_profanity_check` BEFORE INSERT ON `contacts`
 FOR EACH ROW BEGIN
IF ((new.email IN (SELECT word FROM profanity)) OR (new.message
 IN (SELECT word FROM profanity)) OR (new.message REGEXP '/(http|https|ftp|ftps)?(://)?[a-zA-Z0-9-.]+.[a-zA-Z]{2,3}(/S*)?/' ))
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Database Error: BAD REQUEST";
END IF;
END

这篇关于MYSQL:如果具有OR条件& REGEXP比赛的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆