MySql - adding N rows by condition

Question

MySQL

Good afternoon.
I can’t figure out how to make a query in MySql - I just don’t have enough experience.

Condition: there are three tables
1. Characters — char(id,name)
2. Shots — shot(id,name)
3. Using characters in shots char_use(shot_id, char_id

) some kind of character. That is, we indicate pairs of character id and location that need to be linked together.

If now you need to add one character for all shots:
insert into char_use (shot_id, char_id)
select id temp1, 9 temp2 // 9 is the id of the character that we add
from shot
where 1

But if this pair with a character was already installed on some shot, then we already have two entries in the table for one pair of character: shot.

Task:
Make a request that does not just add one character to all shots, but takes into account the fact that some shots already have such a character assigned. Roughly speaking, it is necessary to make sure that the shot record: the character is unique for the couple - not repeated many times.

Answer 1

In link tables, they usually make a primary key for both fields.
And insert ignore

Answer 2

While we were quietly discussing composite keys here, for fun, I will offer the second option:
INSERT INTO char_use (shot_id, char_id)
SELECT shot.id, 9
FROM shot
LEFT JOIN char_use ON shot.shot_id = char_use.shot_id
WHERE char_use.shot_id IS NULL
AND 1 = 1
This query will not even attempt to insert a non-unique record.