mysql 条件索引_如何在MySQL中创建条件索引？

MySQL目前不支持条件索引.

为了得到你想要的东西(不是你应该这样做;))你可以开始创建一个辅助表：

CREATE TABLE `my_schema`.`auxiliary_table` (

`id` int unsigned NOT NULL,

`name` varchar(250), /* specify the same way as in your main table */

PRIMARY KEY (`id`),

KEY `name` (`name`)

);

然后在主表中添加三个触发器：

delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table

FOR EACH ROW

BEGIN

IF NEW.status = 'ACTIVE' THEN

REPLACE auxiliary_table SET

auxiliary_table.id = NEW.id,

auxiliary_table.name = NEW.name;

END IF;

END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table

FOR EACH ROW

BEGIN

IF NEW.status = 'ACTIVE' THEN

REPLACE auxiliary_table SET

auxiliary_table.id = NEW.id,

auxiliary_table.name = NEW.name;

ELSE

DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;

END IF;

END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table

FOR EACH ROW

BEGIN

DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;

END;//

delimiter ;

We need delimiter // because we want to use ; inside the triggers.

这样,辅助表将包含与包含字符串“ACTIVE”的主表行对应的ID,由触发器更新.

要在select上使用它,您可以使用通常的连接：

SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table

ON auxiliary_table.id = main_table.id

ORDER BY auxiliary_table.name;

如果主表已包含数据,或者如果您进行了一些以异常方式更改数据的外部操作(E.G.：在MySQL之外),则可以使用以下方法修复辅助表：

INSERT INTO auxiliary_table SET

id = main_table.id,

name = main_table.name,

WHERE main_table.status="ACTIVE";

关于性能,可能你会有较慢的插入,更新和删除.只有当你真正处理所需条件为正的少数情况时,这才有意义.即使这样,可能只测试你可以看到节省的空间是否真的证明了这种方法(如果你真的在节省任何空间).