MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
set @@explicit_defaults_for_timestamp=0;
-- таблица и начальные данные
CREATE TABLE test (id INT, 
                   ts TIMESTAMP ,
                   ts2 TIMESTAMP  NULL);
INSERT INTO test (id, ts) VALUES (1,'2022-01-01'), (2, NULL);
select * from test;


id
ts
ts2


1
2022-01-01 00:00:00
null

2
2022-01-24 21:33:10
null


show create table test;


Table
Create Table


test
CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ts2` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


-- триггер, предотвращающий вставку дубликатов, в т.ч. NULL-значений
CREATE TRIGGER tr_bi_check_uniqueness_with_nulls
BEFORE INSERT
ON test

FOR EACH ROW
BEGIN
DECLARE ErrorMessage CHAR(50);
--    SET @ts:=NEW.ts;

SET ErrorMessage:= CONCAT('Duplicated values not allowed. ', ISNULL(NEW.ts));
    IF EXISTS ( SELECT NULL
                FROM test
                WHERE test.id <=> NEW.id
                  AND test.ts <=> NEW.ts ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = ErrorMessage;
    END IF;
END
-- проверка, будет ли дубликат
SELECT EXISTS ( SELECT NULL
                FROM test
                WHERE test.id <=> 1
                  AND test.ts <=> '2022-01-01' ) AS check_data


check_data


1


-- попытка вставки - триггер блокирует
INSERT INTO test (id, ts) VALUES (1,'2022-01-01');
Duplicated values not allowed. 0

-- проверка, будет ли дубликат
SELECT EXISTS ( SELECT NULL
                FROM test
                WHERE test.id <=> 2
                  AND test.ts <=> NULL ) AS check_data


check_data


0


SET @t1:= NULL;
SET @t2:= NULL;
SELECT @t1 <=> @t2, ISNULL(@t1), ISNULL(@t2);



@t1 <=> @t2
ISNULL(@t1)
ISNULL(@t2)


1
1
1


-- попытка вставки - триггер почему-то не блокирует
INSERT INTO test (id, ts,ts2) VALUES (2, NULL,'2022-01-01');
Duplicated values not allowed. 0

INSERT INTO test (id, ts,ts2) VALUES (3, NULL, '2033-03-03');
SELECT SLEEP(2);


SLEEP(2)


0


INSERT INTO test (id, ts,ts2) VALUES (3, NULL, '2004-04-04');
INSERT INTO test (id) VALUES (3);
Duplicated values not allowed. 0

-- контроль - дубликат присутствует
SELECT id, ts, ts2 FROM test;


id
ts
ts2


1
2022-01-01 00:00:00
null

2
2022-01-24 21:33:10
null

3
2022-01-24 21:33:10
2033-03-03 00:00:00

3
2022-01-24 21:33:12
2004-04-04 00:00:00
id	ts	ts2
1	2022-01-01 00:00:00	null
2	2022-01-24 21:33:10	null
3	2022-01-24 21:33:10	2033-03-03 00:00:00
3	2022-01-24 21:33:12	2004-04-04 00:00:00