由于我希望sphinx能把单表的数据分成多个索引以提升多核性能
需要计算出某表的N分之X的最小最大ID，稍微学习了mysql存储过程
以下为实现SQL：
DELIMITER // CREATE PROCEDURE `sph_distribute`(IN `tabname` VARCHAR(50), IN `idname` VARCHAR(50), IN `num` INT, IN `pos` INT) BEGIN SET @SQL = CONCAT('SELECT CEIL(COUNT(*)/', num, ')+0 INTO @step FROM `', tabname, '`'); PREPARE stmt1 FROM @SQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; SET @SQL = CONCAT('SELECT `', idname, '` INTO @minid FROM `', tabname, '` ORDER BY `', idname, '` ASC LIMIT ', FLOOR((pos - 1) * @step), ',1'); PREPARE stmt1 FROM @SQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; IF pos = num THEN SET @SQL = CONCAT('SELECT MAX(`', idname, '`) INTO @maxid FROM `', tabname, '`'); PREPARE stmt1 FROM @SQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; ELSE SET @SQL = CONCAT('SELECT `', idname, '` INTO @maxid FROM `', tabname, '` ORDER BY `', idname, '` ASC LIMIT ', FLOOR(pos * @step - 1), ',1'); PREPARE stmt1 FROM @SQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END IF; SELECT @minid, @maxid, @step; END;// DELIMITER ;
调用：
CALL sph_distribute('表名', 'id字段名', 分成多少份, 第几份);
例子：
CALL sph_distribute('table', 'id', 5, 3);
一些MYSQL不常见的写法：
1、DELIMITER，用于定义SQL结束符号；
2、SELECT …… \G，将每个字段一行显示;
3、PROCEDURE中，变量当表名只能用PREPAGE处理，里面的赋值变量，只支持全局变量（即 @var）
4、对整数的变量整乘，会自动转换为浮点，这点我很蛋疼，可以用FLOOR再转成整数
If you enjoyed this article, please consider sharing it!
