Views and More in MySQL 5.0 - Other Improvements in MySQL 5.0
(Page 5 of 6 )
There are some additional new features in MySQL 5.0 that you may find useful. These are detailed in the next few subsections.
New SELECT INTO Syntax
We’ve already seen how this can be used with cursors in a stored procedure. You can also use it to assign values to user variables in a regular SELECT query, e.g., SELECT name INTO @prodname FROM products WHERE prodid = 327; . Note that the SELECT must return a single row for this to work. We show an example here:
New Timestamp Functions
The TIMESTAMPADD() and TIMESTAMPDIFF() functions are introduced in MySQL 5.0.0. Here are the definitions for each:
TIMESTAMPADD(interval, expr, datetime) TIMESTAMPDIFF(interval, datetime1, datetime2)
TIMESTAMPADD() adds expr interval intervals to datetime and returns the result, where expr is an integer value. TIMESTAMPDIFF() returns the integer difference between datetime1 and datetime2 in terms of the unit supplied as interval .
In both cases, the interval argument may be any one of the values FRAC_SECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR , and any of these may optionally be prefixed with SQL_TSI_ . The datetime expressions may be any valid DATE or DATETIME values.
A few examples, as shown here, may help to make clear how these functions are used:
Notice that negative values are possible both for the value of expr input to the TIMESTAMPADD() function as well as for the result returned by TIMESTAMPDIFF() .
Next: Change in User Variable Behavior >>
More MySQL Articles
More By Apress Publishing
|
 This article is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 17 
