sql - usar - porque no está indizado por texto completo

MySQL búsqueda de texto completo con palabras parciales

Question

La búsqueda de texto completo en MySQL parece ser excelente y la mejor forma de buscar en SQL. Sin embargo, me parece que estoy atascado en el hecho de que no buscará palabras parciales. Por ejemplo, si tengo un artículo titulado "Tutorial de MySQL" y busco "MySQL", no lo encontrará.

Después de realizar algunas búsquedas, encontré varias referencias para admitir esto en MySQL 4 (estoy usando 5.1.40). He intentado usar " MySQL " y "% MySQL%", pero ninguno funciona (un enlace que encontré sugirió que era una estrella, pero solo se podía hacer al final o al principio, no a ambos).

Aquí está mi estructura de tabla y mi consulta, si alguien me pudiera decir dónde me va mal, sería genial. Supongo que la coincidencia parcial de palabras está integrada de alguna manera.

CREATE TABLE IF NOT EXISTS `articles` ( `article_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `article_name` varchar(64) NOT NULL, `article_desc` text NOT NULL, `article_link` varchar(128) NOT NULL, `article_hits` int(11) NOT NULL, `article_user_hits` int(7) unsigned NOT NULL DEFAULT ''0'', `article_guest_hits` int(10) unsigned NOT NULL DEFAULT ''0'', `article_rating` decimal(4,2) NOT NULL DEFAULT ''0.00'', `article_site_id` smallint(5) unsigned NOT NULL DEFAULT ''0'', `article_time_added` int(10) unsigned NOT NULL, `article_discussion_id` smallint(5) unsigned NOT NULL DEFAULT ''0'', `article_source_type` varchar(12) NOT NULL, `article_source_value` varchar(12) NOT NULL, PRIMARY KEY (`article_id`), FULLTEXT KEY `article_name` (`article_name`,`article_desc`,`article_link`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

INSERT INTO `articles` VALUES (1, ''MySQL Tutorial'', ''Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'', ''http://www.domain.com/'', 6, 3, 1, ''1.50'', 1, 1269702050, 1, ''0'', ''0''), (2, ''How To Use MySQL Well'', ''Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'', ''http://www.domain.com/'', 1, 2, 0, ''3.00'', 1, 1269702050, 1, ''0'', ''0''), (3, ''Optimizing MySQL'', ''Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'', ''http://www.domain.com/'', 0, 1, 0, ''3.00'', 1, 1269702050, 1, ''0'', ''0''), (4, ''1001 MySQL Tricks'', ''Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'', ''http://www.domain.com/'', 0, 1, 0, ''3.00'', 1, 1269702050, 1, ''0'', ''0''), (5, ''MySQL vs. YourSQL'', ''Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'', ''http://www.domain.com/'', 0, 2, 0, ''3.00'', 1, 1269702050, 1, ''0'', ''0''), (6, ''MySQL Security'', ''Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'', ''http://www.domain.com/'', 0, 2, 0, ''3.00'', 1, 1269702050, 1, ''0'', ''0'');

SELECT count(a.article_id) FROM articles a WHERE MATCH (a.article_name, a.article_desc, a.article_link) AGAINST (''mysql'') GROUP BY a.article_id ORDER BY a.article_time_added ASC

El prefijo se usa porque proviene de una función que a veces agrega uniones adicionales.

Como puede ver, una búsqueda de MySQL debería devolver un conteo de 6, pero desafortunadamente no lo hace.

Actualizar

No se obtuvieron resultados, ya que cada fila individual fue igualada

http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

"El resultado de la búsqueda está vacío porque la palabra" MySQL "está presente en al menos el 50% de las filas. Como tal, se trata de manera efectiva como una palabra de parada. Para grandes conjuntos de datos, este es el comportamiento más deseable: una consulta de lenguaje natural no debe devolver cada segunda fila de una tabla de 1 GB. Para conjuntos de datos pequeños, puede ser menos deseable ".

Answer 1

Citando el manual de MySQL: "El resultado de la búsqueda está vacío porque la palabra" MySQL "está presente en al menos el 50% de las filas". http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Answer 2

MATCH (a.article_name) AGAINST (''MySQL*'' IN BOOLEAN MODE) entendido, los índices de MySQL FULLTEXT admiten la búsqueda de prefijos ( MATCH (a.article_name) AGAINST (''MySQL*'' IN BOOLEAN MODE) ) solamente.