A few years ago, I asked to check with me in the long (very long) change history of MySQL 5.5 documentation what are the changes in relation to the SQL syntax.
Chris Calender helped me to retrieve a list of the main changes, thanks again Chris.
Today, I would like to share this list with you.
It is simply a curated transcript of what you might find in the documentation but I’m sure it can help some of you.
INTO clause in nested SELECT statements
Previously, the parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
Table aliases in DELETE statements
In MySQL 5.5.3, several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases.
In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for the USING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table.
As of MySQL 5.5.3, alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part.
Incorrect:
- DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;
- DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;
Correct:
- DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2;
- DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;
Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1:
- DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;
To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:
- DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;
As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.
Statements containing alias constructs that are no longer permitted must be rewritten.
New reserved words
There are several new reserved words in 5.5 what were not reserved in 5.1.
So if you are using any of these as table names, etc., then you’ll need to surround them with backticks (`)
- GENERAL
- IGNORE_SERVER_IDS
- MASTER_HEARTBEAT_PERIOD
- MAXVALUE
- RESIGNAL
- SIGNAL
- SLOW
FLUSH TABLES and LOCK TABLES
As of MySQL 5.5.3, due to work done for Bug #989, FLUSH TABLES is not permitted when there is an active LOCK TABLES … READ.
To provide a workaround for this restriction, FLUSH TABLES has a new variant, FLUSH TABLES tbl_list WITH READ LOCK, that enables tables to be flushed and locked in a single operation.
As a result of this change, applications that previously used this statement sequence to lock and flush tables will fail:
- LOCK TABLES tbl_list READ;
- FLUSH TABLES tbl_list;
Such applications should now use this statement instead:
- FLUSH TABLES tbl_list WITH READ LOCK;
Fast truncation
As of MySQL 5.5.7, InnoDB always uses the fast truncation technique, equivalent to DROP TABLE and CREATE TABLE.
It no longer performs a row-by-row delete for tables with parent-child foreign key relationships.
TRUNCATE TABLE returns an error for such tables.
Modify your SQL to issue DELETE FROM table_name for such tables instead.
TIMESTAMP display width
In very old versions of MySQL (prior to 4.1), the TIMESTAMP data type supported a display width, which was silently ignored beginning with MySQL 4.1.
This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5.
The display width is no longer supporter for TIMESTAMP data type in MySQL 5.5
Aliases and wildcards
Aliases for wildcards (as in SELECT t.* AS 'alias' FROM t) are no longer accepted and result in an error.
Previously, such aliases were ignored silently. (Bug #27249)
Scientific notation
In addition to these official parts of the MySQL release notes, Sheeri write a post about floats, doubles ans scientific notation between MySQL 5.1 and MySQL 5.5 :
http://blog.mozilla.org/it/2013/01/17/mysql-5-1-vs-mysql-5-5-floats-doubles-and-scientific-notation/
This list is incomplete because I’m sure you have your own tips to share.
Source : http://dev.mysql.com/doc/relnotes/mysql/5.5/en/
![MySQL[Plus]](http://www.mysqlplus.net/wp-content/uploads/2013/01/plus_logo_blanc_mini2.png){kind=logo}
