MySQL 8.0

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

SET SESSION sql_mode := '';

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  val INT
) AUTO_INCREMENT = 123;
CREATE TRIGGER fail_explicit_id
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  IF NEW.id <> 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Explicit ''id'' value is not allowed.';
  END IF;
END
INSERT INTO test (val) VALUES (111);
INSERT INTO test VALUES (NULL, 222);
INSERT INTO test VALUES (0, 333);
INSERT INTO test VALUES (DEFAULT, 444);
INSERT INTO test VALUES (456, 555);

Explicit 'id' value is not allowed.


SET SESSION sql_mode := CONCAT_WS(',', @@sql_mode, 'NO_AUTO_VALUE_ON_ZERO');
SELECT @@sql_mode;
INSERT INTO test VALUES (0, 666);


@@sql_mode


NO_AUTO_VALUE_ON_ZERO


INSERT INTO test VALUES (0, 777);

Duplicate entry '0' for key 'test.PRIMARY'


SELECT * FROM test;


id
val


0
666

123
111

124
222

125
333

126
444