¿Es posible tener un índice basado en funciones en MySQL?

Question

¿Es posible tener un índice basado en funciones en MySQL?

Recuerdo que en Oracle es posible indexar basado en una función, por ejemplo, SUBSTRING(id,1,8).

¿MySQL soporta esto? Si no, ¿ hay alguna alternativa?

49

mysql indexing

Author: Mat, 2012-05-15

Source

3 answers

MySQL no soporta esto, pero hay una alternativa.

1. Desde MySQL 5.7.6

Puede usar una columna generada automáticamente para mantener la subcadena con un índice en ella:

CREATE TABLE SomeTable (
    id CHAR(10),
    sub_id CHAR(8) AS SUBSTRING(id, 1, 8) STORED, INDEX(sub_id)
)

Como Benjamin señaló, InnoDB soporta índices secundarios en columnas virtuales para que la palabra clave ALMACENADA pueda ser ommitted. De hecho, los índices secundarios en columnas virtuales pueden ser preferibles. Más información aquí: Índices Secundarios y Generados Columnas

2. Antes de MySQL 5.7.6

Puede usar una columna actualizada por un disparador con un índice en ella:

CREATE TABLE SomeTable (
    id CHAR(10),
    sub_id CHAR(8) , INDEX(sub_id)
);

CREATE TRIGGER TR_SomeTable_INSERT_sub_id
    BEFORE INSERT
    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);

CREATE TRIGGER TR_SomeTable_UPDATE_sub_id
    BEFORE UPDATE
    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(NEW.id, 1, 8);

23

Author: axxis,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:26:27

Esto es posible a partir de MySQL 5.7.5 usando las nuevas Columnas generadas .

13

Author: Gabe Martin-Dempesy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-11-10 00:01:41

score 47 · Accepted Answer

No, no en un sentido general, no creo que incluso 5.6 tenga esta funcionalidad. es posible usar solo la parte inicial de una columna (esta funcionalidad ha existido durante mucho tiempo), pero no una que comience en el segundo o siguientes caracteres, o cualquier otra función más compleja.

Por ejemplo, lo siguiente crea un índice usando los primeros cinco caracteres de un nombre:

create index name_first_five on cust_table (name(5));

Para expresiones más complejas, puede lograr un efecto similar al tener otra columna con los datos indexables en ella, luego usar los disparadores de inserción/actualización para garantizar que se rellene correctamente.

Aparte del espacio desperdiciado para datos redundantes, eso es más o menos lo mismo.

Y, aunque técnicamente viola 3NF, eso se mitiga mediante el uso de disparadores para mantener los datos sincronizados (esto es algo que a menudo se hace para un rendimiento adicional).