如何将PHP中验证YouTube视频URL的正则迁移至MySQL？

Got it, let's figure out how to migrate your YouTube URL validation regex from PHP to MySQL so you can filter posts with valid YouTube links. First, we need to account for key differences between PHP's PCRE regex and MySQL's regex engine—especially since older MySQL versions use POSIX-style regex (no support for non-capturing groups like (?:...)).

Key Differences to Fix #

1. Non-capturing groups: MySQL's default REGEXP operator doesn't support (?:...). We'll just remove the ?: prefix from all groups (POSIX treats all parentheses as capturing groups, but we don't need the captures here anyway).
2. Backslash escaping: In SQL strings, backslashes need to be doubled (\\) because SQL uses \ as an escape character.
3. Anchor adjustment: Your original PHP regex uses ^ to match the start of the string. If you want to find URLs anywhere in the message field (not just at the very start), remove that ^ anchor.
4. HTML-encoded characters: If your message column stores HTML-escaped content (e.g., & instead of &), keep those in the regex. If it stores raw user input, replace & with & and > with >.

Solution 1: For MySQL < 8.0.4 (POSIX Regex) #

If your message has HTML-escaped content:

SELECT * FROM posts 
WHERE message REGEXP 
'(http(s)?://)?(www\\.)?(m\\.)?(youtu\\.be/|youtube\\.com/((watch)?\\?(.*&)?v(i)?=|(embed|v|vi|user)/))([^?&"\'>]+)'

If your message has raw, unescaped content:

SELECT * FROM posts 
WHERE message REGEXP 
'(http(s)?://)?(www\\.)?(m\\.)?(youtu\\.be/|youtube\\.com/((watch)?\\?(.*&)?v(i)?=|(embed|v|vi|user)/))([^?&"\'>]+)'

Solution 2: For MySQL 8.0.4+ (PCRE Support) #

MySQL 8.0.4 added support for PCRE regex via the REGEXP_LIKE function with the 'pcre' flag. This lets you use a regex almost identical to your original PHP one—you just need to escape backslashes for SQL:

HTML-escaped content version:

SELECT * FROM posts 
WHERE REGEXP_LIKE(
    message, 
    '(?:http(?:s)?://)?(?:www\\.)?(?:m\\.)?(?:youtu\\.be/|youtube\\.com/(?:(?:watch)?\\?(?:.*&)?v(?:i)?=|(?:embed|v|vi|user)/))([^?&"\'>]+)',
    'pcre'
)

Raw content version:

SELECT * FROM posts 
WHERE REGEXP_LIKE(
    message, 
    '(?:http(?:s)?://)?(?:www\\.)?(?:m\\.)?(?:youtu\\.be/|youtube\\.com/(?:(?:watch)?\\?(?:.*&)?v(?:i)?=|(?:embed|v|vi|user)/))([^?&"\'>]+)',
    'pcre'
)

Quick Testing Tip #

Before running this on your full dataset, test with a few sample rows to make sure it's matching the URLs you expect. For example, try it with URLs like https://www.youtube.com/watch?v=dQw4w9WgXcQ, http://m.youtube.com/embed/dQw4w9WgXcQ, and https://youtu.be/dQw4w9WgXcQ to confirm they're picked up.

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