MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (id TEXT, email VARCHAR(255));
CREATE UNIQUE INDEX auth0_users_email_idx 
ON users ((CASE WHEN id NOT LIKE 'auth0|%' THEN email END));

Records: 0  Duplicates: 0  Warnings: 0


INSERT INTO users VALUES ('1', 'email1@dummy.com');
INSERT INTO users VALUES ('2', 'email2@dummy.com');
INSERT INTO users VALUES ('auth0|email1', 'email1@dummy.com');
INSERT INTO users VALUES ('auth0|email1', 'email3@dummy.com');
INSERT INTO users VALUES ('3', 'email1@dummy.com');

Duplicate entry 'email1@dummy.com' for key 'users.auth0_users_email_idx'


INSERT INTO users VALUES ('auth0|email2', 'email1@dummy.com');
DROP TABLE users;
CREATE TABLE users (id TEXT, email TEXT);
CREATE UNIQUE INDEX auth0_users_email_idx 
ON users ((CASE WHEN id NOT LIKE 'auth0|%' THEN email END));

Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.