mysql 正则 去0,MySQL正则表达式从IPv4替换0

I cannot make a mysql regexp which would remove zeros in the beggining of each 3 number sequences of IPv4.

The problem is that the validation should be provided on database level and I don't know how to formulate the regex which would replace all 0., 00., .0, .00 with emtpy string from my IPv4.

I want my regex to do something like that :

If I have 012.123.123.023 entering the database it should be saved as 12.123.123.23

Thank you !

解决方案

By utilizing the MySQL functions INET_ATON() and INET_NTOA() you can reliably convert an incoming IPv4 address which has leading zeros into the same string without leading zeros. Wrap INET_ATON() with INET_NTOA() to convert the IP address first to its integer value, and then back to a dotted quad.

IP with leading zeros in various places:

mysql> SELECT INET_NTOA(INET_ATON('001.110.011.111'));

+-----------------------------------------+

| INET_NTOA(INET_ATON('001.110.011.111')) |

+-----------------------------------------+

| 1.110.11.111 |

+-----------------------------------------+

And without leading zeros for comparison:

mysql> SELECT INET_NTOA(INET_ATON('1.110.11.111'));

+--------------------------------------+

| INET_NTOA(INET_ATON('1.110.11.111')) |

+--------------------------------------+

| 1.110.11.111 |

+--------------------------------------+

Note: This will return NULL if the input IP address was not a valid address. It won't return the original string or strip leading zeros from a bad IP address:

Faulty IP address with leading zeros:

mysql> SELECT INET_NTOA(INET_ATON('888.777.123.123'));

+-----------------------------------------+

| INET_NTOA(INET_ATON('888.007.123.123')) |

+-----------------------------------------+

| NULL |

+-----------------------------------------+