你如何调试 MySQL 存储过程？

How do you debug MySQL stored procedures?

提问by Cory House

My current process for debugging stored procedures is very simple. I create a table called "debug" where I insert variable values from the stored procedure as it runs. This allows me to see the value of any variable at a given point in the script, but is there a better way to debug MySQL stored procedures?

我当前调试存储过程的过程非常简单。我创建了一个名为“调试”的表，在其中插入存储过程运行时的变量值。这允许我在脚本中的给定点查看任何变量的值，但是有没有更好的方法来调试 MySQL 存储过程？

采纳答案by Bob Probst

I do something very similar to you.

我做了和你非常相似的事情。

I'll usually include a DEBUG param that defaults to false and I can set to true at run time. Then wrap the debug statements into an "If DEBUG" block.

我通常会包含一个默认为 false 的 DEBUG 参数，我可以在运行时设置为 true。然后将调试语句包装到“If DEBUG”块中。

I also use a logging table with many of my jobs so that I can review processes and timing. My Debug code gets output there as well. I include the calling param name, a brief description, row counts affected (if appropriate), a comments field and a time stamp.

我还对我的许多工作使用了一个日志表，以便我可以查看流程和时间安排。我的调试代码也在那里得到输出。我包括调用参数名称、简要说明、受影响的行数（如果适用）、注释字段和时间戳。

Good debugging tools is one of the sad failings of all SQL platforms.

好的调试工具是所有 SQL 平台的可悲失败之一。

回答by Brad Parks

The following debug_msgprocedure can be called to simply output a debug message to the console:

debug_msg可以调用以下过程来简单地将调试消息输出到控制台：

DELIMITER $$

DROP PROCEDURE IF EXISTS `debug_msg`$$
DROP PROCEDURE IF EXISTS `test_procedure`$$

