September 09
The most recent MySQL server I used in a production environment was a 3.23, so in conjunction with reading the 5.0 sources, I saw fit to reread large parts of the documentation to see where updates had happened, fixes were applied, extensions added. Here are some random clippings from the docs that illustrate quirks, changes from MySQL 3.23 (other than those noted in 3.23->4.0, 4.0->4.1, 4.1->5.0 which we'll give a brief tour of at the end) and differences from the standard. If you recently read the docs, kindly walk on, there's nothing to see here. If being a MySQL admin is #96 on the list of who you are, stand by for some trivia.
One such goody was added in 4.1.1, ROLLUP. Traditional GROUP BY gives you (possibly multi-level) grouping, but aggregates ( SUM(), COUNT(), ...) only provide one level of analysis; with ROLLUP, you can have several.
UNION was added in 4.0.0; it returns a set union of two of more SELECT statements, optionally with an ORDER BY and/or LIMIT clause, like so:
(SELECT a,b,c FROM table WHERE a=999)
UNION
(SELECT a,b,c FROM table WHERE b=22)
ORDER BY a LIMIT 10
UPDATE accepts an ORDER BY clause from 4.0.0 on.
Before MySQL 4.0.13, LIMIT is a rows- affected restriction in UPDATEs. The statement stops as soon as it has changed n rows that satisfy the WHERE clause.
From 4.0.13 on, LIMIT is a rows- matched restriction. The statement stops as soon as it has found n rows that satisfy the WHERE clause, whether or not they actually were changed.
Starting with MySQL 4.0.4, UPDATE can cover multiple tables:
UPDATE items,month SET items.price=month.price WHERE items.id=month.id
Any JOIN type is allowed, but you cannot use ORDER BY or LIMIT.
Before 4.0.18, you need the UPDATE privilege for all tables used in a multiple-table UPDATE, even if they were not updated. As of MySQL 4.0.18, you need only the SELECT privilege for any columns that are read but not modified.
From MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables.
Sub-SELECTs: « Sub-queries no longer have to be rewritten as JOINs. Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported (as well as a few features that are MySQL-specific). » They're structured, they're readable, and « they provide alternative ways to perform operations that would otherwise require complex JOINs and UNIONs. » And you can practice them here.
TRUNCATE now faster than DELETE *.
« From MySQL 4.1.0 on, you are allowed to specify DUAL as a dummy table name in situations where no tables are referenced. (Oracle compatibility, presumably.) »
Also in 4.1, the ON DUPLICATE KEY UPDATE clause was added to the INSERT statement. If a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. (As opposed to REPLACE ... INTO which performs a DELETE before the INSERT to avoid a duplicate.) For example, if column a is declared as UNIQUE and contains the value 1, INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1 amounts to UPDATE table SET c=c+1 WHERE a=1. If column b is unique too, the INSERT would be equivalent to this UPDATE statement instead: UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1 In general, you should try to avoid using the ON DUPLICATE KEY clause on tables with multiple UNIQUE keys.
As of MySQL 4.1.1, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT part of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted if no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise.
When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.
« As of 4.1, SHOW WARNINGS|ERRORS [LIMIT [offset,] row_count], SHOW COUNT(*) WARNINGS|ERRORS, SELECT @@warning_count|error_count; provide info on the last executed statement — a glorified errno. »
As of 4.1, character column definitions can include a CHARACTER SET (aka CHARSET) attribute to specify the character set and, optionally, a collation for the column:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin)
Also as of 4.1, MySQL interprets length specifications in character column definitions in characters rather than in bytes.
As of 4.1.1, specifying the NO_AUTO_VALUE_ON_ZERO flag for the --sql-mode server option or the sql_mode system variable allows you to store 0 in AUTO_INCREMENT columns without generating a new sequence value.
From 4.1.6 on, a TIMESTAMP field can contain NULL (rather than writing the current time on any request to assign NULL to the field). From 4.1 on, the format of TIMESTAMP has changed, and it no longer needs to be the leftmost TIMESTAMP that's magic.
From 4.1.0 on, some storage engines allow you to specify an index type when creating an index.
CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE=MEMORY
In 4.1 or later, you can create SPATIAL indexes on spatial column types. Spatial types are supported only for MyISAM tables and indexed columns must be declared as NOT NULL.
« In versions of MySQL prior to 5.0.1, parentheses in table_references were just omitted and all JOIN operations were grouped to the left. In general, parentheses can be ignored in JOIN expressions containing only INNER JOIN operations. As of 5.0.1, nested joins are allowed. [...] INNER JOIN allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11. »
« Before MySQL 5.0.2, a HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. Standard SQL requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. To accommodate both standard SQL and the MySQL-specific behavior of being able to refer columns in the SELECT list, MySQL 5.0.2 and up allows HAVING to refer to columns in the SELECT list, columns in the GROUP BY clause, columns in outer subqueries, and to aggregate functions. For example, the following statement works in MySQL 5.0.2 but produces an error for earlier versions: SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2 »
You can use the keyword DEFAULT to explicitly set a column to its default value a/o MySQL 4.0.3; as of MySQL 4.1.0, you can use DEFAULT(col_name) as a more general form that can be used in expressions to produce a column's default value.
INSERT INTO tbl_name () VALUES(); is a legal statement if all columns have default values, or strict mode is not enabled.
As of 4.1, server SQL modes are per client. SELECT @@sql_mode to enquire.
From MySQL 5.0.2 on, the NOT operator precedence is handled so that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). Before, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the new HIGH_NOT_PRECEDENCE SQL mode.
From 4.0->4.1:
InnoDB included by default. Query cache. Boolean mode and other goodies for fulltext search and enhanced MERGE tables, now supporting INSERT statements and AUTO_INCREMENT in MyISAM. UNION syntax in SELECT. Dynamic server variables, allowing configuration changes to be made without having to stop and restart the server. Improved replication code and features. Prepared statements.
« All tables and non-binary string columns (CHAR, VARCHAR, and TEXT) have a character set. […] Binary string columns (BINARY, VARBINARY, and BLOB) contain strings of bytes and do not have a character set. If you were using columns with the CHAR BINARY or VARCHAR BINARY data types in MySQL 4.0, these were treated as binary strings. To have them treated as binary strings in MySQL 4.1, you should convert them to use the BINARY and VARBINARY data types, respectively »
String comparison now works according to SQL standard: Instead of stripping end spaces before comparison, the shorter string is now extended with spaces. Hence, 'a' > 'a\t', which it wasn't before.
« TIMESTAMP is returned as a string in 'YYYY-MM-DD HH:MM:SS' format. »
« Binary values such as 0xFFDF are assumed to be strings instead of numbers. »
« When using multiple-table DELETE statements, you should use the alias of the tables from which you want to delete, not the actual table name […]: DELETE t1 FROM test AS t1, test2 WHERE ... »
« LIMIT no longer accepts negative arguments. Use some large number (maximum 18446744073709551615) instead of -1. » Bah.
« The password hashing mechanism has changed in 4.1 to provide better security, but this may cause compatibility problems if you still have clients that use the client library from 4.0 or earlier. »
From 4.1->5.0:
The popular three, of course — views, stored procedures and triggers.
Update log gone (use binary), ISAM engine gone.
RAID options in MyISAM.
5.0.3 uses precision math (precision, platform-independence, error- and overflow-handling), and BIT is a separate data type, not a synonym for TINYINT(1).
5.0.2+ has stricter SQL modes that pose certain constraints on invalid data, such as wonky dates. Yay for not having to do all those tests on application level anymore, even if it's still suggested. INSERT IGNORE/UPDATE IGNORE trump strict.
Finally, the names of user variables are no longer case sensitive like they were in 4.1.
SELECT INTO list_of_vars.
|
Community (12)