java mysql在match(ip_list)与(string)的比较中使用a“”作为值分隔符，““、'string'也存在

在处理IP地址并比较它们时，出现了一些问题，它几乎找到了数据库中的所有数据。

SELECT * FROM superbans WHERE NOT uuid=? AND (MATCH(ip_list) AGAINST(?) OR MATCH(clientId_list) AGAINST(?) OR MATCH(deviceId_list) AGAINST(?) OR MATCH(xuid_list) AGAINST(?))

uuid示例：第一个月
示例IP：111.111.111.111
客户端ID示例：4848256256931583384 (but clientId maybe contains "-": -4848256256931583384)
器械ID示例：4a474f8b-3032-35fb-bd27-fb4e3c5435fd
示例xuid：2535454407347849
如果我从查询中删除MATCH（ip_list）AGAINST（？），它会正确地输出数据。请帮助我使mysql不认为.“”是分隔符。
重要提示：在与“.”的行中也有“，”
我试着通过REGEXP来做，因为我刚开始学习mysql，所以没有成功：（

是的，.作为分隔符处理（逗号、空格和许多其他字符也是如此）。要匹配确切的IP地址，请在每个值周围加上双引号。否则，您将得到与111（在示例IP中）匹配的结果。
下面的示例演示了使用和不使用双引号的结果：

select * from country where countryname='United States';
+-------------+---------------+
| countrycode | countryname   |
+-------------+---------------+
| USA         | United States |
+-------------+---------------+

SELECT *  FROM country  WHERE  MATCH(countryname) AGAINST('United States');
+-------------+--------------------------------------+
| countrycode | countryname                          |
+-------------+--------------------------------------+
| UMI         | United States Minor Outlying Islands |
| USA         | United States                        |
| FSM         | Micronesia, Federated States of      |
| ARE         | United Arab Emirates                 |
| GBR         | United Kingdom                       |
+-------------+--------------------------------------+

SELECT *  FROM country  WHERE  MATCH(countryname) AGAINST('United.States');
+-------------+--------------------------------------+
| countrycode | countryname                          |
+-------------+--------------------------------------+
| UMI         | United States Minor Outlying Islands |
| USA         | United States                        |
| FSM         | Micronesia, Federated States of      |
| ARE         | United Arab Emirates                 |
| GBR         | United Kingdom                       |
+-------------+--------------------------------------+

SELECT *  FROM country  WHERE  MATCH(countryname) AGAINST('"United States"');
+-------------+--------------------------------------+
| countrycode | countryname                          |
+-------------+--------------------------------------+
| UMI         | United States Minor Outlying Islands |
| USA         | United States                        |
+-------------+--------------------------------------+

SELECT *  FROM country  WHERE  MATCH(countryname) AGAINST('"United States","United Kingdom"');
+-------------+--------------------------------------+
| countrycode | countryname                          |
+-------------+--------------------------------------+
| GBR         | United Kingdom                       |
| UMI         | United States Minor Outlying Islands |
| USA         | United States                        |
+-------------+--------------------------------------+

但是请注意，如果这样做，您就无法利用全文搜索的强大功能。您是否希望匹配部分IP地址？如果不希望，您可以使用如下所示的IN语句：

select * from country where countryname in ('United States', 'United Kingdom');
+-------------+----------------+
| countrycode | countryname    |
+-------------+----------------+
| GBR         | United Kingdom |
| USA         | United States  |
+-------------+----------------+

java mysql在match(ip_list)与(string)的比较中使用a“”作为值分隔符，““、'string'也存在

1条答案

相关问题

热门标签

最新问答