MySQL before starting for INSERT ON DUPLICATE KEY UPDATE - does the manual seem to be wrong?

Question

I work with MySQL 5.7:

D:\>mysql --version
mysql  Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)

According to the manual , the behavior of the BEFORE INSERT trigger should be:

a trigger is activated BEFORE INSERTING for each row, followed either by AFTER INSERT or as BEFORE UPDATING and AFTER UPDATING triggers, depending on whether there was a duplicate key for the row.

I suppose this means that BEFORE THE INSERT is executed regardless of whether the match matches it, while AFTER INSERT and UPDATE triggers depend on whether there is a conflict. This SO repeats the same thing. However, I do not see this behavior. Here is what I did:

create table testtable (
  id integer primary key auto_increment,
  nickname varchar(40),    -- this is the natural key to be unique indexed
  name varchar(40),
  uuid varchar(36));       -- this is what I want to assign in the trigger

alter table testtable add unique index testtable_ux (nickname);
create trigger testtable_uid before insert on testtable for each row set
  new.uuid=uuid();

-- get some data
insert into testtable (nickname, name) values ('bob', 'Robert'), 
  ('fred', 'Frederick'), ('cha', 'Chauncey');
select * from testtable;

1   bob   Robert      06fb18be-f87e-11e6-8e6f-0060737a7c01
2   fred  Frederick   06fb1a5d-f87e-11e6-8e6f-0060737a7c01
3   cha   Chauncey    06fb1aec-f87e-11e6-8e6f-0060737a7c01

- UUID. , , , , UUID - ? :

insert into testtable (nickname, name) values ('fred', 'Alfred') 
  on duplicate key update name='Alfred';
3   88  16:39:32 ... 2 row(s) affected  0.032 sec

select * from testtable;
1   bob   Robert      06fb18be-f87e-11e6-8e6f-0060737a7c01
2   fred  Alfred      06fb1a5d-f87e-11e6-8e6f-0060737a7c01
3   cha   Chauncey    06fb1aec-f87e-11e6-8e6f-0060737a7c01

Hm, . UUID:

2   fred  Frederick   06fb1a5d-f87e-11e6-8e6f-0060737a7c01
2   fred  Alfred      06fb1a5d-f87e-11e6-8e6f-0060737a7c01

, , UUID . .

, ; , , upsert , , , , . , , , . ?

Answer 1

BEFORE INSERT . . ​​ , :

drop table if exists testtable;
create table testtable (
  id integer primary key auto_increment,
  nickname varchar(40),    -- this is the natural key to be unique indexed
  name varchar(40),
  uuid varchar(36));       -- this is what I want to assign in the trigger

alter table testtable add unique index testtable_ux (nickname);

drop table if exists testlog;
create table testlog (
  log_id int primary key auto_increment,
  nickname varchar(40),
  name varchar(40),
  uuid varchar(36)
);

drop trigger if exists testtable_uid;
delimiter //
create trigger testtable_uid before insert on testtable for each row
begin
  set new.uuid=uuid();
  insert into testlog (nickname, name, uuid) values (new.nickname, new.name, new.uuid);
end //
delimiter ;

insert into testtable (nickname, name) values ('bob', 'Robert'), 
  ('fred', 'Frederick'), ('cha', 'Chauncey');
select * from testtable;

insert into testtable (nickname, name) values ('fred', 'Alfred') 
  on duplicate key update name='Alfred';

select * from testtable;
select * from testlog;

, testlog 4 . "fred", "Alfred" UUID. , . , UUID. UUID testtable.uuid. , .

, UUID ( ) ON DUPLICATE, values(uuid):

insert into testtable (nickname, name) values ('fred', 'Alfred') 
  on duplicate key update 
    name='Alfred',
    `uuid`=values(`uuid`);