Close Window x

Frequently Asked Questions

Help Center Search

What are the new features available in MySQL 4.1?

Print this Article
Comment on this Article
Last Updated: November 12, 2008 9:23 AM

Some of the new features in MySql 4.1


32 bit password hashing

This will offer better password security.

Sub-queries

  • Scalar subqueries return a single value, that is, one row with one column of data.
  • Row subqueries return a single row with one or more columns of data.
  • Column subqueries return one or more rows of data, in a single column.
  • Table subqueries return a result with one or more rows containing one or more columns of data.

Timestamps

The behavior of the TIMESTAMP data type has changed in a number of important ways in MySQL Server 4.1:

  • The display format has changed.
  • TIMESTAMP columns may be defined to accept and store NULL values.
  • You have more control over the default value and update behavior of TIMESTAMP columns.
  • It is possible to use connection-specific time zone settings.

Prepared Statements

A new feature of MySQL Server 4.1 is support for prepared statements. Prepared statements are useful when you want to run several queries that differ only in very small details.

Besides being a convenience, prepared statements also offer enhanced performance, because the complete statement is parsed only once by the server. Once the parse is complete, the server and client may make use of a new protocol that makes fewer data conversions (and usually makes for less traffic between the server and client) than in earlier versions of MySQL.

MySQL Server 4.1 does not allow every type of SQL statement to be prepared. The types of SQL statement that may be prepared are limited to the following:

  • SELECT statements.
  • Updating statements: INSERT, REPLACE, UPDATEand DELETE.
  • CREATE TABLE statements.

New clause to INSERT: ON DUPLICATE KEY UPDATE

MySQL Server 4.1 adds a new clause to the INSERT statement: ON DUPLICATE KEY UPDATE.

Normally, if you attempt to insert a row into a table that would result in a duplicate-key error for a unique-valued index, the insertion fails. In some cases, you can use the REPLACE statement instead, which deletes the old row and inserts the new one in its place.

However, REPLACE is not suitable if you wish to change only some columns of the old row. With the new syntax, you have the option of choosing to update one or more columns of the existing row, rather than letting the INSERT statement fail or replacing the entire row.

This INSERT syntax allows you to do in one statement what otherwise requires two (INSERT and UPDATE). Also, for non-transactional tables, it saves you from having to explicitly lock the table to prevent UPDATE errors when the referenced row may have been deleted in between the INSERT and UPDATE. One case where this new behavior is especially useful is when you have a table with counters that are tied to key values. When it's time to increment a counter in the record for a given key, you want to create a new record if none exists for the key, but just increment the counter if the key does exist.

GROUP_CONCAT()

GROUP_CONCAT() is a new aggregate function in MySQL 4.1. Like other aggregate functions such as SUM() and MAX(), it is used in grouping operations.

The purpose of the GROUP_CONCAT() function is to concatenate column values into a single string. This is useful if you would otherwise perform a lookup of many rows and then concatenate them on the client end.

GROUP BY ... WITH ROLLUP

A new functionality that can be used with GROUP BY is WITH ROLLUP. Unlike GROUP_CONCAT(), WITH ROLLUP is not an aggregate function, it is a modifier for the GROUP BY clause.

Suppose that you need to generate a listing of the population of each continent, as well as the total of the population on all continents. One way to do this is by running one query to get the per-continent totals and another to get the total for all continents. Another way to get the results requires some application programming: The application can retrieve the per-continent values and sum those to calculate the total population value. By using WITH ROLLUP, you get both the detailed results as well as the total sum of all rows, eliminating the need for multiple queries or extra processing on the client side.

NOTE: For more information on any of these new features see the MySQL 4 Certification Study Guide.