Indexing Column With REPLACE Function In MySQL

May be a silly question to ask. I need to use this a lot in both join and where: REPLACE(table_a.column_1, '-', '') = REPLACE(table_b.column_2, '-', '') since bit data inconsisten

Solution 1:

There is no such thing as computed column in MySQL.

If you want to format some value to speed-up index search, you will probably have to use some trigger. As a matter of fact, I answered almost the same question this morning: see https://stackoverflow.com/a/18047511/2363712 for a similar example.

In your case, that would lead to something like that:

CREATE TABLE `table_a`(`column_1` VARCHAR(255), ... ,
                       `column_1_fmt` VARCHAR(255),
                       INDEX(`column_1_fmt`));

CREATE TRIGGER ins_table_a BEFORE INSERT ON `table_a`
FOR EACH ROW
    SET NEW.`column_1_fmt` = REPLACE(NEW.column_1, '-', '');

CREATE TRIGGER upd_table_a BEFORE UPDATE ON `table_a`
FOR EACH ROW
    SET NEW.`column_1_fmt` = REPLACE(NEW.column_1, '-', '');

Now you will use column_1_fmt to search for values/join on values having the required format.

---

Concerning your special need (removing dashes -- from some kind of serial/reference number?). Maybe you should reverse the problem. Store those value as canonical form (no dash). And add required dashes at SELECT time.