MySQL character escape

In a string, some sequences have special meanings. These sequences start with a backslash ('\').Escape characters. MySQL recognizes the following escape sequence:

\ 0	ASCII 0 (NUL) characters.
\'	Single quotes (''').
\"	Double quotation marks ('"').
\ B	Return character.
\ N	Line Break.
\ R	Carriage return.
\ T	Tab character.
\ Z	ASCII 26 (Control (Ctrl)-Z ). This character can be encoded as '\ Z' to allow you to solve the problem that ASCII 26 represents the end of the file in Windows. (If you try to use mysqlDb_name<File_name, ASCII 26 will cause problems ).
\\	Backslash ('\') character.
\ %	'%' Character. See the annotations behind the table.
\_	'_' Character. See the annotations behind the table.

These sequences are case sensitive. For example, '\ B' is interpreted as a backspace, but '\ B' is interpreted as 'B '.

The '\ %' and '\ _' sequences are used to search for the '%' and '_' text instances that may be interpreted as wildcards in the pattern matching environment. See section 12.3.1, "string comparison function ". Note that if you use '\ %' or '\ _' in other environments, they return the strings '\ %' and '\_', instead of '%' and '_'.

In other escape sequences, the backslash is ignored. That is to say, escape characters are interpreted as if they are not escaped.

There are several ways to include quotation marks in strings:

· Use ''' referenced ''' in the string to write it ''''.

· You can use '"' referenced '"' in a string to write it '""'.

· Escape characters ('\') can be added before quotation marks ('\').

· The '''' referenced by '"' in the string does not require special processing and does not require double characters or escape. Similarly, using '''referenced 'in a string does not require special processing.

The following SELECT statement shows how references and escaping work:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';

+-------+---------+-----------+--------+--------+

| hello | "hello" | ""hello"" | hel'lo | 'hello |

+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";

+-------+---------+-----------+--------+--------+

| hello | 'hello' | ''hello'' | hel"lo | "hello |

+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';

+--------------------+

| This

Is

Four

Lines |

+--------------------+

mysql> SELECT 'disappearing\ backslash';

+------------------------+

| disappearing backslash |

+------------------------+

If you want to insert binary data (such as BLOB) into a string column, you must use the escape sequence to indicate the following characters:

NUL	NUL byte (ASCII 0 ). Use '\ 0' to represent this character (the backslash is followed by an ASCII '0' character ).
\	Backslash (ASCII 92 ). '\' Indicates the character.
'	Single quotes (ASCII 39 ). '\' Indicates the character.
"	Double quotation marks (ASCII 34 ). '\ "' Indicates the character.

When writing an application, you must correctly escape the strings containing these special characters before they are used to send data values to SQL statements on the MySQL server. You can do this in two ways:

· Use functions that escape special characters to process strings. For example, in a C program, you can use the mysql_real_escape_string () c api function to escape characters. See section 25.2.3.52, "mysql_real_escape_string ()". The Perl DBI interface provides a quote method to convert special characters into correct escape sequences. See section 25.4 "MySQL Perl API ".

· Explicitly escape special characters. Many MySQL APIs provide the placeholder function, allowing you to insert special tags into the query string, and then bind the data values with them when you issue a query. In this case, the API focuses on special characters in the escape value.