我有一个触发器和存储过程。在存储过程中,我验证 IPv4 地址。如果地址有效,则会将其保存到数据库。如果不是,我的程序将空记录保存在数据库中。每当 IP 无效时,我都想这样做 - 没有什么可保存的。我不知道当ip无效时如何停止该过程。我知道不应该在数据库级别提供验证,但这是要求。你能帮我修复我的代码吗?谢谢:)
DELIMITER $$
CREATE TRIGGER `validation_test_trigger` BEFORE INSERT ON setting_parameters
FOR EACH ROW
BEGIN
IF(NEW.parameter_name LIKE 'proxy_test') = true THEN
CALL validate_ip(NEW.parameter_value);
end IF;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS validate_ip;
DELIMITER //
CREATE PROCEDURE validate_ip(INOUT ip VARCHAR(45))
BEGIN
select INET_NTOA(INET_ATON(ip)) into ip;
IF(ip REGEXP '^([1-9]|[1-9][0-9]|1[013-9][0-9]|12[0-689]|2[0-4][1-9]|25[0-4]).(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5]).){2}([1-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][1-9]|25[0-4])$')=0
THEN
SIGNAL SQLSTATE '12345'
SET MESSAGE_TEXT = 'Invalid IP!';
END IF;
END //
DELIMITER ;
- 创建一个函数,如果 IP 正确,则返回
- 0,如果 IP 不正确,则返回 1,而不是过程。
- 创建一个表
b_setting_parameters
类似于setting_parameters
但带有ENGINE=BLACKHOLE
。这将创建一个表,该表丢弃插入其中的所有内容(如/dev/null) 在该黑洞表上创建插入触发器:
CREATE TRIGGER validation_test_trigger BEFORE INSERT ON b_setting_parameters FOR EACH ROW BEGIN IF validate_ip(NEW.parameter_value) = 0 THEN INSERT INTO setting_parameters(col1, col2,...) VALUES (NEW.col1, NEW.col2,...) END IF; END$$
现在您必须
b_setting_parameters
而不是setting_parameters
插入到表中,它将解决问题。
正如您所说,这不应该在数据库级别完成 - 我不相信 MySQL 触发器允许防止插入或更新。
我可以看到成功的唯一方法是让您的触发器更改数据,以便 MySQL 无法插入它......但鉴于 MySQL 经常操作数据以适应表定义,我不确定这是否有效。