我们正常的mysql插入数据语句
INSERT INTO `guild_nakadai`.`admin_role_permission` (`role_id`, `permission_id`, `type`) VALUES ( (SELECT id FROM `guild_nakadai`.`admin_roles` WHERE name="员工"), (SELECT id FROM `guild_nakadai`.`admin_permissions` WHERE name="基础数据汇总"), 3 );
他有一个缺点,当我们不小心执行了几次时,会插入多条相同的垃圾数据
加个前提
要在执行这个 MySQL 语句时添加一个前提条件,即只有表里没有相同数据才进行添加操作,你可以使用 INSERT IGNORE INTO 的方式来实现。案例:
INSERT IGNORE INTO `guild_nakadai`.`admin_role_permission` (`role_id`, `permission_id`, `type`) SELECT (SELECT id FROM `guild_nakadai`.`admin_roles` WHERE name="员工"), (SELECT id FROM `guild_nakadai`.`admin_permissions` WHERE name="基础数据汇总"), 3 FROM dual WHERE NOT EXISTS ( SELECT 1 FROM `guild_nakadai`.`admin_role_permission` WHERE role_id = (SELECT id FROM `guild_nakadai`.`admin_roles` WHERE name="员工") AND permission_id = (SELECT id FROM `guild_nakadai`.`admin_permissions` WHERE name="基础数据汇总") AND type = 3 );