临时表相对于工作表而言,用 CREATE TABLE #TMP OR CREATE TEMPORARY TABLE #TMP 均可
CREATE TABLE #TMP 产生的普通表,会有不同connection相互之间的冲突问题。比如你的网页被同一用户多次刷新。用临时表则比较简单。你的代码可以直接。CREATE PROCEDURE SW() BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS #TMP LIKE old_tbl_name TEST_TABLE ; INSERT INTO #TMP EXEC SA() END;
DELIMITER $$CREATE PROCEDURE SW(in cs1 int,in cs2 int) BEGIN DROP TEMPORARy TABLE IF EXISTS TMP; set @ff=concat('CREATE TEMPORARy TABLE TMP', ' as SELECT * FROM TEST_TABLE LIMIt ',cs1,',',cs2); select @ff; prepare dd from @ff; execute dd; SELECT * FROM TMP a LEFT JOIN TEST_TABLE_A b on a.id=b.id; DROP TEMPORARy TABLE TMP; END$$DELIMITER ;
被调用者SA
CREATE PROCEDURE SA()
BEGIN
SELECT * FROM TEST_TABLE LIMIT ?,?//返回100条记录
END;调用者SW
CREATE PROCEDURE SW()
BEGIN
CREATE TABLE #TMP(字段列表)
INSERT INTO #TMP EXEC SA()
SELECT FROM #TMP LEFT JOIN TEST_TABLE_A
DROP TABLE #TMP
END;
以上过程是不是可以再优化呢?
CREATE TABLE #TMP(字段列表)
as SELECT * FROM TEST_TABLE LIMI cs1,cs2
一个SP就可以解决了嘛
CREATE TABLE #TMP
OR
CREATE TEMPORARY TABLE #TMP
均可
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS #TMP LIKE old_tbl_name TEST_TABLE ;
INSERT INTO #TMP EXEC SA()
END;
BEGIN
DROP TEMPORARy TABLE IF EXISTS TMP;
set @ff=concat('CREATE TEMPORARy TABLE TMP',
' as SELECT * FROM TEST_TABLE LIMIt ',cs1,',',cs2);
select @ff;
prepare dd from @ff;
execute dd;
SELECT * FROM TMP a LEFT JOIN TEST_TABLE_A b on a.id=b.id;
DROP TEMPORARy TABLE TMP;
END$$DELIMITER ;
还有,调用在存储过程中调用存储过程是用EXEC吗?MYSQL数据库。
另外,的确是在一个连接内的多次循环调用的。
NO,CALL 存储过程