sql查询特符号

最新推荐文章于 2023-06-13 11:00:46 发布

weixin_30491641

最新推荐文章于 2023-06-13 11:00:46 发布

阅读量93

点赞数 1

原文链接：http://www.cnblogs.com/qinqiu/p/7099545.html

版权

SELECT * FROM `field_data_body` WHERE `body_value` LIKE '_'

出来两条记录：一个_一个.

---------------------------------------------
SELECT * FROM `field_data_body` WHERE `body_value` LIKE %'_'%

SELECT * FROM `field_data_body` WHERE `body_value` LIKE '%_%'
全部的结果都出来的

-----------------------------------------------

SELECT * FROM `field_data_body` WHERE `body_value` LIKE %'_'%

这个是语法错误

---------------------------------------------------

SELECT * FROM `field_data_body` WHERE `body_value` LIKE '%\_%'
出来两条结果，正常

===================

SELECT * FROM `field_data_body` WHERE `body_value` = '\_'

SELECT * FROM `field_data_body` WHERE `body_value` = '_'
等于号的时候是没有转义\这样的说法的

=========================

For SQL Server, from http://msdn.microsoft.com/en-us/library/ms179859.aspx :

% Any string of zero or more characters.

WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ Any single character.

WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]).

WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.
=============================
Backslashes are difficult in mysql. I just ran a test directly from the mysql client, and found that to add the field, you do a single escape (two backslashes):
```
INSERT INTO mytable (field1) VALUES ('ASDFA3\\7\\2011QWER');
```
But with the LIKE command, escapes are needed to be represented literally, so you need to double-escape the statement (four backslashes):
```
SELECT * FROM mytable WHERE field1 LIKE '%A3\\\\7\\\\2011%';
```
Add to that the escapes necessary for you application layer, and it could get quite confusing. An alternative is to use a single character wildcard:
```
SELECT * FROM mytable WHERE field1 LIKE '%A3_7_2011%';
```
Another alternative is to use the 'NO_BACKSLASH_ESCAPES' mode:
```
SET SESSION sql_mode='NO_BACKSLASH_ESCAPES'; SELECT * FROM mytable WHERE field1 LIKE '%A3\7\2011%';
```
EDIT (thanks, lucek):

Or, to preserve the current sql_mode:
```
SET @temp_sql_mode = @@SESSION.sql_mode; SET SESSION sql_mode = CONCAT_WS(',', @@SESSION.sql_mode, 'NO_BACKSLASH_ESCAPES'); -- YOUR_SELECT_GOES_HERE; SET SESSION sql_mode = @temp_sql_mode;
```