Geek asks and answers

How to save (and find) a string with a template in MySQL?

I need to save an IP address with a wildcard in it, for example 10.0.%. Then I want someone with an IP address in the allowed range (10.0.0-255.1) to be found from my query.

Could you guys help me? I tried to store 10.0%. 1, but I can't find it with a LIKE request (I don’t even know if there is a way to do this - maybe not).

Thanks in advance.

+5
source share
3 answers

How about saving ip addresses as 4 integers in a database:

IPpart1 IPpart2 IPpart3 IPpart4
-------------------------------
127     0       0       1

And make them nullified. (NULL will be our wildcard)

Then you can execute the db request as follows:

SELECT * FROM iptable WHERE (IPpart1 IS NULL OR IPpart1 = '$firstpart') AND (IPpart2 IS NULL OR IPpart2 = '$secondpart') AND (IPpart3 IS NULL OR IPpart3 = '$thirdpart') AND (IPpart4 IS NULL OR IPpart4 = '$fourthpart')

A bit verbose bit, it does the job.

IP-, .

+2

IP- 32- . INET_NTOA() INET_ATON(). 10.0.%. 1 10.0.0.1 255.255.0.255. , IP- , IP- . , . , 10.0.4.1, AND:

10.0.4.1 & 255.255.0.255 = 10.0.0.1

, IP- .

, (ip mask ):

CREATE TABLE networks (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, ip INT, mask INT);

INSERT INTO networks (ip, mask)
VALUES (INET_ATON('10.0.0.1'), INET_ATON('255.255.0.255'));

, IP (10.0.23.1) :

SELECT * FROM networks
WHERE ip & mask = INET_ATON('10.0.23.1') & mask

IP- , , :

SELECT id, INET_NTOA(ip) AS ipstring, INET_NTOA(mask) AS maskstring
FROM networks
WHERE ip & mask = INET_ATON('10.0.23.1') & mask

, IP- . IP-, . , IP-, .

+3

docs MySQL, , :

SELECT '10.0.0.1' AS IP0,
( SUBSTRING_INDEX( '10.0.0.1', '.', 1 ) * 16777216 +
SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.0.1', '.', 2 ),'.',-1) * 65536 +
SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.0.1', '.', -2 ),'.',1) * 256 +
SUBSTRING_INDEX( '10.0.0.1', '.', -1 )
),
'10.0.255.1' AS IP1,
    ( SUBSTRING_INDEX( '10.0.255.1', '.', 1 ) * 16777216 +
    SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.255.1', '.', 2 ),'.',-1) * 65536 +
    SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.255.1', '.', -2 ),'.',1) * 256 +
    SUBSTRING_INDEX( '10.0.255.1', '.', -1 )
) AS IP2Num1

IP:

Ip:  |  10.0.0.1  | 10.0.255.1 |
--------------------------------
Num: | 167772161  | 167837441  |

:

SELECT foo 
FROM bar.foobar
WHERE ( SUBSTRING_INDEX( ipField, '.', 1 ) * 16777216 +
    SUBSTRING_INDEX(SUBSTRING_INDEX( ipField, '.', 2 ),'.',-1) * 65536 +
    SUBSTRING_INDEX(SUBSTRING_INDEX( ipField, '.', -2 ),'.',1) * 256 +
    SUBSTRING_INDEX( ipField, '.', -1 )
) BETWEEN 167772161 AND 167837441;

, :

WHERE ipField LIKE '10.0.%.1'
AND (CAST
       (REPLACE(SUBSTRING_INDEX(ipField,'.',3),
                CONCAT(SUBSTRING_INDEX(ipField ,'.',2),'.')
                ,'')
     AS UNSIGNED) BETWEEN 0 AND 255)

:

SELECT IF ('10.0.12.1' LIKE '10.0.%.1'
       AND (CAST
             (REPLACE( SUBSTRING_INDEX('10.0.12.1','.',3),
                 CONCAT(SUBSTRING_INDEX('10.0.12.1','.',2),'.')
              ,'')
            AS UNSIGNED) BETWEEN 0 AND 255),
      TRUE,
      FALSE);

1, , , - faff, ? :

WHERE ipField REGEXP '10\\.0\\.[0-9]{1,3}\\.1'

You can change this expression a little, but basically this is, in my opinion, the easiest way to request these IP addresses.
For more on MySQL string functions (which will almost always be painful for writing), see the docs here

0
source

More articles:


All Articles