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
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
