DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_information`$$
CREATE
PROCEDURE `vehicle`.`sp_information`(IN limitted BOOLEAN,IN started INT(255),IN size INT(255))
BEGIN IF limitted=TRUE THEN
BEGIN
SELECT
`tab_user`.`id` AS `id`,
`tab_user`.`name` AS `uname`,
`tab_user`.`age` AS `age`,
`tab_vehicle`.`color` AS `color`
FROM (`tab_user`
JOIN `tab_vehicle`)
WHERE (`tab_user`.`id` = `tab_vehicle`.`id`) LIMIT started,size;
END;
ELSE
BEGIN
SELECT
`tab_user`.`id` AS `id`,
`tab_user`.`name` AS `uname`,
`tab_user`.`age` AS `age`,
`tab_vehicle`.`color` AS `color`
FROM (`tab_user`
JOIN `tab_vehicle`)
WHERE (`tab_user`.`id` = `tab_vehicle`.`id`);
END;
END IF;
END$$DELIMITER ; 如你所见,这里写得很浪费,区别只要是后面分页的选择(MYSQL的,这里)
哪位前辈可以指点一二,借条大腿抱一抱?
谢啦!
DROP PROCEDURE IF EXISTS `sp_information`$$
CREATE
PROCEDURE `vehicle`.`sp_information`(IN limitted BOOLEAN,IN started INT(255),IN size INT(255))
BEGIN IF limitted=TRUE THEN
BEGIN
SELECT
`tab_user`.`id` AS `id`,
`tab_user`.`name` AS `uname`,
`tab_user`.`age` AS `age`,
`tab_vehicle`.`color` AS `color`
FROM (`tab_user`
JOIN `tab_vehicle`)
WHERE (`tab_user`.`id` = `tab_vehicle`.`id`) LIMIT started,size;
END;
ELSE
BEGIN
SELECT
`tab_user`.`id` AS `id`,
`tab_user`.`name` AS `uname`,
`tab_user`.`age` AS `age`,
`tab_vehicle`.`color` AS `color`
FROM (`tab_user`
JOIN `tab_vehicle`)
WHERE (`tab_user`.`id` = `tab_vehicle`.`id`);
END;
END IF;
END$$DELIMITER ; 如你所见,这里写得很浪费,区别只要是后面分页的选择(MYSQL的,这里)
哪位前辈可以指点一二,借条大腿抱一抱?
谢啦!
解决方案 »
- mysql varchar not null 还能插入空字符串
- 急,毕业设计,数据库自动更新问题
- 请教一个时间跨度的问题
- 字符串编号自动生成问题
- 我用PostgreSQL数据库,执行一个insert错误后,2个半小时的时间内,不能再对这个表进行insert操作。2个半小时以后,insert的其他语句才能执行。为什么?
- 关于Mysql一个数据库分布在多个物理磁盘的问题
- windows2000中装的mysql不能建数据库?奇怪!
- Mysql load data infile 变量使用问题?
- 用vs2012 C#编写mysql数据库软件,如何将查询到的值赋值给程序里的变量
- sql大数据自关联查询优化
- mysql数据库两个问题
- [求助]文本数据导入mysql数据库
你可以用 set @sql = "select .... limit 10,100"
生成需要的SQL语句然后 用 mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;
这类方法执行。
DECLARE sql_value VARCHAR(255) DEFAULT '';
SET sql_value='LIMIT started,size';
BEGIN SELECT `tab_user`.`id` AS `id`, `tab_user`.`name` AS `uname`, `tab_user`.`age` AS `age`, `tab_vehicle`.`color` AS `color` FROM (`tab_user` JOIN `tab_vehicle`) WHERE (`tab_user`.`id` = `tab_vehicle`.`id`) sql_value; END;
'LIMIT started,size'用变量sql_value来替代
但就是怎么把这个变量添加到select语句where条件后面,我尝试用java的格式:+sql_value,但这种写法mysql不支持,我想怎么才能加进去
a='10'
b='10'
sql='select * from tt limit '+a+','+b
再执行SQL语句