The operations developed today need to insert association records in one many-to-many table. The previous one and multiple operations have no difference in my implementation functions, but multiple duplicate records seem awkward, after all, it's still not good, So I thought about it.
Previously, the most naive idea was to first query the records to be inserted. If the result set size is greater than 0, the data already exists and no data insertion operation is performed. Otherwise, insert ......, Today, I understand that an SQL statement can be used to solve the problem. The method of using MySQL dual table is as follows:
Insert into users_roles
(Userid, roleid)
SELECT 'userid _ x', 'Role ID _ x'
FROM dual
Where not exists (
SELECT * FROM users_roles
WHERE userid = 'userid _ x'
AND roleid = 'roleid _ x'
);
Users_roles is the table for data insertion, and userid_x and roleid_x are the records to be inserted.
The dual table in MySQL is described as follows:
Table-'dual': a dummy table in mysql
Description of dual tables in mysql:
You are allowed to specify DUAL as a dummy table name in situations where no tables are referenced:
Mysql> SELECT 1 + 1 from dual;
-> 2
DUAL is purely for the convenience of people who require that all SELECT statements showould have FROM and possibly other clses. MySQL may ignore the clses. MySQL does not require from dual if no tables are referenced.
Author "onedada"