MySQL 使用 "." 作为 match(ip_list) against (string) 中的值分隔符，"," "string" 也存在

Question

在处理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:e778e3d4-7866-3601-b069-d38dd1ed2e21

示例ip:111.111.111.111

示例clientId:4848256256931583384 (but clientId maybe contains "-": -4848256256931583384)

示例deviceId:4a474f8b-3032-35fb-bd27-fb4e3c5435fd

示例xid:2535454407347849

如果我从查询中删除MATCH(ip_list) AGAINST(?)，它将正确输出数据。请帮助我使mysql不认为";这是分隔符

重要:在与"."也有";

我试着通过REGEXP来做，因为我刚刚开始学习mysql，然后我没有成功:(

Answer 1

是的。作为分隔符处理(逗号、空格和许多其他字符也是如此)。要匹配精确的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  |
+-------------+----------------+