Oracle 5.0, Reference Manual

Page: 2986 / 3232

Query-Related Issues
2966
• Recompile MySQL from source to use a different default Unix socket file location. Define the path
to the file with the
--with-unix-socket-path
[121] option when you run
configure
. See
Section 2.17.3, “MySQL Source-Configuration Options” .
You can test whether the new socket location works by attempting to connect to the server with this
command:
shell> mysqladmin --socket=/path/to/socket version
C.5.4.6. Time Zone Problems
If you have a problem with
SELECT NOW()
returning values in UTC and not your local time, you
have to tell the server your current time zone. The same applies if
UNIX_TIMESTAMP()
[931] returns
the wrong value. This should be done for the environment in which the server runs; for example, in
mysqld_safe
or
mysql.server
. See Section 2.21, “Environment Variables” .
You can set the time zone for the server with the
--timezone=timezone_name
[248] option to
mysqld_safe
. You can also set it by setting the
TZ
environment variable before you start
mysqld
.
The permissible values for
--timezone
[248] or
TZ
are system dependent. Consult your operating
system documentation to see what values are acceptable.
C.5.5. Query-Related Issues
C.5.5.1. Case Sensitivity in String Searches
For nonbinary strings (
CHAR
,
VARCHAR
,
TEXT
), string searches use the collation of the comparison
operands. For binary strings (
BINARY
,
VARBINARY
,
BLOB
), comparisons use the numeric values of the
bytes in the operands; this means that for alphabetic characters, comparisons will be case sensitive.
A comparison between a nonbinary string and binary string is treated as a comparison of binary strings.
Simple comparison operations (
>=, >, =, <, <=
, sorting, and grouping) are based on each
character's “sort value.” Characters with the same sort value are treated as the same character. For
example, if “
e
” and “
é
” have the same sort value in a given collation, they compare as equal.
The default character set and collation are
latin1
and
latin1_swedish_ci
, so nonbinary string
comparisons are case insensitive by default. This means that if you search with
col_name LIKE 'a
%'
, you get all column values that start with
A
or
a
. To make this search case sensitive, make sure
that one of the operands has a case sensitive or binary collation. For example, if you are comparing a
column and a string that both have the
latin1
character set, you can use the
COLLATE
operator to
cause either operand to have the
latin1_general_cs
or
latin1_bin
collation:
col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin
If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or
binary collation. See
Section 13.1.10, “
CREATE TABLE
Syntax” .
To cause a case-sensitive comparison of nonbinary strings to be case insensitive, use
COLLATE
to
name a case-insensitive collation. The strings in the following example normally are case sensitive, but
COLLATE
changes the comparison to be case insensitive:
mysql> SET @s1 = 'MySQL' COLLATE latin1_bin,
-> @s2 = 'mysql' COLLATE latin1_bin;
mysql> SELECT @s1 = @s2;
+-----------+
| @s1 = @s2 |
+-----------+
| 0 |
+-----------+