MySQL IPv4验证实现 [英] MySQL IPv4 Validation implementation

问题描述

大家好，这是我的第一篇文章

Hello guys it is my first post

过去两天，我在这里努力为ipv4地址实施验证.... 问题在于验证应该在数据库级别执行，这使得像我这样的菜鸟很难

I am struggling here for the past two days in implementing validation for ipv4 address.... The problem is that the validation should be performed on database level and this makes it difficult for noob like me

所以我必须做:

1. ipv4地址的验证
2. 如果ip中某处的前缀为0，则必须将其删除(例如:123.013.221.011 应该变成123.13.221.11)

1. Validation of ipv4 address
2. If the prefix somewhere in the ip is 0 I have to remove it (example : 123.013.221.011 should become 123.13.221.11)

到目前为止，我的触发条件是:

What I have so far in a trigger:

这无法正常工作，所以我要向你们寻求帮助...谢谢！！！

This is not working as it is supposed to, so I am asking you guys for help...thanks !!!

DELIMITER $$
CREATE TRIGGER validation_trigger BEFORE INSERT ON setting_parameters
FOR EACH ROW 
BEGIN 
-- PROXY RULES --
   IF(NEW.parameter_name LIKE '%proxy%') = true THEN 
     IF(INET_ATON(NEW.parameter_value)<INET_ATON('255.255.255.255'))=true THEN 
        IF(NEW.parameter_name LIKE '0%') = true THEN 
           SET NEW.parameter_value = SUBSTR(NEW.parameter_value,2);
        ELSE 
           SIGNAL SQLSTATE '12345'
           SET MESSAGE_TEXT = 'Wrong PROXY parameter values !';
        END IF;
     END IF;
   END IF;

推荐答案

这是一个建议.考虑将此功能实现为用户定义的函数(或UDF).这样您就可以用c或c++而不是SQL编写代码.

Here is a suggestion. Consider implementing this functionality as a User Defined Functions (or UDF). This will allow you to write code in either c or c++ rather than SQL.

或者，您可以在MySQL中使用 REGEXP支持 .

Alternatively, You can use REGEXP support in MySQL.

这是您的问题的第1部分的解决方案:

This is a solution for Part 1 of your question:

此示例用于使用REGEXP

SELECT '123.1.2.3'
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])$'
;

SQL小提琴链接

(注意:，我自己从未使用过触发器.因此无法为您提供现成的代码.但是您可以使用此示例从中创建触发器)

(Note: I have never used triggers myself. Hence cannot give you ready-to-use code. But you can use this example to make a trigger out of it)

我认为第2部分的解决方案是简单的查找和替换.在这里，REGEXP可以再次用于匹配0，00，.0，.00的所有模式，并替换为.(如果为字符串开头，则为空)

I think solution for part 2 is a simple find-and-replace. Here again a REGEXP can be used to match all the patterns of 0, 00, .0, .00 and replace with a . (or nothing, if beginning of string)

更新2:

这是一个删除前导零的SQL示例.我不必在这里使用REGEXP，因为CAST()发挥了神奇的作用！

Here is an SQL example to remove leading zeros. I did not have to use REGEXP here since CAST() did the magic!

SELECT ip, 
    CONCAT_WS('.',CAST(SUBSTRING_INDEX(ip,'.',1) as UNSIGNED), 
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1) as UNSIGNED), 
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1) as UNSIGNED), 
    CAST(SUBSTRING_INDEX(ip,'.',-1) as UNSIGNED))  as Converted_IP
FROM ip_addr;

如果ip_addr是具有名为ip的列且具有类似值的表

If ip_addr is a table with a column named ip and values like this

-------------    
IP
-------------
127.0.0.1
001.02.0.123
1.23.123.000
-------------

SQL将输出:

-----------------------------
IP              CONVERTED_IP
-----------------------------
127.0.0.1       127.0.0.1
001.02.0.123    1.2.0.123
1.23.123.000    1.23.123.0
-----------------------------

SQL小提琴链接

更新3:

我认为此解决方案-berkowski> Michael Berkowski 很棒

I think this solution by Michael Berkowski is awesome

这篇关于MySQL IPv4验证实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！