MySQL中具有REPLACE函数的索引列 [英] Indexing column with REPLACE function in mySQL

问题描述

可能是一个愚蠢的问题.

May be a silly question to ask.

我需要在 join 和 where 中大量使用此功能:

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 inconsistency from other companies' system

使用REPLACE函数，是否可能具有任何其他数据库类型的列索引?例如:

Is that possible or any other database type can have index of a column with REPLACE function? eg:

ALTER TABLE `table_a` ADD INDEX ( REPLACE(`column_1`, '-', '') ) ;

数据库类型:MyISAM

Database Type: MyISAM

推荐答案

没有如果要格式化某些值以加快索引搜索的速度，则可能必须使用一些触发器.实际上，我今天早上回答了几乎相同的问题:请参见 https://stackoverflow.com/a/18047511/2363712 类似的例子.

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

现在，您将使用column_1_fmt搜索值/对具有所需格式的值进行联接.

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

关于您的特殊需要(要从某种序列号/参考号中删除破折号吗?).也许您应该扭转问题.将这些值存储为规范形式(无破折号).并在SELECT时间添加所需的破折号.

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.

这篇关于MySQL中具有REPLACE函数的索引列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！