如何在MySQL中的正则表达式中转义特殊的SQL字符 [英] How to escape special SQL characters in regular expression in Mysql

问题描述

我有一个文本字段可以接受来自UI的正则表达式.对于这些正则表达式，我具有搜索功能，并且想要进行搜索.我正在使用准备好的语句，而数据库是mysql.当我在'％'上进行搜索时，我只希望搜索以'％'开头的正则表达式.但是，由于'％'在mysql中是通配符，因此我在搜索中得到了所有正则表达式.如何逃脱.

I have a text field to accept regular expressions from the UI. For these regular expressions, I have a search capability and want to do a search. I am using prepared statements and the DB is mysql. When I do a search on '%', I only want search regex starting with '%'. But, since '%' is wildcard in mysql, I get all the regex in the search. How to escape it.

推荐答案

只需在字符前使用反斜杠，如

Just use a backslash before the character, as shown in the MySQL documentation section 9.1:

\0  An ASCII NUL (0x00) character.  
\'  A single quote ("'") character.  
\"  A double quote (""") character.  
\b  A backspace character.  
\n  A newline (linefeed) character.  
\r  A carriage return character.  
\t  A tab character.  
\Z  ASCII 26 (Control+Z). See note following the table.  
\\  A backslash ("\") character.  
\%  A "%" character. See note following the table.  
\_  A "_" character. See note following the table.  

注意(来自MySQL文档):

Note (from the MySQL documentation):

如果在模式匹配上下文之外使用"\％"或"\ _"，则它们的计算结果为字符串"\％"和"\ _"，而不是％"和"_".

If you use "\%" or "\_" outside of pattern-matching contexts, they evaluate to the strings "\%" and "\_", not to "%" and "_".

这篇关于如何在MySQL中的正则表达式中转义特殊的SQL字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！