首页 文章

程序中MySQL错误“截断了错误的DOUBLE值”

提问于
浏览
0

我的手术有问题 . 我尝试从sales表中获取值并使用它们进行查询 . 过程如下所示:

DROP PROCEDURE IF EXISTS turnover;

DELIMITER $$

CREATE PROCEDURE turnover()
BEGIN
    DECLARE col INT;
    DECLARE q TEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE m TEXT;
    SET col = (SELECT count(DISTINCT article) FROM sales);
    SET q = "SELECT article, ";
    WHILE i < co DO
        SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
        SET q = q + "SUM(IF(month=" + m + ",value,NULL)) AS " + m;
        IF i < (col - 1) THEN
            SET q = q + ", ";
        END IF;
        SET i = i + 1;
    END WHILE;
    SET q = q + " FROM sales GROUP BY article";
    EXECUTE q;
END$$

DELIMITER ;

CALL turnover();

我收到错误:

错误代码:1292 . 截断错误的DOUBLE值:',value,NULL))AS'

我怎么能让它有效?

谢谢 .

2 回答

  • 1

    col 问题已修复或假设如下 .

    CREATE SCHEMA safe_Tuesday_01; -- safe sandbox
    USE safe_Tuesday_01;    -- DO the work in this db to test it
    
    -- a fake table, we need something
    create table sales 
    (   article varchar (100) not null,
        month int not null
    );
    

    Step 1 ,找出字符串的样子:

    DROP PROCEDURE IF EXISTS turnover;
    DELIMITER $$
    CREATE PROCEDURE turnover()
    BEGIN
        DECLARE col INT;
        DECLARE q TEXT;
        DECLARE i INT DEFAULT 0;
        DECLARE m TEXT;
        SET col = (SELECT count(DISTINCT article) FROM sales);
        SET q = "SELECT article, ";
        WHILE i < col DO
            SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
            SET q = CONCAT(q,"SUM(IF(month=" + m + ",value,NULL)) AS ", m);
            IF i < (col - 1) THEN
                SET q = q + ", ";
            END IF;
            SET i = i + 1;
        END WHILE;
        SET q = CONCAT(q," FROM sales GROUP BY article");
        select q;
        -- EXECUTE q; -- No no no this is wrong anyway
    END$$
    DELIMITER ;
    
    CALL turnover();
    

    SELECT文章,FROM销售GROUP BY文章

    以上 SELECT 看起来并不那么热 . 在第1步中重复修复逻辑以修复该字符串 .

    Step 2 ,当您修复上面的代码时,请在下面进行操作 . 注意,目前,它没有修复 . 所以,再次,上面这样做 .

    但是在下面,使用一个你不是的合适的PREPARED STATEMENT .

    DROP PROCEDURE IF EXISTS turnover;
    DELIMITER $$
    CREATE PROCEDURE turnover()
    BEGIN
        DECLARE col INT;
        DECLARE q TEXT;
        DECLARE i INT DEFAULT 0;
        DECLARE m TEXT;
        SET col = (SELECT count(DISTINCT article) FROM sales);
        SET q = "SELECT article, ";
        WHILE i < col DO
            SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
            SET q = CONCAT(q,"SUM(IF(month=" + m + ",value,NULL)) AS ", m);
            IF i < (col - 1) THEN
                SET q = q + ", ";
            END IF;
            SET i = i + 1;
        END WHILE;
        SET q = CONCAT(q," FROM sales GROUP BY article");
        -- select q;
        SET @theSQL=q;
        PREPARE stmt1 FROM @theSQL;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END$$
    DELIMITER ;
    

    完成后,

    DROP SCHEMA safe_Tuesday_01; -- clean up, poof, sandbox is gone
    

    CONCAT是你的朋友 . 你错过了这一步 . 重要的是PREPARE对用户变量(带有 @ 符号)而不是局部变量(来自 DECLARE )起作用,否则它会爆炸 . 所以我用 @theSQL 修正了上面的内容

    再次,请参阅MySQL手册页PREPARE Syntax . 让你的字符串正确是很重要的 . 这是第1步的要点 . 只有这样你才能继续进行第2步并使用它 .

  • 0

    SELECT DISTINCT month FROM sales 没有返回任何内容时会发生这种情况 . 在下一行,查询片段生成为 SUM(IF(month=,value,NULL)) AS ,当然,那里有一个错误(可能MySQL没有产生正确的错误消息,但这是错误的地方) .

    并且错误的原因是 WHILE 行将 i 与未知变量 co 进行比较 . 它可能应该是:

    WHILE i < col DO
    

    但它不能解决问题,因为 colarticle 的不同值的数量,并且您在 month 的不同值上从1迭代到 col . 最有可能的是它们的数量不同,如果文章的数量更大,那么错误将再次发生 .

相关问题