Mysql workaround for partial index or filtered index?

Question

I am using mysql db. I know that postgresql and SQL server support partial indexing. In my case, I want to do something like this:

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

I want to create a unique constraint, but it should allow duplicates if it is a specific text.

I could not find a direct way to do this in mysql. But is there a workaround?

Answer 1

, . , / , , :

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

,

Answer 2

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 & lt;>

:

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