正则表达式来验证MySQL中的URL [英] Regexp to validate URL in MySQL

问题描述

我已经尝试了几种正则表达式模式(设计用于PHP，因为我找不到用于MySQL的任何正则表达式)用于URL验证，但是它们都没有起作用. MySQL的语法可能略有不同.

I have tried several regex patterns (designed for use with PHP because I couldn't find any for MySQL) for URL validation, but none of them are working. Probably MySQL has a slightly different syntax.

我也曾尝试提出一个，但没有成功.

I've also tried to come up with one, but no success.

那么，有谁知道一个相当好的正则表达式与MySQL一起用于URL验证?

So does anyone know a fairly good regex to use with MySQL for URL validation?

推荐答案

根据文章 11.5.2. MySQL的文档中的正则表达式，您可以使用具有以下语法的正则表达式

According to article 11.5.2. Regular Expressions in MySQL's documentation, you can perform selections with a regular expression with the following syntax

SELECT field FROM table WHERE field REGEX pattern

为了匹配简单的URL，您可以使用

In order to match simple URLS, you may use

SELECT field FROM table
 WHERE field REGEXP "^(https?://|www\\.)[\.A-Za-z0-9\-]+\\.[a-zA-Z]{2,4}"

这将与大多数网址匹配

• www.google.il
• http://google.com/
• http://ww.google.net/
• www.google.com/index.php?test=data
• https://yahoo.dk/as
• http://goo.gle.com/
• http://wt.a.x24-s.org/ye/
• www.website.info

• www.google.il
• http://google.com/
• http://ww.google.net/
• www.google.com/index.php?test=data
• https://yahoo.dk/as
• http://goo.gle.com/
• http://wt.a.x24-s.org/ye/
• www.website.info

但不是

• htp://google.com
• ww.google.com/
• www-google.com
• http://google.c
• http://goo#.com
• httpf://google.com

• htp://google.com
• ww.google.com/
• www-google.com
• http://google.c
• http://goo#.com
• httpf://google.com

这篇关于正则表达式来验证MySQL中的URL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！