Acceptance by the local public is the key determinant for the successful implementation of NIMBY (Not-In-My-Backyard) infrastructures and may be shaped in different ways among different infrastructure types. Based on social cognitive theory (SCT), this study clarifies the specific mechanism shaping local public acceptance of NIMBY facilities with two types of hazardous effects (i.e., pollution and psychological exclusion) using a large-scale questionnaire survey and structural equation model. The results reveal that, firstly, SCT provides a solid theoretical basis for exploring the mechanism under the joint action of environmental and personal factors. Secondly, it is verified that self-efficacy indirectly predicts local public acceptance by influencing perceived risk. The effect of the positive affect tag is mediated by perceived risk in shaping acceptance of polluting facilities but not of psychologically excluded facilities. In general, people tend to have a lower perceived risk, higher perceived benefit, stronger sense of self-efficacy, and more positive attitude when faced with the siting of psychologically excluded NIMBY facilities over polluting ones. These findings are helpful for planning and decision-making of NIMBY facilities with different types of hazardous impacts, reducing NIMBY conflicts and promoting the construction of NIMBY infrastructures. Furthermore, it contributes to the achievement of Sustainable Development Goal (SDG) 16 (promoting peaceful and inclusive societies for sustainable development) and (SDG) 11 (building inclusive, safe, resilient and sustainable cities and human settlements).