Home arrow MySQL arrow Page 5 - Views and More in MySQL 5.0

Other Improvements in MySQL 5.0 - MySQL

MySQL 5.0 supports a range of features that earlier versions of MySQL do not support. This article, the fourth in a series, explains views and other new features. It 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).

  1. Views and More in MySQL 5.0
  2. Syntax--Creating, Altering, and Dropping Views
  3. Examples
  4. Benefits
  5. Other Improvements in MySQL 5.0
  6. Change in User Variable Behavior
By: Apress Publishing
Rating: starstarstarstarstar / 25
May 11, 2006

print this article



There are some additional new features in MySQL 5.0 that you may find useful. These are detailed in the next few subsections.


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() .

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: