mysql uses "." as a value separator in match(ip_list) against(string), with ","-CodePudding

When processing ip addresses, and comparing them, something goes wrong, and it finds almost all the data from the database. Query:

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(?))

Example uuid: e778e3d4-7866-3601-b069-d38dd1ed2e21

Example ip: 111.111.111.111

Example clientId: 4848256256931583384 (but clientId maybe contains "-": -4848256256931583384)

Example deviceId: 4a474f8b-3032-35fb-bd27-fb4e3c5435fd

Example xuid: 2535454407347849

If I remove MATCH(ip_list) AGAINST(?) from the query, it outputs the data correctly. Please help me to make mysql not think that "." this is the delimiter.

IMPORTANT: in the line with "." also has ","

I tried to do it through REGEXP, because I'm just starting to learn mysql, then I didn't succeed :(

CodePudding user response：

Yes, the . is handled as a delimiter (as are commas, spaces, and many other characters). To match the exact IP address, put double quotes around each value. Otherwise, you are going to get results matching anything with 111 (in your example IP).

Here is an example to demonstrate the results with and without the double quotes:

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                        |
 ------------- --------------------------------------

But note you're not leveraging the power of fulltext search if you do this. Do you want to match partial IP addresses? If not, you could just use an IN statement like this example:

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