Describe：
Porject: SSM
DB table：
CREATE TABLE public.test
(
id int,
ip inet,
a character varying
)
INSERT INTO public.test(
id, ip, a)
VALUES (1, '1111:0000:0101:000A:0002:0003:0004:0005', 'admin');
INSERT INTO public.test(
id,ip, a)
VALUES (2, '1111:0001:0101:000A:0002:0003:0004:0005', 'admin');
INSERT INTO public.test(
id, ip, a)
VALUES (3, '1111:0011:0101:000A:0002:0003:0004:0005', 'admin');
INSERT INTO public.test(
id, ip, a)
VALUES (4, '1111:0111:0101:000A:0002:0003:0004:0005', 'admin');
INSERT INTO public.test(
id, ip, a)
VALUES (5, '1111:1111:0101:000A:0002:0003:0004:0005', 'admin');
The ip value of the records is '111:0:0101:a:2:3:4:5','111:1:0101:a:2:3:4:5','111:11:0101:a:2:3:4:5','111:111:0101:a:2:3:4:5','111:1111:0101:a:2:3:4:5' in Postgres DB.
We want search the record by ip. The ip input is '1111:0',we want the result is (1-4)records.
自己试了一下
如果输入：1111:0
那么数据库中可能匹配的值是1111:0000，1111:000X，1111:00XX，1111:0XXX
我的SQL这样写的
select * from test where
cast((ip)as varchar) like '%1111::%'
or cast((ip)as varchar) like '%1111:_:%'--1位
or cast((ip)as varchar) like '%1111:__:%'--2位
or cast((ip)as varchar) like '%1111:___:%'--3位
or cast((ip)as varchar) like '%1111:_____'--最后一位
or cast((ip)as varchar) like '%1111:______'--最后2位
or cast((ip)as varchar) like '%1111:_______'--最后3位
不知道还有什么好方法使检索更有效率？