Upgrading MySQL
150
• Incompatible change: If you have created a user-defined function (UDF) with a given name and
upgrade MySQL to a version that implements a new built-in function with the same name, the
UDF becomes inaccessible. To correct this, use
DROP FUNCTION
to drop the UDF, and then use
CREATE FUNCTION
to re-create the UDF with a different nonconflicting name. If a new version
of MySQL implements a built-in function with the same name as an existing stored function, you
have two choices: Rename the stored function to use a nonconflicting name, or change calls to the
function so that they use a database qualifier (that is, use
db_name.func_name()
syntax). See
Section 9.2.3, “Function Name Parsing and Resolution”
, for the rules describing how the server
interprets references to different kinds of functions.
• Incompatible change: As of MySQL 5.0.15, the
CHAR()
[886]
function returns a binary string
rather than a string in the connection character set. An optional
USING charset_name
clause
may be used to produce a result in a specific character set instead. Also, arguments larger than
256 produce multiple characters. They are no longer interpreted modulo 256 to produce a single
character each. These changes may cause some incompatibilities:
•
CHAR(ORD('A')) = 'a'
is no longer true:
mysql>
SELECT CHAR(ORD('A')) = 'a';
+----------------------+
| CHAR(ORD('A')) = 'a' |
+----------------------+
| 0 |
+----------------------+
To perform a case-insensitive comparison, you can produce a result string in a nonbinary
character set by adding a
USING
clause or converting the result:
mysql>
SELECT CHAR(ORD('A') USING latin1) = 'a';
+-----------------------------------+
| CHAR(ORD('A') USING latin1) = 'a' |
+-----------------------------------+
| 1 |
+-----------------------------------+
mysql>
SELECT CONVERT(CHAR(ORD('A')) USING latin1) = 'a';
+--------------------------------------------+
| CONVERT(CHAR(ORD('A')) USING latin1) = 'a' |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
• Incompatible change: Beginning with MySQL 5.0.12, natural joins and joins with
USING
,
including outer join variants, are processed according to the SQL:2003 standard. The changes
include elimination of redundant output columns for
NATURAL
joins and joins specified with a
USING
clause and proper ordering of output columns. The precedence of the comma operator also
now is lower compared to
JOIN
,
LEFT JOIN
, and so forth.
These changes make MySQL more compliant with standard SQL. However, they can result in
different output columns for some joins. Also, some queries that appeared to work correctly prior to
5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes
and examples that show what query rewrites are necessary, see
Section 13.2.8.2, “
JOIN
Syntax”
.
•
CREATE TABLE ... SELECT CHAR(...)
produces a
VARBINARY
column, not a
VARCHAR
column. To produce a
VARCHAR
column, use
USING
or
CONVERT()
[948]
as just described to
convert the
CHAR()
[886]
result into a nonbinary character set.
• Previously, the following statements inserted the value
0x00410041
(
'AA'
as a
ucs2
string) into
the table:
CREATE TABLE t (ucs2_column CHAR(2) CHARACTER SET ucs2);
INSERT INTO t VALUES (CHAR(0x41,0x41));
As of MySQL 5.0.15, the statements insert a single
ucs2
character with value
0x4141
.
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 ...