CREATE PROCEDURE debug_msg(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN
    select concat('** ', msg) AS '** DEBUG:';
  END IF;
END $$

CREATE PROCEDURE test_procedure(arg1 INTEGER, arg2 INTEGER)
BEGIN
  SET @enabled = TRUE;

  call debug_msg(@enabled, 'my first debug message');
  call debug_msg(@enabled, (select concat_ws('','arg1:', arg1)));
  call debug_msg(TRUE, 'This message always shows up');
  call debug_msg(FALSE, 'This message will never show up');
END $$

DELIMITER ;

Then run the test like this:

然后像这样运行测试：

CALL test_procedure(1,2)

It will result in the following output:

它将导致以下输出：

** DEBUG:
** my first debug message
** DEBUG:
** arg1:1
** DEBUG:
** This message always shows up

回答by George

Yes, there is a specialized tools for this kind of thing - MySQL Debugger.

是的，有一个专门的工具来处理这种事情 - MySQL Debugger。

回答by Eric Leschinski

How to debug a MySQL stored procedure.

如何调试 MySQL 存储过程。

Poor mans debugger:

可怜的调试器：

1. Create a table called logtable with two columns, id INTand log VARCHAR(255).

2. Make the id column autoincrement.

3. Use this procedure:

   delimiter //
   DROP PROCEDURE `log_msg`//
   CREATE PROCEDURE `log_msg`(msg VARCHAR(255))
   BEGIN
       insert into logtable select 0, msg;
   END
   

4. Put this code anywhere you want to log a message to the table.

   call log_msg(concat('myvar is: ', myvar, ' and myvar2 is: ', myvar2));
   

1. 创建一个名为 logtable 的表，其中包含两列id INT和log VARCHAR(255)。

2. 使 id 列自动递增。

3. 使用此程序：

   delimiter //
   DROP PROCEDURE `log_msg`//
   CREATE PROCEDURE `log_msg`(msg VARCHAR(255))
   BEGIN
       insert into logtable select 0, msg;
   END
   

4. 将此代码放在您想要将消息记录到表的任何位置。

   call log_msg(concat('myvar is: ', myvar, ' and myvar2 is: ', myvar2));
   

It's a nice quick and dirty little logger to figure out what is going on.

这是一个很好的快速和肮脏的小记录器来弄清楚发生了什么。

回答by Josef Miran

There are GUI tools for debugging stored procedures/ functions and scripts in MySQL. A decent tool that dbForge Studio for MySQL, has rich functionality and stability.

有用于调试MySQL 中的存储过程/函数和脚本的GUI 工具。dbForge Studio for MySQL 是一款不错的工具，具有丰富的功能和稳定性。

回答by Tone ?koda

Debugger for mysql was good but its not free. This is what i use now:

mysql 调试器很好，但它不是免费的。这是我现在使用的：

DELIMITER GO$

DROP PROCEDURE IF EXISTS resetLog

GO$

Create Procedure resetLog() 
BEGIN   
    create table if not exists log (ts timestamp default current_timestamp, msg varchar(2048)) engine = myisam; 
    truncate table log;
END; 

GO$

DROP PROCEDURE IF EXISTS doLog 

GO$

Create Procedure doLog(in logMsg nvarchar(2048))
BEGIN  
  insert into log (msg) values(logMsg);
END;

GO$

Usage in stored procedure:

存储过程中的用法：

call dolog(concat_ws(': ','@simple_term_taxonomy_id',  @simple_term_taxonomy_id));

usage of stored procedure:

存储过程的用法：

call resetLog ();
call stored_proc();
select * from log;

回答by Jeremy S.

Another way is presented here

另一种方式在这里介绍

http://gilfster.blogspot.co.at/2006/03/debugging-stored-procedures-in-mysql.html

http://gilfster.blogspot.co.at/2006/03/debugging-stored-procedures-in-mysql.html

with custom debug mySql procedures and logging tables.

使用自定义调试 mySql 程序和日志记录表。

You can also just place a simple select in your code and see if it is executed.

您也可以在代码中放置一个简单的选择，看看它是否被执行。

SELECT 'Message Text' AS `Title`; 

I got this idea from

我得到了这个想法

http://forums.mysql.com/read.php?99,78155,78225#msg-78225

http://forums.mysql.com/read.php?99,78155,78225#msg-78225

Also somebody created a template for custom debug procedures on GitHub.

还有人在 GitHub 上为自定义调试程序创建了一个模板。

See here

看这里

http://www.bluegecko.net/mysql/debugging-stored-procedures/https://github.com/CaptTofu/Stored-procedure-debugging-routines

http://www.bluegecko.net/mysql/debugging-stored-procedures/ https://github.com/CaptTofu/Stored-procedure-debugging-routines

Was mentioned here

在这里被提及

How to catch any exception in triggers and store procedures for mysql?

如何在 mysql 的触发器和存储过程中捕获任何异常？

回答by Ash Machine

I just simply place select statements in key areas of the stored procedure to check on current status of data sets, and then comment them out (--select...) or remove them before production.

我只是简单地在存储过程的关键区域放置 select 语句来检查数据集的当前状态，然后将它们注释掉 (--select...) 或在生产之前将它们删除。

回答by Marcelo Amorim

I'm late to the party, but brought more beer:

我参加聚会迟到了，但带来了更多啤酒：

http://ocelot.ca/blog/blog/2015/03/02/the-ocelotgui-debugger/and https://github.com/ocelot-inc/ocelotgui

http://ocelot.ca/blog/blog/2015/03/02/the-ocelotgui-debugger/和 https://github.com/ocelot-inc/ocelotgui

I tried, and it seems pretty stable, supporting Breakpoints and Variable inspection.

我试过了，它似乎很稳定，支持断点和变量检查。

It's not a complete suite (just 4,1 Mb) but helped me a lot!

它不是一个完整的套件（只有 4.1 Mb），但对我帮助很大！

How it works: It integrates with your mysql client (I'm using Ubuntu 14.04), and after you execute:

工作原理：它与您的 mysql 客户端集成（我使用的是 Ubuntu 14.04），并在您执行后：

$install
$setup yourFunctionName

It installs a new database at your server, that control the debugging process. So:

它会在您的服务器上安装一个新数据库，用于控制调试过程。所以：

$debug yourFunctionName('yourParameter')

will give you a chance to step by step walk your code, and "refreshing" your variables you can better view what is going on inside your code.

将让您有机会一步一步地浏览您的代码，并“刷新”您的变量，您可以更好地查看代码中发生的事情。

Important Tip: while debugging, maybe you will change (re-create the procedure). After a re-creation, execute: $exit and $setup before a new $debug

重要提示：在调试时，也许你会改变（重新创建程序）。重新创建后，在新的 $debug 之前执行： $exit 和 $setup

This is an alternative to "insert" and "log" methods. Your code remains free of additional "debug" instructions.

这是“插入”和“记录”方法的替代方法。您的代码仍然没有额外的“调试”指令。

Screenshot:

截屏：

回答by Rahul Tripathi

MySQL Connector/Net 6.6 has a feature to Debug Stored Procedures and Functions

MySQL Connector/Net 6.6 具有调试存储过程和函数的功能

Installing the Debugger

To enable the stored procedure debugger:

• For Connector/Net 6.6: Install Connector/Net 6.6 and choose the Complete option.
• For Connector/Net 6.7 and later: Install the product MySQL for Visual Studio, to which the stored procedure debugger belongs.

Starting the Debugger

To start the debugger, follow these steps:

• Choose a connection in the Visual Studio Server Explorer.
• Expand the Stored Procedures folder. Only stored procedures can be debugged directly. To debug a user-defined function, create a stored
  procedure that calls the function.
• Click on a stored procedure node, then right-click and from the context menu choose Debug Routine.

安装调试器

要启用存储过程调试器：

• 对于 Connector/Net 6.6：安装 Connector/Net 6.6 并选择 Complete 选项。
• 对于 Connector/Net 6.7 及更高版本：安装存储过程调试器所属的产品 MySQL for Visual Studio。

启动调试器

要启动调试器，请执行以下步骤：

• 在 Visual Studio Server Explorer 中选择一个连接。
• 展开存储过程文件夹。只有存储过程可以直接调试。要调试用户定义的函数，请创建
  调用该函数的存储过程。
• 单击存储过程节点，然后右键单击并从上下文菜单中选择调试例程。