给你一个我写的存储过程的例子,里面有你想要的。 DELIMITER $$DROP PROCEDURE IF EXISTS `sp_get_new_release`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_new_release`(IN var_str varchar(20), IN var_note_language char(5)) BEGIN DECLARE cnt int; DECLARE i int; SET @curs = var_str; SET @op = "'"; SET @cur_lang = 'en'; SET @full_version = ''; SET @sql = 'SELECT * FROM software_release a WHERE 1 = 1'; SELECT COUNT(*) FROM software_release WHERE STRCMP(note_language,var_note_language) = 0 INTO cnt; IF cnt != 0 THEN SET @sql = CONCAT(@sql,' AND STRCMP(a.note_language,',@op,var_note_language,@op,') = 0'); ELSE SET @sql = CONCAT(@sql,' AND STRCMP(a.note_language,',@op,@cur_lang,@op,') = 0'); END IF; loop1:LOOP SET i = LOCATE('.',@curs,1); SET @a = LEFT(@curs,i-1); SET @full_version = CONCAT(@full_version,REPEAT('0',(4 - LENGTH(@a))),@a); SET @curs = SUBSTR(@curs,i+1); IF INSTR(@curs,'.') = 0 THEN SET @a = @curs; SET @full_version = CONCAT(@full_version,REPEAT('0',(4 - LENGTH(@a))),@a); LEAVE loop1; END IF; END LOOP loop1; SET @sql = CONCAT(@sql,' AND full_version >= ',@op,@full_version,@op); PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1; END$$DELIMITER ;
DELIMITER $$DROP PROCEDURE IF EXISTS `sp_get_new_release`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_new_release`(IN var_str varchar(20),
IN var_note_language char(5))
BEGIN
DECLARE cnt int;
DECLARE i int;
SET @curs = var_str;
SET @op = "'";
SET @cur_lang = 'en';
SET @full_version = '';
SET @sql = 'SELECT * FROM software_release a WHERE 1 = 1';
SELECT COUNT(*) FROM software_release WHERE STRCMP(note_language,var_note_language) = 0 INTO cnt;
IF cnt != 0 THEN
SET @sql = CONCAT(@sql,' AND STRCMP(a.note_language,',@op,var_note_language,@op,') = 0');
ELSE
SET @sql = CONCAT(@sql,' AND STRCMP(a.note_language,',@op,@cur_lang,@op,') = 0');
END IF;
loop1:LOOP
SET i = LOCATE('.',@curs,1);
SET @a = LEFT(@curs,i-1);
SET @full_version = CONCAT(@full_version,REPEAT('0',(4 - LENGTH(@a))),@a);
SET @curs = SUBSTR(@curs,i+1);
IF INSTR(@curs,'.') = 0 THEN
SET @a = @curs;
SET @full_version = CONCAT(@full_version,REPEAT('0',(4 - LENGTH(@a))),@a);
LEAVE loop1;
END IF;
END LOOP loop1;
SET @sql = CONCAT(@sql,' AND full_version >= ',@op,@full_version,@op);
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END$$DELIMITER ;