Query-Related Issues
2967
mysql>
SELECT @s1 COLLATE latin1_swedish_ci = @s2;
+-------------------------------------+
| @s1 COLLATE latin1_swedish_ci = @s2 |
+-------------------------------------+
| 1 |
+-------------------------------------+
A binary string is case sensitive in comparisons. To compare the string as case insensitive, convert it to
a nonbinary string and use
COLLATE
to name a case-insensitive collation:
mysql>
SET @s = BINARY 'MySQL';
mysql>
SELECT @s = 'mysql';
+--------------+
| @s = 'mysql' |
+--------------+
| 0 |
+--------------+
mysql>
SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'mysql';
+--------------------------------------------------------------+
| CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'mysql' |
+--------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------+
To determine whether a value will compare as a nonbinary or binary string, use the
COLLATION()
[959]
function. This example shows that
VERSION()
[964]
returns a string that has a
case-insensitive collation, so comparisons are case insensitive:
mysql>
SELECT COLLATION(VERSION());
+----------------------+
| COLLATION(VERSION()) |
+----------------------+
| utf8_general_ci |
+----------------------+
For binary strings, the collation value is
binary
, so comparisons will be case sensitive. One context in
which you will see
binary
is for compression and encryption functions, which return binary strings as
a general rule: string:
mysql>
SELECT COLLATION(ENCRYPT('x')), COLLATION(SHA1('x'));
+-------------------------+----------------------+
| COLLATION(ENCRYPT('x')) | COLLATION(SHA1('x')) |
+-------------------------+----------------------+
| binary | binary |
+-------------------------+----------------------+
C.5.5.2. Problems Using
DATE
Columns
The format of a
DATE
value is
'YYYY-MM-DD'
. According to standard SQL, no other format is
permitted. You should use this format in
UPDATE
expressions and in the
WHERE
clause of
SELECT
statements. For example:
SELECT * FROM t1 WHERE date >= '2003-05-05';
As a convenience, MySQL automatically converts a date to a number if the date is used in a numeric
context and vice versa. MySQL also permits a “relaxed” string format when updating and in a
WHERE
clause that compares a date to a
DATE
,
DATETIME
, or
TIMESTAMP
column. “Relaxed” format
means that any punctuation character may be used as the separator between parts. For example,
'2004-08-15'
and
'2004#08#15'
are equivalent. MySQL can also convert a string containing no
separators (such as
'20040815'
), provided it makes sense as a date.
When you compare a
DATE
,
TIME
,
DATETIME
, or
TIMESTAMP
to a constant string with the
<
,
<=
,
=
,
>=
,
>
, or
BETWEEN
operators, MySQL normally converts the string to an internal long integer for faster
comparison (and also for a bit more “relaxed” string checking). However, this conversion is subject to
the following exceptions:
• When you compare two columns
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...