Problem when making a query with the MATCH and AGAINTS in mysql

Question

Problem when making a query with the MATCH and AGAINTS in mysql

Navigation

#1 by (0 votes)

0

I have the following problem is that I want to perform a user search, with the use of match againts and it is the next fragment of code.

SELECT
  pur.url_product, us.Name,us.LastName, pur.fecha_compra,
  count.Name_country, ci.Name_city, pur.status_compra, pur.type_compra,
  MATCH(us.Name,us.LastName)  AGAINST("jhon mrurillo") AS names 
FROM compras pur 
INNER JOIN users us ON pur.id_user = us.id_users 
INNER JOIN countrys count ON pur.id_country = count.id_country 
INNER JOIN citys_ ci ON pur.id_city = ci.id_city 
WHERE MATCH(us.Name, us.LastName) AGAINST("jhon murillo")

In which I am trying to show me the user's registry with the name jhon murillo and the problem arises that, when it will show the data, because there is a user with the name < em> jhon carillo and he shows it to me too. That for obvious reasons I should not do it.
How do I solve that?

mysql sql mariadb

asked by Jhon Dember Murillo Mendez 26.04.2018 в 21:52

source

1 answer

Is this loop safe to protect my system using php? How to configure JSON output in logs / console with SPRINGBOOT + LOGBACK with YAML properties

score 0 · Answer 1

Queries that use MATCH and AGAINST are known as queries FULLTEXT or full text, and have a very specific use: search all occurrences of the values sought in generally large fields, such as fields of type TEXT .

To make you an idea, a query of this type would serve to find certain words or phrases in a book or in the chapter of a book stored in a column of type TEXT .

The MySQL documentation thus defines this type of search:

The full text search ( FULLTEXT ) is performed using the syntax MATCH () ... AGAINST . MATCH() takes a separate list by commas that names the columns to search. AGAINST takes a string to search and an optional modifier that indicates what type of search perform. The search string must be a string value that is constant during the evaluation of the query. This rules, for example, a table column because it may differ for each row.

To apply this type of query there are some restrictions that also affect the table and that should not be applied under any concept, unless you really need to do searches of this type.

These restrictions are:

The table must have a full text index ( FULLTEXT ), which must be indicated in CREATE TABLE or later.
Full-text indexes can only be used with tables InnoDB or MyISAM , and can only be created for columns CHAR , VARCHAR or TEXT .
For large data sets, it is much faster to load your data into a table that does not have FULLTEXT index and then create the index after that, than to load data into a table that has a FULLTEXT index Existing%.

This last statement, like all the others, can clearly show the serious error that could be to endow a table with a FULLTEXT index only to search for a name and a surname.

Opt for the classic solution

In your case, I believe that opting for the solution of all life would be the right thing to do. If you want the records whose name is X and whose last name is Y , you can do it with a WHERE , as it has always been done:

SELECT
  pur.url_product, us.Name,us.LastName, pur.fecha_compra,
  count.Name_country, ci.Name_city, pur.status_compra, pur.type_compra
FROM compras pur 
INNER JOIN users us ON pur.id_user = us.id_users 
INNER JOIN countrys count ON pur.id_country = count.id_country 
INNER JOIN citys_ ci ON pur.id_city = ci.id_city 
WHERE us.Name='jhon' AND us.LastName='murillo';

Now, if you want to look up word lists in the chapters of Jhon Murillo's book, then yes, you could consider giving the table libros_completos of an index FULLTEXT .

That's all!

I hope you find it useful.