FullText Indexing IPv6 addresses with MySQL 5.7

Posted on by

MySQL 5.7 supports generated fields. This is particularly useful for searching the string representation of numeric stored ip addresses:

CREATE TABLE catalog(
ip varbinary(16) not null,
hostname varchar(64) not null,
label varchar(64),
ip_ntoa varchar(64) generated always as (inet6_ntoa(ip)) STORED, -- generate and store fields with the address representation
fulltext key (hostname, ip_ntoa, label)
);

When inserting values

INSERT INTO catalog(ip,hostname,label) VALUES
(inet6_aton('127.0.0.1'), 'localhost', 'lo'),
(inet6_aton('192.168.0.1'), 'gimli', 'stage,ipv4'),
(inet6_aton('fdfe::5a55:caff:fefa:9089'), 'legolas', 'router,ipv6'),
(inet6_aton('fdfe::5a55:caff:fefa:9090'), 'boromir', 'router,ipv6')

you can search in OR mode with

SELECT hostname FROM catalog WHERE
  MATCH(ip_ntoa, hostname, label)
  AGAINST('9089 router');
-- returns every entry matching ANY needle
***1***
hostname: legolas
***2***
hostname: boromir

Or exact matches

SELECT hostname FROM catalog WHERE
  MATCH(ip_ntoa, hostname, label)
  AGAINST('+9089 +router' in boolean mode);
-- returns ONE entry matching ALL needles
***1***
hostname: legolas

Lascia un commento