/**
* 查询俱协信息
*/
DROP PROCEDURE IF EXISTS `get_club_list`;
DELIMITER $$
CREATE PROCEDURE `get_club_list`(
-- 页码
IN i_page_num int unsigned,
-- 一页数量
IN i_page_size int unsigned
)
BEGIN
-- 定义变量
DECLARE v_num int unsigned DEFAULT 0;
SET v_num = i_page_num*i_page_size;
SELECT * FROM (
SELECT
`t`.*,
`club_area`.`name` AS `area_name`,
`club_city`.`name` AS `city_name`
FROM (
SELECT * FROM `club_record` WHERE `status` = '' ORDER BY `createtime` DESC LIMIT v_num,i_page_size
) AS `t` LEFT JOIN `club_area` ON (
`t`.`area_id` = `club_area`.`id`
) LEFT JOIN `club_city` ON (
`t`.`city_id` = `club_city`.`id`
)
) AS `tt` ORDER BY `createtime` DESC;
END $$
DELIMITER ; CALL get_club_list(0,10);
/**
* 测试-删除
*/
DROP PROCEDURE IF EXISTS `test_del`;
DELIMITER $$
CREATE PROCEDURE `test_del`(
-- 删除一组id
IN i_ids varchar(255)
)
BEGIN
-- 定义变量
DECLARE v_i int unsigned DEFAULT 1;
DECLARE v_num int unsigned DEFAULT 0;
DECLARE v_id int unsigned DEFAULT 0;
SET v_num = LENGTH(i_ids) - LENGTH( REPLACE(i_ids,',','') ) + 1; WHILE v_i <= v_num DO
SET v_id = SUBSTRING_INDEX( SUBSTRING_INDEX(i_ids,',',v_i),',',-1 );
DELETE FROM `club_area_copy` WHERE `id` = v_id;
DELETE FROM `club_city_copy` WHERE `area_id` = v_id;
-- SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(i_ids,',',v_i),',',-1 );
SET v_i = v_i+1;
END WHILE;
END $$
DELIMITER ;
MYSQL函数：
1. SUBSTRING_INDEX(str, delim, count)
str: 要处理的字符串
delim: 分割符
count: 计数 如果为正数，则从左开始数，如果为负数，则从右开始数
-- SET @str = 'www.baidu.com'; SELECT substring_index(@str,'.',1); # www SELECT substring_index(@str,'.',2); # www.baidu SELECT substring_index(@str,'.',-1); # com SELECT substring_index(@str,'.',-2); # baidu.com SELECT substring_index( substring_index(@str,'.',-2),'.',1 ); # baidu SELECT LENGTH(@str); # 13 SELECT LENGTH( REPLACE(@str,'.','') ); # 11 -- 得到结果数
SELECT LENGTH(@str) - LENGTH( REPLACE(@str,'.','') ) + 1; # 3