{"id":2044,"date":"2015-03-28T16:37:37","date_gmt":"2015-03-28T08:37:37","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2044"},"modified":"2015-03-28T16:37:37","modified_gmt":"2015-03-28T08:37:37","slug":"mysqlpostgresql%e4%b8%ad%e7%9a%84%e6%ad%a3%e5%88%99%e8%a1%a8%e8%be%be%e5%bc%8f","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2044.html","title":{"rendered":"MySQL\/PostgreSQL\u4e2d\u7684\u6b63\u5219\u8868\u8fbe\u5f0f"},"content":{"rendered":"<p>\u4e4b\u524d\u7528\u6570\u636e\u5e93\u7528\u7684\u6bd4\u8f83\u5c11\uff0c\u6ca1\u600e\u4e48\u5b9e\u9645\u7528\u8fc7MySQL\u4e2d\u7684rlike\u8bed\u53e5\uff0c\u6700\u591a\u4e5f\u5c31like\u8fdb\u884c\u4e2a\u6a21\u7cca\u5339\u914d\uff0c\u4f46\u6700\u8fd1\u78b0\u5230\u4e86rlike\uff0c\u89c9\u5f97\u6bd4\u8f83\u6709\u610f\u601d\uff0c\u5c31\u591a\u770b\u4e86\u4e9b\u5185\u5bb9\uff0c\u8bb0\u5f55\u5982\u4e0b\uff1a<\/p>\n<h6>1.MySQL\u4e2d\u7684like\u8bed\u53e5<\/h6>\n<p><a href=\"http:\/\/www.sqlexamples.info\/PHP\/mysql_rlike.htm\" target=\"_blank\">http:\/\/www.sqlexamples.info\/PHP\/mysql_rlike.htm<\/a><\/p>\n<p>The wildcards used with LIKE are:<\/p>\n<ul>\n<li>% substitute for unlimited amount of characters at the BEGINING\/END of the string.<\/li>\n<li>_ substitute for one character<\/li>\n<\/ul>\n<p>==<\/p>\n<h6>2.[MySQL]String Regular Expression Operators<\/h6>\n<table>\n<tbody>\n<tr>\n<td width=\"90\">Name<\/td>\n<td width=\"283\">Description<\/td>\n<\/tr>\n<tr>\n<td width=\"90\"><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/regexp.html#operator_not-regexp\">NOT REGEXP<\/a><\/td>\n<td width=\"283\">Negation of REGEXP<\/td>\n<\/tr>\n<tr>\n<td width=\"90\"><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/regexp.html#operator_regexp\">REGEXP<\/a><\/td>\n<td width=\"283\">Pattern matching using regular expressions<\/td>\n<\/tr>\n<tr>\n<td width=\"90\"><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/regexp.html#operator_regexp\">RLIKE<\/a><\/td>\n<td width=\"283\">Synonym for REGEXP<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Regular expressions in MySQL are invoked with the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/regexp.html\">REGEXP<\/a> keyword, aliased to RLIKE. The most basic usage is a hardcoded regular expression in the right hand side of a conditional clause, e.g.:<\/p>\n<pre class=\"lang:default decode:true\">SELECT * FROM users WHERE email RLIKE '^[a-c].*[0-9]@';<\/pre>\n<p>Additionally, MySQL supports <a href=\"http:\/\/en.wikipedia.org\/wiki\/POSIX\">POSIX<\/a> regular expressions, not <a href=\"http:\/\/www.pcre.org\/\">PCRE<\/a> like Ruby. There are things (like negative lookaheads) that you simply can\u2019t do, though you probably ought not to be doing them in your SQL anyway.<\/p>\n<p>==<\/p>\n<h6>3.[PostgreSQL]Regular Expression Match Operators<\/h6>\n<table>\n<tbody>\n<tr>\n<td width=\"130\">Operator<\/td>\n<td width=\"181\">Description<\/td>\n<td width=\"242\">Example<\/td>\n<\/tr>\n<tr>\n<td width=\"130\">~<\/td>\n<td width=\"181\">Matches regular expression, case sensitive<\/td>\n<td width=\"242\">&#8216;thomas&#8217; ~ &#8216;.*thomas.*&#8217;<\/td>\n<\/tr>\n<tr>\n<td width=\"130\">~*<\/td>\n<td width=\"181\">Matches regular expression, case insensitive<\/td>\n<td width=\"242\">&#8216;thomas&#8217; ~* &#8216;.*Thomas.*&#8217;<\/td>\n<\/tr>\n<tr>\n<td width=\"130\">!~<\/td>\n<td width=\"181\">Does not match regular expression, case sensitive<\/td>\n<td width=\"242\">&#8216;thomas&#8217; !~ &#8216;.*Thomas.*&#8217;<\/td>\n<\/tr>\n<tr>\n<td width=\"130\">!~*<\/td>\n<td width=\"181\">Does not match regular expression, case insensitive<\/td>\n<td width=\"242\">&#8216;thomas&#8217; !~* &#8216;.*vadim.*&#8217;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>IN POSTGRESQL<\/p>\n<blockquote><p>Support for regular expressions in PostgreSQL is similar to that of MySQL, though the syntax is different (e.g. email ~ &#8216;^a&#8217; instead of email RLIKE &#8216;^a&#8217;). What\u2019s more, Postgres contains some useful functions for working with regular expressions, like substring andregexp_replace. See the <a href=\"http:\/\/www.postgresql.org\/docs\/9.0\/static\/functions-matching.html\">documentation<\/a> for more information.<\/p><\/blockquote>\n<p>==<\/p>\n<p>\u5b9e\u9645\u6d4b\u8bd5\uff1a<\/p>\n<pre class=\"lang:default decode:true  \">mysql&gt; select version();\n+-------------------------+\n| version()\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\n+-------------------------+\n| 5.5.41-0ubuntu0.14.04.1 |\n+-------------------------+\n1 row in set (0.01 sec)\n\nmysql&gt; select \"4270202235500236\" rlike '^[0-9]{12,19}$';\nmysql&gt; select \"4270202235500236\" rlike '^([0-9]{4}( |-)?){4}$';\nmysql&gt; select \"4270 2022 3550 0236\" rlike '^([0-9]{4}( |-)?){4}$';\nmysql&gt; select \"4270-2022-3550-0236\" rlike '^([0-9]{4}( |-)?){4}$';<\/pre>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/regexp.html\" target=\"_blank\">http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/regexp.html <\/a>\u00a0#MySQL\u5b98\u65b9\u6587\u6863<\/li>\n<li><a href=\"http:\/\/www.postgresql.org\/docs\/9.2\/static\/functions-matching.html\" target=\"_blank\">http:\/\/www.postgresql.org\/docs\/9.2\/static\/functions-matching.html <\/a>\u00a0#PostgreSQL\u5b98\u65b9\u6587\u6863<\/li>\n<li><a href=\"http:\/\/deerchao.net\/tutorials\/regex\/regex.htm\" target=\"_blank\">\u6b63\u5219\u8868\u8fbe\u5f0f30\u5206\u949f\u5165\u95e8\u6559\u7a0b<\/a><\/li>\n<li><a href=\"http:\/\/viget.com\/extend\/regular-expressions-in-mysql\" target=\"_blank\">Regular Expressions in MySQL | Viget<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/15558172\/mysql-regexp-whitespace-s\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/15558172\/mysql-regexp-whitespace-s<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/9099469\/mysql-select-like-or-regexp-to-match-multiple-words-in-one-record\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/9099469\/mysql-select-like-or-regexp-to-match-multiple-words-in-one-record<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/1842354\/match-a-query-to-a-regular-expression-in-sql\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/1842354\/match-a-query-to-a-regular-expression-in-sql<\/a><\/li>\n<li><a href=\"http:\/\/www.tutorialspoint.com\/mysql\/mysql-regexps.htm\" target=\"_blank\">http:\/\/www.tutorialspoint.com\/mysql\/mysql-regexps.htm<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u4e4b\u524d\u7528\u6570\u636e\u5e93\u7528\u7684\u6bd4\u8f83\u5c11\uff0c\u6ca1\u600e\u4e48\u5b9e\u9645\u7528\u8fc7MySQL\u4e2d\u7684rlike\u8bed\u53e5\uff0c\u6700\u591a\u4e5f\u5c31like\u8fdb\u884c\u4e2a\u6a21\u7cca\u5339\u914d\uff0c\u4f46\u6700\u8fd1\u78b0\u5230 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,12],"tags":[483,16,404,377,484],"class_list":["post-2044","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-tools","tag-like","tag-mysql","tag-postgresql","tag-regex","tag-rlike"],"views":2420,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2044","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2044"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2044\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2044"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2044"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2044"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}