MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE
  `customer_group` (
    `id` integer NOT NULL AUTO_INCREMENT,
    `created_at` datetime NOT NULL DEFAULT NOW(),
    `updated_at` datetime NOT NULL DEFAULT NOW(),
    `deleted_at` datetime,
    `name` text NOT NULL,
    `metadata` text,
    CONSTRAINT `PK_142c3338-da81-4d0c-8cd8-490bb41cd187` PRIMARY KEY (`id`)
  );
CREATE UNIQUE INDEX `IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9` ON `customer_group` (`name`)
WHERE
  `deleted_at` IS NULL;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE
  `deleted_at` IS NULL' at line 2


DROP TABLE `customer_group`;
CREATE TABLE
  `customer_group` (
    `id` integer NOT NULL AUTO_INCREMENT,
    `created_at` datetime NOT NULL DEFAULT NOW(),
    `updated_at` datetime NOT NULL DEFAULT NOW(),
    `deleted_at` datetime,
    `name` text NOT NULL,
    `metadata` text,
    `deleted_row` bit as (CASE WHEN deleted_at is null THEN false ELSE true END),
    CONSTRAINT `PK_142c3338-da81-4d0c-8cd8-490bb41cd187` PRIMARY KEY (`id`)
  );
-- added (100) to _solve_: BLOB/TEXT column 'name' used in key specification without a key length
CREATE UNIQUE INDEX `IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9` ON 
  `customer_group` (`deleted_row`,`name`(100),`id`);

Records: 0  Duplicates: 0  Warnings: 0


-- short test
INSERT INTO `customer_group`(name) VALUES ('name1');
SELECT SLEEP(1);
INSERT INTO `customer_group`(name) VALUES ('name2');
SELECT SLEEP(1);
INSERT INTO `customer_group`(name) VALUES ('name3');
SELECT SLEEP(1);
INSERT INTO `customer_group`(name) VALUES ('name4');


SLEEP(1)


0




SLEEP(1)


0




SLEEP(1)


0


select * from `customer_group`;


id
created_at
updated_at
deleted_at
name
metadata
deleted_row


1
2023-04-19 08:38:30
2023-04-19 08:38:30
null
name1
null
0

2
2023-04-19 08:38:31
2023-04-19 08:38:31
null
name2
null
0

3
2023-04-19 08:38:32
2023-04-19 08:38:32
null
name3
null
0

4
2023-04-19 08:38:33
2023-04-19 08:38:33
null
name4
null
0


UPDATE `customer_group` 
SET deleted_at = now()
WHERE second (created_at) mod 2=1;

Rows matched: 2  Changed: 2  Warnings: 0


select * from `customer_group`;


id
created_at
updated_at
deleted_at
name
metadata
deleted_row


1
2023-04-19 08:38:30
2023-04-19 08:38:30
null
name1
null
0

2
2023-04-19 08:38:31
2023-04-19 08:38:31
2023-04-19 08:38:33
name2
null
1

3
2023-04-19 08:38:32
2023-04-19 08:38:32
null
name3
null
0

4
2023-04-19 08:38:33
2023-04-19 08:38:33
2023-04-19 08:38:33
name4
null
1


select * 
from `customer_group`
where not deleted_row;


id
created_at
updated_at
deleted_at
name
metadata
deleted_row


1
2023-04-19 08:38:30
2023-04-19 08:38:30
null
name1
null
0

3
2023-04-19 08:38:32
2023-04-19 08:38:32
null
name3
null
0


explain 
select * 
from `customer_group`
where not deleted_row;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
customer_group
null
ref
IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9
IDX_d12ecf48-302c-4292-8c8d-d2cc7c8149f9
2
const
4
100.00
null

id	created_at	updated_at	deleted_at	name	metadata
1	2023-04-19 08:38:30	2023-04-19 08:38:30	null	name1	null
2	2023-04-19 08:38:31	2023-04-19 08:38:31	null	name2	null
3	2023-04-19 08:38:32	2023-04-19 08:38:32	null	name3	null
4	2023-04-19 08:38:33	2023-04-19 08:38:33	null	name4	null