La colación que usan los índices FULLTEXT no es una propiedad que se puede establecer durante la creación del índice y depende más bien de la colación prevaleciente en la lista de campos que conforman dicho índice. Por otra parte, los principales editores de texto y procesadores de palabras manejan una opción que permite buscar de las dos formas, tanto insensitiva como sensitiva a mayúsculas, con solamente activar una casilla de verificación. En este tutorial te enseñaré cómo puedes hacerlo también desde Laravel.
Veamos un definición de columnas e índices FULLTEXT en Laravel:
Schema::create('chapters', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->longText('content');
$table->string('translator');
$table->timestamps();
});
DB::statement('
create fulltext index chapters_title_content_fulltext
on chapters(title, content);
');
Ahora, para no llevarnos sorpresas de lo que hace internamente Laravel, vamos a consultar qué conjunto de caracteres y colación tiene el índice FULLTEXT desde MySQL:
mysql[fulltext_search]>SHOW CREATE TABLE chapters\G
*************************** 1. row ***************************
Table: chapters
Create Table: CREATE TABLE `chapters` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`translator` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `chapters_title_content_fulltext` (`title`,`content`),
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.07 sec)
Podemos apreciar que la colación está definida a nivel de columna como utf8mb4_unicode_ci y es un valor predeterminado que usa Laravel, porque la colación predeterminada de MySQL 8 es utf8mb4_0900_ai_ci.
Aunque las columnas del índice title y content sólo hacen uso de la cláusula COLLATE para especificar la colación, sabemos que una colación pertenece a un único conjunto de caracteres en este caso utf8mb4 que a su vez debe ser compatible con esta.
Desafortunadamente, una vez que se crea el índice FULLTEXT, no podemos cambiar la colación sin que tengamos que modificar la definición del campo y volver a recrear el índice ejecutando nuevamente las migraciones. Esto significa que si el campo es insensitivo a mayúsculas, el índice de FULLTEXT tomará tanto el conjunto de caracteres como la colación utilizada por este.
Ya hemos visto cómo pueden ser afectados los resultados de una búsqueda con ésta característica, y de aquí que es importante poder cambiar esto desde nuestra aplicación, por ejemplo, para tener un índice sensible a mayúsculas deberíamos cambiar la migración tal como lo muestra el siguiente código:
Schema::create('chapters', function (Blueprint $table) {
$table->id();
$table->string('title')->collation('utf8mb4_bin');
$table->longText('content')->collation('utf8mb4_bin);
$table->string('translator');
$table->timestamps();
});
DB::statement('
create fulltext index chapters_title_content_fulltext
on chapters(title, content);
');
Por supuesto, después hay que volver a ejecutar las migraciones y seeders para que el índice se recargue con los nuevos datos.
Son nuevos datos, porque el índice FULLTEXT va a cambiar todas las estadísticas del análisis que hace de las palabras.
El criterio de selección para usar un índice
Otro aspecto que hay que considerar cuando tengamos dos índices FULLTEXT en una misma tabla, es cómo podemos saber cuál es el que realmente se está utilizando. Por ejemplo, en el siguiente fragmento:
Route::get('/', function () {
return Chapter::query()
->when(request('search'), function ($query, $search) {
$query->select('id', 'title', 'content')
->selectRaw('match(title,content) against (? in natural language mode) as score', [$search])
->whereRaw('match(title, content) against (? in natural language mode) > 0.0000001', [$search]);
})
->get();
});
Las columnas que se mencionan en la cláusula WHERE determinan el índice que se va a utilizar (no sirve otro lugar), en cuyo caso la expresión match(title, content) indica que se escoja un índice FULLTEXT definido sobre los dos campos title y content.
Comenzando por crear los dos índices simultáneamente
Aunque ya hemos mencionado que no es posible crear dos índices simultáneos que compartan los mismos campos pero que utilicen colaciones diferentes, con un artificio podemos hacerlo si utilizamos los modificadores de columnas de Laravel para definir columnas generadas:
public function up()
{
Schema::create('chapters', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->longText('content');
$table->string('cs_title')->collation('utf8mb4_bin')->storedAs('title');
$table->longText('cs_content')->collation('utf8mb4_bin')->storedAs('content');
$table->string('translator');
$table->timestamps();
});
DB::statement('
create fulltext index chapters_title_content_fulltext
on chapters(title, content);
');
DB::statement('
create fulltext index chapters_cs_title_cs_content_fulltext
on chapters(cs_title, cs_content);
');
}
Ejecuta las migraciones y los seeders para que se creen los índices.
Recuerda que tanto el campo title como content, usan el conjunto de caracteres utf8mb4 y LA colación utf8mb4_unicode_ci como mencionamos al principio. Sin embargo, ahora hemos creado dos columnas autogeneradas prefijado con cs_ como nemotécnico de case-sensitivity, tanto para cs_title como cs_content.
Los valores de una columna generada son calculados desde una expresión incluida en la definición de columna de una tabla.
Otro elemento clave aquí, es que utiliza otra colación distinta utf8mb4_bin que deseamos tener para el segundo índice chapters_cs_title_cs_content_fulltext.
Habilitando el índice adecuado
Puesto que ya hemos dicho que los índices se seleccionan por la mención que hacemos de los campos en la clásula WHERE, se vuelve obvio que debemos establecer este criterio también en nuestro código:
Route::get('/', function () {
return Chapter::query()
->when(request('search'), function ($query, $search) {
$query->select('id', 'title', 'content')
->selectRaw('match(title,content) against (? in natural language mode) as score', [$search])
->whereRaw('match(title, content) against (? in natural language mode) > 0.0000001', [$search]);
})
->get();
});
Route::get('/con_sensibilidad/', function () {
return Chapter::query()
->when(request('search'), function ($query, $search) {
$query->select('id', 'title', 'content')
->selectRaw('match(cs_title,cs_content) against (? in natural language mode) as score', [$search])
->whereRaw('match(cs_title, cs_content) against (? in natural language mode) > 0.0000001', [$search]);
})
->get();
});
Vemos que tenemos dos rutas, la primera para las búsquedas insensibles a mayúsculas que usarán el primer índice chapters_title_content_fulltext puesto que en la cláusula WHERE se mencionan los campos que lo conforman match(title, content).
La segunda ruta está diferenciada porque agregamos el path /con_sensibilidad/ y puedes observar que en la cláusula WHERE mencionan las columnas autogeneradas match(cs_title, cs_content) que permiten hacer las búsquedas sensibles a mayúsculas, gracias a que utiliza el índice chapters_cs_title_cs_content_fulltext que usa la colación binaria.
Haciendo una búsqueda insensitiva a mayúscula
Puedes inspecionar todos los registros que hay en la base de datos, tal como venimos haciendo hasta ahora, visitando la dirección http://127.0.0.1:8000/ sin utilizar parámetros.
Ahora buscaremos de forma insensitiva, de la siguiente manera: http://127.0.0.1:8000/?search=eloQuent
Observa que la mayúscula en el intermedio de la palabra no afectó los resultados.
Haciendo una búsqueda sensitiva a mayúscula
De igual modo, puedes verificar que la base de datos que se usa para la búsqueda sensitiva es la misma si navegas hasta la siguiente dirección: http://127.0.0.1:8000/con_sensibilidad/
Y si repetimos la búsqueda anterior, esperaremos que falle en una forma sensitiva. Esto es: http://127.0.0.1:8000/con_sensibilidad/?search=eloQuent
En efecto, no devuelve ningún resultado coincidente.
Quiere decir que la búsqueda sensitiva correcta es: http://127.0.0.1:8000/con_sensibilidad/?search=Eloquent
La cuál devuelve los registros adecuados.
Finalmente, no podemos dejar de mencionar que hay una penalización que tiene que ver con el espacio de almacenamiento que usan los campos generados declarados de tipo stored , sin embargo, MySQL se encuentra en continuo desarrollo y se espera que más adelante los índices FULLTEX superen esta limitación y se puedan crear también a partir de columnas virtuales, tal como actualmente ocurre con los índices secundarios tipo INDEX.
Regístrate hoy en Styde y obtén acceso a todo nuestro contenido.
Lección anterior La sensibilidad a las mayúsculas en las búsquedas de texto completo