求助：用grepWin正则匹配指定关键词文本块或MySQL查询存储过程方案

Hey there! I've got you covered on both fronts—getting grepWin to work with your pattern, and a MySQL query to find the right stored procedures. Let's break it down step by step.

1. Fixing grepWin Regex for Your Pattern #

grepWin uses the .NET regex engine, so we need to craft a pattern that accounts for possible line breaks between your target phrases (since stored procedures often span multiple lines). Here's the regex you need:

(?s).*start transaction.*st_message_index.*commit;.*

What this does: #

• (?s): Enables single-line mode, which makes the . character match line breaks (critical if your target text spans multiple lines).
• .*: Matches any number of characters (including spaces) between your key phrases.
• The sequence start transaction.*st_message_index.*commit; ensures the phrases appear in order, with any content in between.

grepWin Settings to Enable: #

• Make sure the "Regular expression" checkbox is checked (under the "Search Mode" section).
• You don't need extra settings if you use (?s)—it handles line breaks internally.

Example Matches (Should Be Found):

start transaction;
DECLARE msg_id INT;
SELECT id INTO msg_id FROM st_message_index WHERE ...;
commit;

Example Non-Matches (Should Be Excluded):

start transaction;
UPDATE some_table SET ...;
commit;
-- Missing st_message_index

SELECT * FROM st_message_index;
-- Missing start transaction and commit

2. MySQL Query to Find Matching Stored Procedures #

If you'd rather query MySQL directly for stored procedures containing your pattern, use the INFORMATION_SCHEMA.ROUTINES table (where MySQL stores procedure definitions). Here's the query:

SELECT 
  ROUTINE_NAME, 
  ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE 
  ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_DEFINITION REGEXP 'start transaction[\\s\\S]*st_message_index[\\s\\S]*commit;'

Notes on the MySQL Regex: #

• [\\s\\S]*: Matches any character (including line breaks) between your key phrases—this works around MySQL's default behavior where .* doesn't match newlines.
• ROUTINE_TYPE = 'PROCEDURE': Filters to only stored procedures (exclude functions if you don't need them).
• If you're using MySQL 8.0+, you can also use the REGEXP_LIKE function for more control, but the above query works across most versions.

This will return all stored procedures where the definition includes start transaction, followed by any content (including line breaks), then st_message_index, then any content, and finally commit;.

内容的提问来源于stack exchange，提问作者love2code