MySQL 8.0

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

create table original as 
    select 0 as id, 'xy' as name, 'a' as medium union all
    select 1 as id, 'xz' as name, 'b' as medium union all
    select 2 as id, 'yz' as name, 'a' as medium 
create table table2 (
    id int primary key,
    name varchar(255) unique
);

insert into table2 
    select row_number() over (order by medium), medium
    from original
    group by medium;

create table table1 as 
    select o.id, o.name, t2.id as medium
    from original o join
         table2 t2
         on t2.name = o.medium;
         

     
alter table table2 modify column id int auto_increment;
select *
from table2


id
name


1
a

2
b


select *
from table1


id
name
medium


0
xy
1

1
xz
2

2
yz
1


-- demonstrate that autoincrement works
insert into table2 (name) values ('newname')
select *
from table2


id
name


1
a

2
b

3
newname