mysql5.7监听ipv4,MySQL IPv4验证实现

Hello guys it is my first post

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

So I have to make :

Validation of ipv4 address

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)

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;

解决方案

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.

Alternatively, You can use REGEXP support in MySQL.

This is a solution for Part 1 of your question:

This example is for validating an IPV4 address using 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])$'

;

(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)

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)

Update 2:

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;

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

-------------

The SQL will Output:

-----------------------------

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

-----------------------------

Update 3:

I think this solution by Michael Berkowski is awesome