sql查询特符号

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;