Workaround in MySQL for partial Index or filtered Index?

15.6k views Asked by At

I am using a MySQL database. I know PostgreSQL and SQL Server supports 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 particular text.

I couldn't find a direct way to do this in MySQL. But, is there a workaround to achieve it?

3

There are 3 answers

0
ravnur On BEST ANSWER

I suppose there is only one way to achieve it. You can add another column to your table, create index on it and create trigger or do insert/update inside your stored procedures to fill this column using following condition:

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

Hope it helps

3
Lukasz Szozda On

Filtered indexes could be emulated with function indexes and CASE expression(MySQL 8.0.13 and newer):

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<>fiddle demo

Output:

+-----+----------+
| id  | myColumn |
+-----+----------+
|  1  | myText   |
|  2  | myText   |
|  3  | aaaaa    |
+-----+----------+
1
Ozzy Black On

The best workaround is to create a persisted generated column with the necessary condition and a add unique index on it.