A string is a sequence of bytes or characters, enclosed within
either single quote ( '
) or
double quote ( "
) characters.
Examples:
'a string' "another string"
字符串是多个字符组成的一个字符序列,由单引号( ' ) 或双引号 ( " ) 字符包围。
If the ANSI_QUOTES
SQL mode is enabled,
string literals can be quoted only within single quotes because
a string quoted within double quotes is interpreted as an
identifier.
在 ANSI_QUOTES
SQL模式中运行时只能用单引号, ............
A binary string is a string of bytes that has no character set or collation. A non-binary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit. For binary strings, the unit is the byte. For non-binary strings the unit is the character and some character sets allow multi-byte characters. Character value ordering is a function of the string collation.
String literals may have an optional character set introducer
and COLLATE
clause:
[_charset_name
]'string
' [COLLATEcollation_name
]
Examples:
SELECT _latin1'string
'; SELECT _latin1'string
' COLLATE latin1_danish_ci;
For more information about these forms of string syntax, see Section 10.3.5, “Character String Literal Character Set and Collation”.
Within a string, certain sequences have special meaning. Each of
these sequences begins with a backslash
(‘\
’), known as the
escape character. MySQL recognizes the
following escape sequences:
在字符串內,某個順序有特殊的意義。這些順序的每一個以一條反斜線backslash(“\”)開始,稱為 轉義字符escape character。MySQL識別下列轉義字符:
For all other escape sequences, backslash is ignored. That is,
the escaped character is interpreted as if it was not escaped.
For example, ‘\x
’ is just
‘x
’.
These sequences are case sensitive. For example,
‘\b
’ is interpreted as a
backspace, but ‘\B
’ is
interpreted as ‘B
’.
The ASCII 26 character can be encoded as
‘\Z
’ to enable you to work around
the problem that ASCII 26 stands for END-OF-FILE on Windows.
ASCII 26 within a file causes problems if you try to use
mysql
.
db_name
<
file_name
The ‘\%
’ and
‘\_
’ sequences are used to search
for 文字實例literal instances of ‘%
’ and
‘_
’ in pattern-matching 正文contexts
where they would 否則otherwise be interpreted as wildcard
characters. See the description of the LIKE
operator in Section 12.3.1, “String Comparison Functions”. If
you use ‘\%
’ or
‘\_
’ in non-pattern-matching
contexts, they evaluate to the strings
‘\%
’ and
‘\_
’, not to
‘%
’ and
‘_
’.
注意,如果你在某些正文環境中使用“\%”或“\%_”,這些將返回字符串“\%”和“\_”而不是“%”和“_”。
There are several ways to include quote characters within a string:
A ‘'
’ inside a string quoted
with ‘'
’ may be written as
‘''
’.
A ‘"
’ inside a string quoted
with ‘"
’ may be written as
‘""
’.
Precede the quote character by an escape character
(‘\
’).
A ‘'
’ inside a string quoted
with ‘"
’ needs no special
treatment and need not be doubled or escaped. In the same
way, ‘"
’ inside a string
quoted with ‘'
’ needs no
special treatment.
有幾種方法在一個字符串內包括引號:
The following SELECT
statements demonstrate
how quoting and escaping work:
下面顯示的SELECT展示引號和轉義如何工作:
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 into a string column (such as
a BLOB
column), the following characters must
be represented by escape sequences:
如果你想要把二進制binary數據 插入到一個BLOB列,下列字符必須由轉義序列escape sequences表示:
NUL |
NUL byte (ASCII 0). Represent this character by
‘\0 ’ (a backslash
followed by an ASCII ‘0 ’
character). |
\ |
Backslash (ASCII 92). Represent this character by
‘\\ ’. |
' |
Single quote (ASCII 39). Represent this character by
‘\' ’. |
" |
Double quote (ASCII 34). Represent this character by
‘\" ’. |
When writing application programs, any string that might contain any of these special characters must be properly escaped before the string is used as a data value in an SQL statement that is sent to the MySQL server. You can do this in two ways:
Process the string with a function that escapes the special
characters. In a C program, you can use the
mysql_real_escape_string()
C API function
to escape characters. See
Section 22.2.3.52, “mysql_real_escape_string()
”. The Perl DBI
interface provides a quote
method to
convert special characters to the proper escape sequences.
See Section 22.4, “MySQL Perl API”. Other language interfaces may
provide a similar capability.
As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.
If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons.
Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.
expr
LIKE
pat
[ESCAPE
'escape_char
']
Pattern matching using SQL simple regular expression
comparison. Returns 1
(TRUE
) or 0
(FALSE
). If either
expr
or
pat
is NULL
,
the result is NULL
.
The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
Per the SQL standard, LIKE
performs
matching on a per-character basis, thus it can produce
results different from the =
comparison
operator:
mysql>SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+ | 'ä' LIKE 'ae' COLLATE latin1_german2_ci | +-----------------------------------------+ | 0 | +-----------------------------------------+ mysql>SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+ | 'ä' = 'ae' COLLATE latin1_german2_ci | +--------------------------------------+ | 1 | +--------------------------------------+
With LIKE
you can use the following two
wildcard characters in the pattern:
Character | Description |
% |
Matches any number of characters, even zero characters |
_ |
Matches exactly one character |
mysql>SELECT 'David!' LIKE 'David_';
-> 1 mysql>SELECT 'David!' LIKE '%D%v%';
-> 1
To test for literal instances of a wildcard character,
precede it by the escape character. If you do not specify
the ESCAPE
character,
‘\
’ is assumed.
String | Description |
\% |
Matches one ‘% ’ character |
\_ |
Matches one ‘_ ’ character |
mysql>SELECT 'David!' LIKE 'David\_';
-> 0 mysql>SELECT 'David_' LIKE 'David\_';
-> 1
To specify a different escape character, use the
ESCAPE
clause:
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
-> 1
The escape sequence should be empty or one character long.
As of MySQL 5.1.2, if the
NO_BACKSLASH_ESCAPES
SQL mode is enabled,
the sequence cannot be empty.
The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:
mysql>SELECT 'abc' LIKE 'ABC';
-> 1 mysql>SELECT 'abc' LIKE BINARY 'ABC';
-> 0
In MySQL, LIKE
is allowed on numeric
expressions. (This is an extension to the standard SQL
LIKE
.)
mysql> SELECT 10 LIKE '1%';
-> 1
Note: Because MySQL uses C
escape syntax in strings (for example,
‘\n
’ to represent a newline
character), you must double any
‘\
’ that you use in
LIKE
strings. For example, to search for
‘\n
’, specify it as
‘\\n
’. To search for
‘\
’, specify it as
‘\\\\
’; this is because the
backslashes are stripped once by the parser and again when
the pattern match is made, leaving a single backslash to be
matched against. (Exception: At the end of the pattern
string, backslash can be specified as
‘\\
’. At the end of the
string, backslash stands for itself because there is nothing
following to escape.)
expr
NOT LIKE
pat
[ESCAPE
'escape_char
']
This is the same as NOT
(
.
expr
LIKE
pat
[ESCAPE
'escape_char
'])
,
expr
NOT REGEXP
pat
expr
NOT RLIKE
pat
This is the same as NOT
(
.
expr
REGEXP
pat
)
expr
REGEXP
pat
expr
RLIKE
pat
Performs a pattern match of a string expression
expr
against a pattern
pat
. The pattern can be an
extended regular expression. The syntax for regular
expressions is discussed in Appendix G, Regular Expressions.
Returns 1
if
expr
matches
pat
; otherwise it returns
0
. If either
expr
or
pat
is NULL
,
the result is NULL
.
RLIKE
is a synonym for
REGEXP
, provided for
mSQL
compatibility.
The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
Note: Because MySQL uses
the C escape syntax in strings (for example,
‘\n
’ to represent the newline
character), you must double any
‘\
’ that you use in your
REGEXP
strings.
REGEXP
is not case sensitive, except when
used with binary strings.
mysql>SELECT 'Monty!' REGEXP 'm%y%%';
-> 0 mysql>SELECT 'Monty!' REGEXP '.*';
-> 1 mysql>SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1 mysql>SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
-> 1 0 mysql>SELECT 'a' REGEXP '^[a-d]';
-> 1
REGEXP
and RLIKE
use
the current character set when deciding the type of a
character. The default is latin1
(cp1252
West European). Warning:
These operators are not multi-byte safe.
STRCMP()
returns 0
if
the strings are the same, -1
if the first
argument is smaller than the second according to the current
sort order, and 1
otherwise.
mysql>SELECT STRCMP('text', 'text2');
-> -1 mysql>SELECT STRCMP('text2', 'text');
-> 1 mysql>SELECT STRCMP('text', 'text');
-> 0
STRCMP()
uses the current character set
when performing comparisons. This makes the default
comparison behavior case insensitive unless one or both of
the operands are binary strings.
Posted by Sascha René Leib on October 10 2002 3:29am | [Delete] [Edit] |
Also, keep in mind that by default,
Swedish/Finnish string comparison rules are in
use! This means that e.g.: u <> ü = y !
Posted by James Sleeman on January 12 2004 7:43am | [Delete] [Edit] |
You
should also be aware that because NOT ( NULL ) is NULL and NULL LIKE
'%' is NULL, that NOT ( foo LIKE '%bar%' ) will return only those rows
that do not contain 'bar' AND are also not NULL.
That is,
select count(*) from foo; => 2
select count(*) from foo where bar IS NULL; => 2
select count(*) from foo where bar LIKE '%narf%'; => 0
normal so far, but then we negate the last one, expecting to get 2 rows..
select count(*) from foo where NOT (bar LIKE '%narf%'); => 0
Posted by Jeremy Schultz on May 4 2004 10:10pm | [Delete] [Edit] |
In
the manual it states "Before MySQL 4.0, STRCMP() is case sensitive.", I
did not relize this, and I found out the hard way that STRCMP() is
case-sensitive.
Also I found it strange that STRCMP() is case-sensitive, but ORDER BY is case-insensitive.
Here
is the what I was doing, and I hope this helps others. I have a table
that I display, just a basic dump of the table order by a varchar
field. I have a section to display a single item from the list, then
used the STRCMP to go to the next/prevoius item in the order it was
displayed
Listing the table:
SELECT id, name, price, description WHERE category = 1 ORDER BY name
Getting the Next Item Button for display section:
SELECT id WHERE category = 1 AND STRCMP(name, 'current_item_name') = 1 ORDER BY name LIMIT 1
Getting the Prev Item Button for display section:
SELECT id WHERE category = 1 AND STRCMP(name, 'current_item_name') = -1 ORDER BY name DESC LIMIT 1
The Fix I use for now, is to lowercase the strings in STRCMP():
STRCMP(LOWER(name), LOWER('current_item_name'))
Posted by Moreno Gentili on July 2 2004 12:36pm | [Delete] [Edit] |
You got a wrong sytanx there, James Sleeman.
This query:
SELECT 'abcdefg' LIKE '%c%'
returns 1 because the string 'abcdefg' matches the LIKE pattern. As a consequence:
SELECT NOT ('abcdefg' LIKE '%c%')
returns 0
So, according to your example, the right syntax to select all those records that do not match the LIKE pattern is:
SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%narf%'
Posted by Anthony Law on July 9 2004 9:15pm | [Delete] [Edit] |
No Moreno you're wrong & completely missed the point.
James
is trying to point out if BAR IS NULL then it won't show up in a NOT
condition, because the opposite of NULL is still NULL (i.e. not 1).
In other words, both
a) WHERE bar LIKE '%';
b) WHERE bar NOT LIKE '%';
will miss the rows WHERE bar IS NULL;
Refer to the docs above. The syntax you gave is identical to his:
=> expr NOT LIKE pat [ESCAPE 'escape-char']
=>
=> This is the same as NOT (expr LIKE pat [ESCAPE 'escape-char']).
Posted by Derek Hateley on July 12 2005 8:48pm | [Delete] [Edit] |
Hi All
I
found a "Funny" today. Have been developing a VB6 app using ODBC
connectivity, and mostly testing with Access for ease of setup and
changing/adding data field. Anyway:
SELECT COUNT (*) AS Cnt FROM
Customer WHERE Postcode LIKE 'CV%' worked fine pointing to Access, but
fell over when I pointed to MySQL (version 3.23.58).
Turned out to be the space between COUNT & (*)
Now works a treat.
Posted by Ami Chayun on January 12 2006 10:16am | [Delete] [Edit] |
It is possible to search wildchar patterns when using bind parameters.
For example to search a substring:
select * from tblUser where Name LIKE CONCAT('%', ? ,'%');
And then (for example in perl DBI)
$dbh->selectrow_hashref($SQL_Stmnt, undef, @{[ 'Erik']});
This provides a 'safer' search environment in web interfaces, since it prevents SQL injections.
User Comments
I was having the following error and this page seems to be the key to solving it:
Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation
When I looked at my table, I had specified the collation sequence as:
utf8_bin (binary是二进制)
yet in my SQL (in PHP code) I was simply specifying a literal (it was more fancy than "xyz" - arabic or chinese, but you will get the point):
SELECT * from my_table WHERE my_col = "xyz"
Now, the solution proved to be stating:
SELECT * from my_table WHERE my_col = _utf8"xyz"
Add your own comment.
MySQL中文排序错误的解决方法
一. 方法1
在MySQL中,进行中文排序和查找的时候,对汉字的排序和查找结果是错误的。这种情况在MySQL的很多版本中都存在。如果这个问题不解决,那么MySQL将无法实际处理中文。
出现这个问题的原因是:MySQL在查询字符串时是大小写不敏感的,在编绎MySQL时一般以ISO-8859字符集作为默认的字符集,因此在比较过程中中文编码字符大小写转换造成了这种现象,一种解决方法是对于包含中文的字段加上"binary"属性,使之作为二进制比较,例如将"name char(10)"改成"name char(10)binary"。
二. 方法2
如果你使用源码编译MySQL,可以编译MySQL时使用--with--charset=gbk 参数,这样MySQL就会直接支持中文查找和排序了。
.