Is it possible to have function-based index in MyS

问题:

I recall in Oracle it is possible to index based on a function, e.g. SUBSTRING(id,1,8).

Does MySQL support this? If not, is there is any alternative?

回答1:

No, not in a general sense, I don\'t believe even 5.6 has this functionality. It is possible to only use the leading part of a column (this functionality has been around for a long time), but not one starting at the second or subsequent characters, or any other more complex function.

For example, the following creates an index using the first five characters of a name:

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

For more complex expressions, you can achieve a similar effect by having another column with the indexable data in it, then using insert/update triggers to ensure it\'s populated correctly.

Other than the wasted space for redundant data, that\'s pretty much the same thing.

And, although it technically violates 3NF, that\'s mitigated by the use of triggers to keep the data in sync (this is something that\'s often done for added performance).

回答2:

MySQL does not support this, but there is an alternative.

1. Since MySQL 5.7.6

You can use an auto generated column to hold the substring with an index on it:

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

As Benjamin noted, InnoDB supports secondary indexes on virtual columns so the STORED keyword can be ommitted. In fact secondary indexes on virtual columns may be preferable. More info here: Secondary Indexes and Generated Columns

2. Before MySQL 5.7.6

You can use a column updated by a trigger with an index on it:

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

回答3:

This is possible as of MySQL 5.7.5 using the new Generated Columns.