mysql如何将null参数传递给存储过程并在sql语句中对其进行测试

NOTE: Solution for this issue has been attached at the bottom. :)

注意:此问题的解决方案已附在底部。 :)

5.6.17 - MySQL Community Server (GPL) Using MySQL Console

5.6.17 - 使用MySQL控制台的MySQL社区服务器(GPL)

Trying to Test this procedure out in mysql console. It actually involves numerous fields which maybe searched against. Some values maybe defined as NULL.

尝试在mysql控制台中测试此过程。它实际上涉及许多可能被搜索的字段。某些值可能定义为NULL。

I was having troubles with the query with an ERROR #1064 which involved a NULL value at line 1.

我遇到了ERROR#1064的查询问题,它在第1行涉及NULL值。

Here is the query and it breaks when I added the @P1 IS NULL test. I saw this somewhere but cannot for the life of my find it again...

这是查询,当我添加@ P1 IS NULL测试时,它会中断。我在某个地方看到了这个,但是我的生命再也找不到了......

SET @p0='46,51,52,1317,1318,1319,1320,1322,1323'; 
SET @p1='500-000'; 
CALL `searchCount2`(@p0, @p1);

DROP PROCEDURE IF EXISTS `searchCount2`//

CREATE PROCEDURE `searchCount2`(
IN _dealerIds varchar(100),
IN _dealerPhoneNumber varchar(10)
)
BEGIN
        SET @query = CONCAT('SELECT count(cID) 
        FROM tblclassifieds c
        JOIN tblphotos p 
        ON c.cmd5val=p.cClassCode 
        WHERE p.cpMain=1 
        AND c.cMarkedInappropriate=0 
        AND c.cBlacklisted=0 
        AND c.cEndDate>NOW() 
        AND (cType=29) OR (c.cType=27 OR c.cType=28) 
        AND c.cCompanyId IN (',_dealerIds,')
        AND (("',_dealerPhoneNumber,'" is null) or (c.cPhoneNum="',_dealerPhoneNumber,'"));');
    --  SELECT @query;
     PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

Retested the Above using quotes.

使用引号重新测试上面的内容。

Here is the example I have which works before I added the @P1 IS NULL, but as I mentioned this query is far from complete. There are numerous parameters to search against.

以下是我添加@ P1 IS NULL之前的示例,但正如我所提到的,此查询远未完成。有许多参数可供搜索。

DROP PROCEDURE IF EXISTS `searchCount3`//

CREATE PROCEDURE `searchCount3`(
IN _dealerIds varchar(100),
IN _dealerPhoneNumber varchar(10)
)
BEGIN
        SET @query = CONCAT('SELECT count(cID) 
        FROM tblclassifieds c
        JOIN tblphotos p 
        ON c.cmd5val=p.cClassCode 
        WHERE p.cpMain=1 
        AND c.cMarkedInappropriate=0 
        AND c.cBlacklisted=0 
        AND c.cEndDate>NOW() 
        AND ((cType=29) OR (cType=27 OR cType=28))
        AND c.cCompanyId IN (',_dealerIds,')
        OR c.cPhoneNum=',_dealerPhoneNumber,';');
     -- SELECT @query;
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

SO what is my error with this NULL error? Is there another way I should implement this? How can I test it in MySQL?

那么这个NULL错误的错误是什么?我还有另一种方法可以实现吗?我怎样才能在MySQL中测试它?

Is Set @p1=NULL; valid?

设置为@ p1 = NULL;有效?

Please disregard the horrible naming convention.

请忽略可怕的命名惯例。

Thanks for any help. I have been struggling with this for too long.

谢谢你的帮助。我一直在努力解决这个问题太久了。

Here is a print off of the query before it executes, SELECT @query:

这是在执行之前打印查询,SELECT @query:

SELECT count(cID)
              FROM tblclassifieds c
              JOIN tblphotos p
              ON c.cmd5val=p.cClassCode
              WHERE p.cpMain=1
              AND c.cMarkedInappropriate=0
              AND c.cBlacklisted=0
              AND c.cEndDate>NOW()
              AND (cType=29)
              AND c.cCompanyId IN (46,51,52,1317,1318,1319,1320,1322,1323)
              OR (cType=27 OR cType=28)
              AND cCompanyId IN (46,51,52,1317,1318,1319,1320,1322,1323)
              AND ((579-7775 is null) or (c.cPhoneNum=579-7775)); 

I copy and paste this query into sql console and I get results. But Execute fails! Why is this so? Error #1064.

我将此查询复制并粘贴到sql控制台中,我得到了结果。但是执行失败了!为什么会这样?错误#1064。

SOLUTION:

I removed the parameter test for _dealerPhoneNumber IS NULL and replaced it with _dealerPhoneNumber = "". This has fixed the issue.

我删除了_dealerPhoneNumber IS NULL的参数test并将其替换为_dealerPhoneNumber =“”。这解决了这个问题。

DROP PROCEDURE IF EXISTS `searchCount2`//

CREATE PROCEDURE `searchCount2`(
IN _dealerIds varchar(100),
IN _dealerPhoneNumber varchar(10)
)
BEGIN
        SET @query = CONCAT('SELECT count(cID) 
        FROM tblclassifieds c
        JOIN tblphotos p 
        ON c.cmd5val=p.cClassCode 
        WHERE p.cpMain=1 
        AND c.cMarkedInappropriate=0 
        AND c.cBlacklisted=0 
        AND c.cEndDate>NOW() 
        AND ((cType=29) AND cCompanyId IN (',_dealerIds,')) 
        OR ((cType=27 OR cType=28) AND cCompanyId IN (',_dealerIds,')) 
        AND (("',_dealerPhoneNumber,'" = "") or (c.cPhoneNum="',_dealerPhoneNumber,'"));');
    --  SELECT @query;
     PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

1 个解决方案

#1

try to define the parameters as null on the creation.

尝试在创建时将参数定义为null。

CREATE PROCEDURE `searchCount3`(
IN _dealerIds varchar(100) = NULL,
IN _dealerPhoneNumber varchar(10) = NULL
)