首页 文章

Mysql错误：错误代码：1292 . 截断不正确的DOUBLE值

提问于
浏览 次
0

我在mySQL中执行此存储过程时收到此错误：

错误代码：1292 . 截断错误的DOUBLE值：'WHERE s.name ='确认'AND bp.id =''

DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test (province VARCHAR(100), country VARCHAR(100), departureDate TIMESTAMP, arrivalDate TIMESTAMP, people INTEGER) 
BEGIN 
 DECLARE selectClause VARCHAR(500) DEFAULT "SELECT DISTINCT b.id, b.boat_model_id, b.port_id, b.address_id, b.maximum_discount, 
                 b.discounts_have_subtotals, b.skipper_type_id, b.length, b.people_capacity_id,
                 b.simple_cabin_id, b.double_cabin_id, b.bathroom_id, year_id ";         
 DECLARE fromClause VARCHAR(500) DEFAULT "FROM states s, boat_people bp, boat_prices bprd, boat_prices bpra, boats b ";
 DECLARE whereClause VARCHAR(1000) DEFAULT "WHERE s.name = 'Confirmed' 
 AND bp.id = " + people + "
 AND b.state_id = s.id 
 AND b.id NOT IN (SELECT bc.boat_id 
                  FROM boat_calendars bc 
                  WHERE (date(bc.since) <= " + departureDate + " AND date(bc.until) >= " + departureDate + ")   
                  OR (date(bc.since) <= " + arrivalDate + " AND date(bc.until) >= " + arrivalDate + "))
 AND bprd.boat_id = b.id 
 AND date(bprd.since) <= " + departureDate + " AND date(bprd.until) >= " + departureDate + "
 AND bpra.boat_id = b.id 
 AND date(bpra.since) <= " + arrivalDate + " AND date(bpra.until) >= " + arrivalDate + "
 AND truncate(((bprd.weekly_price + bpra.weekly_price)/2), 2)  > 0";
 SET @queryClause = concat(selectClause, fromClause, whereClause);
 PREPARE stmt FROM @queryClause;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END // 
CALL test('Mallorca (Islas Baleares)', 'España', '2016-03-02 16:02:12', '2016-03-02 16:02:12', 1);

表boat_people.id是INT（11） .

mysql

1 回答

  • 0

    你有“DECLARE”变量有什么特别的原因吗？您可以直接编写“SELECT”查询 . 例如，

    CREATE PROCEDURE `myprocedure`(myparam VARCHAR(100))
BEGIN
   SELECT * from mytable where myfield = myparam;
END

CALL myprocedure('value');

    编辑：基本上不适用于字符串连接，

    CREATE PROCEDURE `myprocedure`(myparam VARCHAR(100))
BEGIN
    DECLARE queryClause VARCHAR(1000) DEFAULT CONCAT('select * from mytable where myfield = "', myparam, '"');
    SET @queryA = queryClause;
    PREPARE stmt FROM @queryA;
    EXECUTE stmt;
END

    你不需要“DECLARE” . 您可以按如下方式使用SET：

    SET @queryA = CONCAT('select * from mytable where myfield = "', myparam, '"');

    或多个SET如下：

    SET @queryA = CONCAT('select * from mytable where myfield = "', myparam, '"');
SET @queryB = ' and myfield is not null ';
SET @queryC = CONCAT(@queryA, @queryB);
    回复于

相关问题