从 MySQL 中的存储过程打印调试信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3314771/
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
Print debugging info from stored procedure in MySQL
提问by Peanut
Is there a way in MySQL to print debugging messages to stdout, temptable or logfile? Something like:
MySQL 有没有办法将调试消息打印到标准输出、临时表或日志文件？就像是：
printin SQLServerDBMS_OUTPUT.PUT_LINEin Oracle
print在 SQLServer 中DBMS_OUTPUT.PUT_LINE在甲骨文
回答by Patrick
Option 1: Put this in your procedure to print 'comment' to stdout when it runs.
选项 1：将其放入您的程序中，以便在运行时将“注释”打印到标准输出。
SELECT 'Comment';
Option 2: Put this in your procedure to print a variable with it to stdout:
选项 2：把它放在你的程序中，用它打印一个变量到标准输出：
declare myvar INT default 0;
SET myvar = 5;
SELECT concat('myvar is ', myvar);
This prints myvar is 5to stdout when the procedure runs.
这会myvar is 5在过程运行时打印到标准输出。
Option 3, Create a table with one text column called tmptable, and push messages to it:
选项 3，创建一个表，其中包含一个名为 的文本列tmptable，并向其推送消息：
declare myvar INT default 0;
SET myvar = 5;
insert into tmptable select concat('myvar is ', myvar);
You could put the above in a stored procedure, so all you would have to write is this:
您可以将上述内容放在存储过程中，因此您只需编写以下内容：
CALL log(concat('the value is', myvar));
Which saves a few keystrokes.
这节省了几次击键。
Option 4, Log messages to file
选项 4，将消息记录到文件
select "penguin" as log into outfile '/tmp/result.txt';
There is very heavy restrictions on this command. You can only write the outfile to areas on disk that give the 'others' group create and write permissions. It should work saving it out to /tmp directory.
此命令有非常严格的限制。您只能将输出文件写入磁盘上授予“其他”组创建和写入权限的区域。它应该可以将其保存到 /tmp 目录。
Also once you write the outfile, you can't overwrite it. This is to prevent crackers from rooting your box just because they have SQL injected your website and can run arbitrary commands in MySQL.
此外，一旦您编写了输出文件，就无法覆盖它。这是为了防止破解者仅仅因为他们将 SQL 注入您的网站并且可以在 MySQL 中运行任意命令而使您的机器生根。
回答by b8b8j
One workaround is just to use select without any other clauses.
一种解决方法是只使用 select 而没有任何其他子句。
回答by Rob Cooke
I usually create log table with a stored procedure to log to it. The call the logging procedure wherever needed from the procedure under development.
我通常使用存储过程创建日志表来登录它。从正在开发的过程中在需要的地方调用日志记录过程。
Looking at other postson this same question, it seems like a common practice, although there are some alternatives.
查看关于同一问题的其他帖子，这似乎是一种常见做法，尽管有一些替代方法。
回答by Roberto Rodriguez
Quick way to print something is:
打印东西的快速方法是：
select '** Place your mesage here' AS '** DEBUG:';
回答by Mohan Munisifreddy
This is the way how I will debug:
这是我将如何调试的方式：
CREATE PROCEDURE procedure_name()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS; --this is the only one which you need
ROLLBACK;
END;
START TRANSACTION;
--query 1
--query 2
--query 3
COMMIT;
END
If query 1, 2 or 3 will throw an error, HANDLER will catch the SQLEXCEPTION and SHOW ERRORS will show errors for us. Note: SHOW ERRORS should be the first statement in the HANDLER.
如果查询 1、2 或 3 将抛出错误，HANDLER 将捕获 SQLEXCEPTION，而 SHOW ERRORS 将为我们显示错误。注意：SHOW ERRORS 应该是 HANDLER 中的第一条语句。
