MySQL MySQL如何从[存储过程]插入[临时表]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/687102/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL How to INSERT INTO [temp table] FROM [Stored Procedure]
提问by Brian Fisher
This is very similar to question 653714, but for MySQL instead of SQL Server.
这与问题653714非常相似，但适用于 MySQL 而不是 SQL Server。
Basically, I have a complicated select that is the basis for several stored procedures. I would like to share the code across the stored procedures, however, I'm not sure how to do this. One way I could do this is by making the shared select a stored procedure and then calling that stored procedure from the other ones. I can't figure out how to work with the result set of the nested stored procedure. If I could put them in a temp table I could use the results effectively, but I can't figure out how to get them in a temp table. For example, this does not work:
基本上，我有一个复杂的选择，它是几个存储过程的基础。我想在存储过程中共享代码，但是，我不确定如何执行此操作。我可以做到这一点的一种方法是让共享选择一个存储过程，然后从其他存储过程调用该存储过程。我不知道如何处理嵌套存储过程的结果集。如果我可以将它们放在临时表中，我可以有效地使用结果，但我无法弄清楚如何将它们放入临时表中。例如，这不起作用：
CREATE TEMPORARY TABLE tmp EXEC nested_sp();
采纳答案by St. John Johnson
The problem is, Stored Procedures don't really return output directly. They can execute select statements inside the script, but have no return value.
问题是，存储过程并没有真正直接返回输出。它们可以在脚本内执行 select 语句，但没有返回值。
MySQL calls stored procedures via CALL StoredProcedureName();And you cannot direct that output to anything, as they don't return anything(unlike a function).
MySQL 通过调用存储过程CALL StoredProcedureName();并且您不能将该输出定向到任何内容，因为它们不返回任何内容（与函数不同）。
回答by dkretz
My first reaction was "That sounds like a view to me". Doesn't that abstract it enough so you can just add the variability into an SP per case?
我的第一反应是“这听起来像我的观点”。这还不够抽象，所以您可以将可变性添加到每个案例的 SP 中？
Anything that adds a temp table that wouldn't otherwise be there is a very likely antipattern.
任何添加本来不会存在的临时表的东西都很可能是反模式。
回答by coder 222
i know this is coming really late but since it took me ages to find a real solution i might as well share. I worked on an example that is below.
我知道这来得太晚了，但由于我花了很长时间才找到真正的解决方案，我不妨分享一下。我研究了下面的一个例子。
the tables created are:
创建的表是：
CREATE TABLE BOOK(
B_ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(B_ID),
TITLE VARCHAR(100),
DESCRIPTION VARCHAR(30),
PRICE DOUBLE);
CREATE TABLE BOOK_COMMENT(
PRIMARY KEY(B_C_ID),
B_C_ID INT NOT NULL AUTO_INCREMENT,
REMARK VARCHAR(120),
B_ID INT,
FOREIGN KEY(B_ID) REFERENCES BOOK(B_ID));
CREATE TABLE AUTHOR(
A_ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(A_ID),
A_NAME CHAR(15),
B_ID INT,
FOREIGN KEY(B_ID) REFERENCES BOOK(B_ID));
- DELIMITER
- 分隔符
CREATE PROCEDURE BOOK_IMPORTANT( _PRICE DOUBLE, _B_ID INT, A_NAME CHAR(15), _BD_ID INT)
BEGIN
INSERT INTO BOOK(PRICE)
VALUES(_PRICE);
SET _B_ID=LAST_INSERT_ID();
INSERT INTO BOOK_COMMENT(B_ID)
VALUES(_B_ID);
SET _BD_ID=LAST_INSERT_ID();
INSERT INTO AUTHOR(A_NAME,B_ID)
VALUES(A_NAME,_BD_ID);
END
then use the following to insert the values.
然后使用以下内容插入值。
CALL BOOK_IMPORTANT('0.79',LAST_INSERT_ID(),'',LAST_INSERT_ID());
LAST_INSERT_ID()takes the last auto increment of the table and inserts it into the referencing column of the child table.
LAST_INSERT_ID()获取表的最后一个自动增量并将其插入到子表的引用列中。
In the procedure parameters _B_IDand _BD_IDrepresent the B_IDsince I need B_IDas a foreign key in both tables.
在过程参数_B_ID和_BD_ID表示B_ID因为我需要B_ID作为两个表中的外键。
Sorry for the excess wording. All the other guys expect you to automatically know how to do it. Hope it helps
抱歉措辞过多。所有其他人都希望你自动知道如何去做。希望能帮助到你
回答by slaakso
You cannot "SELECT INTO" with stored procedures.
您不能使用存储过程“SELECT INTO”。
Create the temporary table first and have your stored procedure to store the query result into the created temporary table using normal "INSERT INTO". The temporary table is visible as long as you drop it or until the connection is closed.
首先创建临时表，并让您的存储过程使用正常的“INSERT INTO”将查询结果存储到创建的临时表中。只要您删除临时表或直到连接关闭，临时表都是可见的。
回答by Janhell
Maybe it's a closed topic, but I would like to offer a solution based on the properties of MySQL temporary tables. First, the way to create the temporary table would not be to call the stored procedure "CREATE TEMPORARY TABLE tmp EXEC nested_sp ();". The query is to the temporary table of "infrastructure", (to name it somehow).
也许这是一个封闭的话题，但我想根据 MySQL 临时表的属性提供一个解决方案。首先，创建临时表的方法不会是调用存储过程“CREATE TEMPORARY TABLE tmp EXECnested_sp();”。查询是“基础设施”的临时表，（以某种方式命名）。
To achieve the desired result, it is necessary to create 2 stored procedures, the first stored procedure processes the data and fills the temporary "infrastructure" table, the second stored procedure, reads this table and continues with the process and finally "DROP" the "infrastructure" table
为了达到预期的结果，需要创建 2 个存储过程，第一个存储过程处理数据并填充临时“基础设施”表，第二个存储过程读取该表并继续该过程，最后“DROP” “基础设施”表
This is the first stored procedure:
这是第一个存储过程：
CREATE DEFINER = 'root'@'localhost'
PROCEDURE cajareal.priv_test()
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp(
column1 TEXT
, column2 TEXT
, column3 TEXT
);
INSERT INTO tmp(column1, column2 , column3) VALUES(CURDATE(), CURRENT_DATE(), CURRENT_TIMESTAMP());
END
This is the second stored procedure:
这是第二个存储过程：
CREATE DEFINER = 'root'@'localhost'
PROCEDURE cajareal.priv_caller()
BEGIN
CALL priv_test;
-- Read data of "infrastructure" table
SELECT * FROM tmp;
-- Do the business logic
-- Delete the "infrastructure" table
DROP TABLE tmp;
END
I use this technique to analyze a string and convert it to the table
我使用这种技术来分析字符串并将其转换为表格
