MySQL INSERT WHERE变量不重复

Question

我在DECLARE、CALL函数和IF设置方面遇到了一些问题。到目前为止，我拥有的是：

CREATE PROCEDURE number_of_projects(project_name VARCHAR)
BEGIN
    DECLARE variable INT;
    SET variable = 4;
    SELECT variable;
    SELECT count(project_id) FROM atm_projects WHERE project_name = variable;
END
IF number_of_projects("PROJECT NAME") = 0
    THEN
    INSERT INTO atm_projects(project_id,project_name,added_from_mti)
    VALUES (project_id,'PROJECT NAME',1)
ENDIF

主要目标是在project_name不重复的表中插入一行。

我可以将project_name更改为UNIQUE密钥，但请告诉我我的代码出了什么问题，以及我如何解决这个问题？

我需要学习PROCEDURE、CALL、procedure、IF是如何工作的。

Answer 1

try:

CREATE PROCEDURE number_of_projects(project_name VARCHAR(255))
BEGIN
    DECLARE var_project_no INT;
    SET var_project_no = 0;
    SELECT var_project_no;
    SELECT count(project_id) INTO var_project_no FROM atm_projects WHERE var_project_name = 4;

    IF var_project_no = 0
    THEN
        INSERT INTO atm_projects(project_id,project_name,added_from_mti)
        VALUES (project_id,'PROJECT NAME',1)
    END IF;
END;

Answer 2

您已经向varchar声明了project_name，但没有指定长度。将其更改为varchar（100）

Answer 3

CREATE PROCEDURE number_of_projects(project_name VARCHAR(255))
BEGIN
 if NOT  exists(SELECT Top 1 1  FROM atm_projects WHERE var_project_name = 4)
 BEGIN 
   INSERT INTO atm_projects(project_id,project_name,added_from_mti)
   Select project_id,project_name ,1
 END
END;