MySQL 中部分索引或过滤索引的解决方法？

Question

我正在使用 MySQL 数据库。我知道 PostgreSQL 和 SQL Server 支持部分索引。就我而言，我想做这样的事情：

CREATE UNIQUE INDEX myIndex ON myTable (myColumn) where myColumn <> 'myText'

我想创建一个唯一的约束，但如果它是特定的文本，它应该允许重复。

我找不到在 MySQL 中执行此操作的直接方法。但是，有解决方法可以实现这一目标吗？

Answer 1

过滤索引可以用函数索引和 CASE 表达式（MySQL 8.0.13 及更高版本）来模拟：

CREATE TABLE t(id INT PRIMARY KEY, myColumn VARCHAR(100));

-- NULL are not taken into account with `UNIQUE` indexes   
CREATE UNIQUE INDEX myIndex ON t((CASE WHEN myColumn <> 'myText' THEN myColumn END));


-- inserting excluded value twice
INSERT INTO t(id, myColumn) VALUES(1, 'myText'), (2, 'myText');

-- trying to insert different value than excluded twice
INSERT INTO t(id, myColumn) VALUES(3, 'aaaaa');

INSERT INTO t(id, myColumn) VALUES(4, 'aaaaa');
-- Duplicate entry 'aaaaa' for key 'myIndex'

SELECT * FROM t;

db>>小提琴演示

输出：

+-----+----------+
| id  | myColumn |
+-----+----------+
|  1  | myText   |
|  2  | myText   |
|  3  | aaaaa    |
+-----+----------+

Answer 2

我想只有一种方法可以实现这一目标。您可以向表中添加另一列，在其上创建索引并创建触发器，或者在存储过程中进行插入/更新，以使用以下条件填充此列：

if value = 'myText' then put null
otherwise put value

希望有帮助

Answer 3

最好的解决方法是创建一个具有必要条件的持久生成列，并在其上添加唯一索引。