[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE t INT DEFAULT 0; DECLARE s VARCHAR(1000) ; SET s=CONCAT_WS('' at line 8
将DECLARE t INT DEFAULT 0; 这句放到begin后面
DELIMITER $$ DROP PROCEDURE IF EXISTS get_rands$$ CREATE PROCEDURE get_rands(IN cnt INT,IN protype VARCHAR(1000))BEGIN DECLARE t INT DEFAULT 0; -- MYSQL declare 必须在首部 DROP TEMPORARY TABLE IF EXISTS rands; CREATE TEMPORARY TABLE rands ( rand_id BIGINT(20), UNIQUE KEY(rand_id)); SET @psql=CONCAT_WS(',','INSERT IGNORE INTO rands SELECT r1.id FROM comma_picture AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM comma_picture where name in (',protype,')) AS id) AS r2 WHERE r1.name in(',protype,') and r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1') ;
loop_me: LOOP IF t = cnt THEN LEAVE loop_me; END IF; PREPARE s1 FROM @psql; EXECUTE s1; SELECT COUNT(rand_id) INTO t FROM rands;
END LOOP loop_me; END$$ DELIMITER ;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE t INT DEFAULT 0; DECLARE s VARCHAR(1000) ; SET s=CONCAT_WS('' at line 8yes,thanks,不过,还是有错
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE t INT DEFAULT 0; DECLARE s VARCHAR(1000) ; SET s=CONCAT_WS('' at line 8yes,thanks,不过,还是有错DELIMITER $$ DROP PROCEDURE IF EXISTS get_rands$$ CREATE PROCEDURE get_rands(IN cnt INT,IN protype VARCHAR(1000))BEGIN DECLARE t INT DEFAULT 0; DECLARE s VARCHAR(1000) ; DROP TEMPORARY TABLE IF EXISTS rands; CREATE TEMPORARY TABLE rands ( rand_id BIGINT(20), UNIQUE KEY(rand_id)); SET s=CONCAT_WS(',','INSERT IGNORE INTO `rands` SELECT r1.id FROM `comma_picture` AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM `comma_picture` where `name` in (',protype,'))) AS id) AS r2 WHERE r1.name in(',protype,') and r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1') ;
loop_me: LOOP IF t = cnt THEN LEAVE loop_me; END IF; SET @psql=s PREPARE s1 FROM @psql; EXECUTE s1; SELECT COUNT(rand_id) INTO t FROM rands;
END LOOP loop_me; END$$ DELIMITER ; 错误信息:[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PREPARE s1 FROM @psql; EXECUTE s1; SELECT COUNT(rand_id) INTO t FRO' at line 21
DROP PROCEDURE IF EXISTS get_rands$$
CREATE PROCEDURE get_rands(IN cnt INT,IN protype VARCHAR(1000))BEGIN DECLARE t INT DEFAULT 0; -- MYSQL declare 必须在首部 DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id BIGINT(20), UNIQUE KEY(rand_id));
SET @psql=CONCAT_WS(',','INSERT IGNORE INTO rands SELECT r1.id FROM comma_picture AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM comma_picture where name in (',protype,')) AS id) AS r2 WHERE r1.name in(',protype,') and r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1') ;
loop_me: LOOP IF t = cnt THEN
LEAVE loop_me;
END IF; PREPARE s1 FROM @psql;
EXECUTE s1; SELECT COUNT(rand_id) INTO t FROM rands;
END LOOP loop_me;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS get_rands$$
CREATE PROCEDURE get_rands(IN cnt INT,IN protype VARCHAR(1000))BEGIN DECLARE t INT DEFAULT 0;
DECLARE s VARCHAR(1000) ; DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id BIGINT(20), UNIQUE KEY(rand_id));
SET s=CONCAT_WS(',','INSERT IGNORE INTO `rands` SELECT r1.id FROM `comma_picture` AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM `comma_picture` where `name` in (',protype,'))) AS id) AS r2 WHERE r1.name in(',protype,') and r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1') ;
loop_me: LOOP IF t = cnt THEN
LEAVE loop_me;
END IF; SET @psql=s
PREPARE s1 FROM @psql;
EXECUTE s1;
SELECT COUNT(rand_id) INTO t FROM rands;
END LOOP loop_me;
END$$
DELIMITER ;
错误信息:[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PREPARE s1 FROM @psql;
EXECUTE s1;
SELECT COUNT(rand_id) INTO t FRO' at line 21
恩,找到了,用concat()就够啦,thanks ,问题解决。