Optimizing Queries with Operators for Date, Time and Other Functions

This article will give you a good grounding in operators, branching and functions in MySQL, so you can make the database, instead of your own code, do the bulk of the work. It is the third of three parts, and excerpted from chapter four of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324).

Date and Time Functions

In the previous chapter, you saw how MySQL’s date and time datatypes can save storage space—50% or more over storing dates as strings—but that’s not the only reason for using them. One aspect of applications programming that’s often troublesome is working with dates. Different programming languages have radically different ways of representing dates and performing date calculations; users and clients have different requirements for how they’re displayed. By leveraging MySQL functions that are associated with DATE, TIME, and DATETIME values, you can minimize and sometimes even eliminate many of these problems.

First, let’s look at how to get the current date and time. MySQL supports all of the standard SQL functions for this, as well as several others, as shown in Table 4-3.

Table 4-3. Current Date and Time Functions Supported by MySQL

SQL STANDARD CURRENT_DATE

DESCRIPTION Current date in YYYY-MM-DD format (server time)

MYSQL ADDITIONS CURRENT_DATE(), CURDATE()

CURRENT_TIME

Current time in HH:MM:SS format (server time)

CURRENT_TIME(), CURTIME()

CURRENT_TIMESTAMP

Current timestamp in YYYY-MM-DD HH:MM:SS format (server time)

CURRENT_TIMESTAMP(), NOW(), SYSDATE()*

 

Current timestamp in Unix format (seconds elapsed since 1970-01-01 00:00:00) (server time)

UNIX_TIMESTAMP()

 

Current UTC date in YYYY-MM-DD format

UTC_DATE**, UTC_DATE()**

 

Current UTC time in HH:MM:SS format

UTC_TIME**, UTC_TIME()**

 

Current UTC timestamp in YYYY-MM-DD HH:MM:SS format

UTC_TIMESTAMP**, UTC_TIMESTAMP()**

* SYSDATE() is supported to provide compatibility with Oracle.

** The UTC functions were added in MySQL 4.1.1.

Note that functions expecting a TIME value will generally accept DATETIME values while ignoring the date part. Functions that expect a DATE value will generally accept a DATETIME value and ignore the time portion of the value.

It’s somewhat customary in MySQL to use NOW(), CURDATE() , and CURTIME() , since these are short and convenient. However, if compatibility with other databases is an issue, you should use the standard SQL functions instead. To convert from a Unix-style timestamp to date/time format, use the FROM_UNIXTIME() function.

If you call any functions returning the current date and/or time in the same query, they will always return the same date and/or time.

Date and Time Formatting and Extraction

If your application doesn’t need to support multiple locales or languages, you can perform most, if not all, of your date formatting in your queries using the DATE_FORMAT() function, which takes this form:

DATE_FORMAT(date, format)

where date is a date, and format is a string containing one or more format specifiers and optional additional punctuation marks. Table 4-4 shows a partial listing of the available format specifiers.

Table 4-4. Some Format Specifiers for DATE_FORMAT() and Date Arithmetic Functions

SPECIFIER*

FORMAT

%a

Three-letter day of week (Sun, Mon, Tue, and so on)

%b

Three-letter name of month ( Jan, Feb, Mar, and so on)

%c

Month number (0–12)

%D

Day of month with ordinal suffix (0th, 1st, 2nd, 3rd, and so on)

%d

Two-digit day of month (00–31)

%e

Day of month (0–31)

%f

Microseconds (000000–999999)

%H

Two-digit hour (00–23)

%h or %I

Two-digit hour (01–12)

%i

Minutes (00–59)

%j

Day of year (001–366)

%k

Hour (0–23)

%l

Hour (1–12)

%M

Full month name ( January, February, March, and so on)

%m

Two-digit month number (00–12)

%p

AM/PM

%r

12-hour time in HH:MM:SS XM format

%S or %s

Seconds (00–59)

 

Table 4-4. Some Format Specifiers for DATE_FORMAT() and Date Arithmetic Functions (Continued)

SPECIFIER* %T

FORMAT 24-hour time in HH:MM:SS format

%U

Week of year (00–53), where Sunday is the first day of the week

%u

Week of year (00–53), where Monday is the first day of the week

%W

Full day of week (Sunday, Monday, Tuesday, and so on)

%w

Day of the week (0–6, where 0=Sunday)

%Y

Four-digit year

%y

Two-digit year

*These specifiers return the names of days and months in English.

The following are a few examples of formatting dates.


TIP  
If you need to return only a formatted time, you can also use the TIME_FORMAT() function. It accepts any of the time-related format specifiers shown in Table 4-4.

You can use practically any punctuation you like in formatting dates: commas, dashes, slashes, spaces, and so on. However, you cannot use alphanumeric characters except as part of a format specifier. In addition, you can return a date, time, or date/time value as a number with no formatting whatsoever, simply by forcing it to be evaluated in a numeric context. This will work with any of the functions shown in Table 4-3, as shown in the following example.

Formatting dates can be even easier in MySQL 4.1.1 and above, using the GET_FORMAT() function. This function returns format strings for a number of locales:

GET_FORMAT(DATE|TIME|DATETIME, locale)

The locale argument can take one of several predefined values: ‘EUR’ , ‘USA’ , ‘JIS’ , ‘ISO’ , and ‘INTERNAL’ . GET_FORMAT() can be used in place of a format string wherever one is applicable. Here are some examples:

The SUBDATE() function employed in the second example is used to subtract dates, as discussed in the “Date Arithmetic” section later in this chapter.

You can also obtain the various parts of a date, time, or datetime as a number using the functions shown in Table 4-5.


CAUTION  
The WEEK() function exhibits incorrect behavior in MySQL versions previous to 4.0, where the mode argument is the default (0). See the MySQL documentation for details. We suggest that you use WEEKOFYEAR() instead if it’s available to you (MySQL 4.1.1 and above).

Table 4-5. Functions Returning Portions of Date, Time, or Date/Time Values

FUNCTION DAYNAME()

VALUE RETURNED Name of the day of the week (English)

DAYOFMONTH(), DAY()

Day of the month (DAY() was added in MySQL 4.1.1)

DAYOFWEEK()

Number of the day of the week (1=Sunday, 7=Saturday)

DAYOFYEAR()

Day of the year as a number

HOUR()

Hours portion of the time (added in MySQL 4.1.1)

MINUTE()

Minutes portion of the time

MONTH()

Month portion of the date (1=January)

MONTHNAME()

Name of the month (in English); if the date holds a 0 for the month, this function returns NULL

QUARTER()

Quarter of the year (1–4)

SECOND()

Seconds from time

TIME()

Time portion of a date/time (added in MySQL 4..1.1)

WEEK()*

Week of the year (1–53)

WEEKDAY()

Day of the week (0=Monday, 6=Sunday)

WEEKOFYEAR()

Week of the year, the week reckoned as beginning on Monday (added in MySQL 4.1.1)

YEAR()

Four-digit year (1000–9999)

* The WEEK() function takes an optional second mode argument. The behavior of this function also changed significantly in MySQL 4.0; see the MySQL Manual for details.

Another means of extracting portions of dates and times is to use the EXTRACT() function, which takes this form:

EXTRACT(type FROM date)

It returns a number corresponding to the part of the date argument specified by type. The type argument is the name of one of the following units of time: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. MICROSECOND was added in MySQL 4.1.1; WEEK and QUARTER were added in MySQL 5.0.0. (The date argument is a date or date/time value in standard format.) Here are some examples:

If a time value is missing when you use EXTRACT() , it returns 0.

In addition, the following compound types are permitted: MINUTE_SECOND, HOUR_SECOND, HOUR_MINUTE, DAY_SECOND, DAY_MINUTE, DAY_HOUR, and YEAR_MONTH. Each of these actually specifies a complete set of values; that is, any values that would normally be expected are filled in. For example, if you use HOUR_SECOND for the type, MySQL returns the hours, minutes, and seconds as a number.

MySQL 4.1.1 and above also supports these compound types, which include microseconds: SECOND_MICROSECOND, MINUTE_MICROSECOND, HOUR_MICROSECOND , and DAY_MICROSECOND. These also fill in the missing units from the range, as in this example:

EXTRACT(DAY_MICROSECOND FROM ‘2004-05-15 15:35:25.104528′)

This returns the value 151535250104528.

 


 

NOTE  All of the type specifiers mentioned in this section can also be used with date arithmetic functions such as DATE_ADD(). See the “Date Arithmetic” section later in this chapter.

 


 

{mospagebreak title=Date and Time Conversion Functions}

MySQL also has some handy date and time conversion functions:

FROM_DAYS(days ): Takes a number of days since 0000-00-00 and returns a date in YYYY-MM-DD format. The minimum value accepted by this function is 366, which yields ‘0001-01-01′ ; however, you should not use this function for dates preceding the advent of the Gregorian calendar (1582 in Western Europe; 1917 in Russia) in any case, as it does not take the conversion from Julian to Gregorian reckoning into account.

FROM_UNIXTIME(unix_timestamp[, formatstring]): Takes a Unix timestamp and returns a date/time formatted according to a formatstring using the same format specifiers as the DATE_FORMAT() function (see Table 4-4); the format defaults to standard Unix date/time format.

MAKEDATE(year, day): Takes a year and a day of that year and returns a date in Unix format. Note that day may be greater than 366 and MySQL will calculate the year accordingly in the return value; for example, MAKEDATE(2003, 366) returns ‘2004-01-01′ .

MAKETIME(hours, minutes, seconds): Returns a time in standard format. Unlike MAKEDATE(), passing out-of-range values to this function will result in a NULL value. (Added in MySQL 4.1.1.)

SEC_TO_TIME(seconds): Takes a number of seconds and returns a time in standard Unix (HH:MM:SS) format.

STR_TO_DATE(datestring, formatstring): The inverse of DATE_FORMAT(); takes a formatted datestring and formatstring (using the specifiers for DATE_FORMAT() ) and returns a date or date/time in standard format. (Added in MySQL 4.1.1.)

TIME_TO_SEC(hours, minutes, seconds): Takes a time in HH:MM:SS format and returns a number of seconds. If either the minutes or seconds value is greater than 59, the function returns NULL . The upper limit for the hours argument is at least 1021.

TO_DAYS(date): The inverse of FROM_DAYS(); takes a date string in Unix format and returns the number of days since 0000-00-00. Like FROM_DAYS(), this function is not reliable for dates prior to the adoption of the Gregorian calendar in the sixteenth century.

UNIX_TIMESTAMP(date): Returns a Unix timestamp for a DATE or DATETIME value; can also accept an integer representing a date in YYYYMMDD format. Note that the returned value is an unsigned integer. Out-of-range dates will return 0; the year must be between 1970 and 2037 inclusive.

Date Arithmetic

You have two choices when it comes to performing date arithmetic in MySQL:

  • Converting dates and times into a common unit before performing the calculation. This isn’t that hard to do, and there are times—for instance, when making comparisons in a WHERE clause—when it’s desirable. However, when you’re interested in obtaining a nicely formatted final result, it can be quite cumbersome.
  • Using the INTERVAL operator alone or in conjunction with the DATE_ADD() and DATE_SUB() functions. This is generally what you want to do when you need to pass the results of date calculations back to the application. The advantage here is that MySQL automatically returns the results in standard date, time, or datetime format, which can save you a lot of overhead.

Let’s look at an example illustrating the first option. Suppose our firm’s billing department wants a report of accounts that have unpaid orders that are more than three months old. We need to extract this information from an orders table, a partial definition of which might be as follows:

CREATE TABLE orders (
 
order_id INT AUTO_INCREMENT PRIMARY KEY,
  acct_id INT UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
 
order_amt DECIMAL(8, 2) NOT NULL,
  last_pmt_date DATE NOT NULL,

  order_balance DECIMAL(8, 2) NOT NULL
);

For purposes of this set of examples, we’ll ignore the possibility that any negative amounts might be stored in the order_amt or order_balance columns due to refunds or other adjustments. Also, we won’t worry about creating any indexes or the fact that this does not represent a fully normalized database schema, since we should really have a separate payments table.


NOTE  
In this first example, we also assume that when the order is placed, the last payment date is set to the same value as the order date. The reason for this is that TO_DAYS(‘0000-00-00′) returns NULL .

We need to find all the records for which the last payment date is at least 120 days in the past, and for which there remains an unpaid balance. The unpaid balance part is simple enough—we’ll just need a balance > 0 constraint in the WHERE clause. To determine whether a date is more than 120 days in the past, we can use the TO_DAYS() function to convert both the date column and the current date into numbers of days, subtract, and then compare the difference to 120. The resulting query might look something like this:

SELECT acct_id, SUM(balance) AS total
FROM orders
WHERE balance > 0
AND TO_DAYS(CURRENT_DATE) – TO_DAYS(last_pmt_date) > 120
GROUP BY acct_id;

We’ve used the SUM() function with a GROUP BY clause in order to produce a listing with one entry per delinquent account, with the total past due for all orders made by that account.


NOTE  
Beginning with MySQL 4.1.1, you can also use the DATE_DIFF() function to rewrite TO_DAYS(CURRENT_DATE) – TO_DAYS(last_pmt_date) > 120 as DATE_DIFF(CURRENT_DATE, last_pmt_date) > 120 .

Using data from the same orders table, we want to do a weekly billing for all new orders from the previous week and in each case show a due date 30 days from the date of the order. We won’t worry about whether there’s a balance showing on the account, only whether an order was placed in the last seven days. Using the INTERVAL operator makes this much easier than you might think:

SELECT acct_id, SUM(balance) AS total,
  MAX(order_date) + INTERVAL 30 DAY AS due_date
FROM orders
WHERE order_date + INTERVAL 7 DAY >= CURRENT_DATE
GROUP BY acct_id;

Alternatively, depending on the exact requirements, we might be able to use this:

SELECT acct_id, SUM(balance) AS total,
  MAX(order_date) + INTERVAL 1 MONTH as due_date
FROM orders
WHERE order_date + INTERVAL 1 WEEK >= CURRENT_DATE
GROUP BY acct_id;

As we indicated earlier, we can also use INTERVAL in conjunction with MySQL’s date arithmetic functions:

DATE_ADD(date, INTERVAL expression type) DATE_SUB(date, INTERVAL expression type)

DATE_ADD() returns the date obtained by adding the interval specified by expression type to date. The expression is simply any legal MySQL expression that evaluates to a number. The value used for date can be any valid DATE, TIME, or DATETIME value. DATE_SUB() does the same calculation, except that it returns the date obtained by subtracting the specified interval. In all of these cases, the type argument is any of the values that can be used with EXTRACT() (see Table 4-5). The following are some examples of using the date arithmetic functions.

Synonymous with these functions are ADDDATE() and SUBDATE(), whose arguments follow the same rules.

As you can see from the second query in the preceding examples, there’s nothing wrong with using compound type specifiers with these functions, as long as you observe the rules explained in our earlier discussion of the EXTRACT() function.

Should you choose ADDDATE() and SUBDATE() over DATE_ADD() and DATE_SUB()? In many cases, it doesn’t make any difference; however, beginning with MySQL 4.1.1, ADDDATE() and SUBDATE() have been enhanced somewhat with a simplified alternative syntax when working with numbers of days:

ADDDATE(date, days)
SUBDATE(date, days)

Here are a few examples:

This shorthand notation is not available with DATE_ADD() and DATE_SUB() .

So, as you can see, quite a lot of the work required for date calculations, conversions, and even representations can be handled in queries rather than in application code. Your time spent in learning these and making use of them will generally be well spent.

{mospagebreak title=Other MySQL Functions}

In this section, we’ll discuss a few miscellaneous functions that don’t fit in very well elsewhere, but which you may find useful in your quest to replace program logic with SQL logic for optimization purposes.

How Many Rows Actually Matched?

Sometimes when you’ve used a LIMIT clause in a SELECT query, it’s also handy to know how many rows would have been returned had the LIMIT not been used. In order to do this, first execute the query using the SQL_CALC_FOUND_ROWS option, followed by a SELECT FOUND_ROWS() query. The second query will return the number of all rows meeting the conditions set in the query, without the LIMIT clause.

For example, when displaying a heading such as “Displaying 1 through 10 of 22,052 records,” this approach is often faster and less cumbersome than writing a separate query using the COUNT() function.

IP Address Conversion

The INET_ATON() and INET_NTOA() functions convert an IP address string in dotted-quad format to an integer and back again.

Often, you need to keep IP addresses in logs in your applications. These two functions let you save a bit of space while doing so, since an IP address stored as a string requires at least 8 bytes of storage (and as many as 16 bytes), but when converted to an integer, it takes up 8 bytes and 8 bytes only.

What was the Last ID Inserted?

This LAST_INSERT_ID() function returns the last value generated for an AUTO_INCREMENT column for the current connection. It can be useful when inserting records into linked tables. For example, suppose that we’ve just accepted an order from a new customer, so that we need to insert a new record into a customers table and then a related record into an orders table. Assuming that the customerid column has the AUTO_INCREMENT modifier applied to it in the table definition, we could accomplish this like so:

INSERT INTO customers (customerid, lastname, firstname)
  VALUES (”, ‘Smith’, ‘William’);
INSERT INTO orders (orderid, customerid, orderdate)
  VALUES (”, LAST_INSERT_ID(), CURRENT_DATE);

The value returned by LAST_INSERT_ID() persists for the lifetime of the connection, so we could have done other things between the first INSERT query and the second without altering what was inserted into the customerid column of the new record in the orders table.


TIP  
The value returned by LAST_INSERT_ID() is exclusive to each connection, and it’s not affected by inserts performed by other connections, even on the same tables. In other words, concurrency is never an issue when using this function.

User Variables

It’s a common programming practice to store values returned from queries in the application space for reuse. However, this can often be done within MySQL itself by means of user variables. A user variable (identified by a leading @ sign) can contain any scalar value and lasts for the lifetime of the current connection. It can be named using any combination of alphanumeric characters and may include the $ (dollar sign), _ (underscore), and . (period) characters.

You have two options for setting the value of a user variable:

  • The SET statement, which simply takes the form SET @varname = value;. value can be any legal MySQL expression that evaluates to a scalar value.
  • Within queries, you can use @varname:=value . In this case, the expression used for value can also include one or more column names.

Once the value of a user variable is set, you can use it in any expression. Here is an example that works in MySQL 4.1.1 and newer:

A user variable may hold any scalar type, including integers, floating-point numbers, strings, and dates. You can set a user variable’s value using an expression containing other user variables, as well as values derived from other functions.


CAUTION
  Never try to set a user variable and then use its value in a GROUP BY, ORDER BY, or HAVING clause in the same statement. The results of such a query are unpredictable.

Notice that you’re not limited (as is sometimes imagined) to setting user variables in SELECT queries, as you can see in the next example.

Unfortunately, in web applications, you cannot preserve values between pages by means of MySQL user variables, because each new page load creates its own connection. Even if you use persistent connections, you’re almost certain to get the value for a user variable of the same name that was set using a different connection (or a null value in the case where a new connection was established). MySQL won’t let you choose a connection identifier, nor can you create or set arbitrary global variables. Even so, user variables can still be useful for multiple queries within the same page and, as you’ll see in the example in the next section, for aliasing columns and intermediate results within queries where you can’t use real aliases.

{mospagebreak title=Branching: Making Choices in Queries}

Branching is obviously an area with a wealth of potential when it comes to replacing programming logic with SQL logic. So, in this section, we present some useful MySQL branching methods with this goal in mind.

No programming or scripting language would be very useful if it didn’t provide a way to execute different instructions depending on the outcome of a test condition, such as whether one value is greater than another. MySQL is similar in this regard. It has four flow-control operators that you can use to choose between values based on how one or more conditions are evaluated: IF(), IFNULL(), NULLIF(), and CASE .

True/False Branching with IF()

The IF() operator has the following syntax:

IF(test_expression, true_result, false_result)

The IF() operator takes three expressions as arguments. If test_expression evaluates as true (or 1), than it returns true_result; otherwise, false_result is returned. Here is a fairly simple example:

Let’s look at a slightly more complex scenario. Suppose as part of an e-commerce web site, we have a products table defined in part like this:

CREATE TABLE products (
 
Product_id INT AUTO_INCREMENT PRIMARY KEY,
 
product_price DECIMAL(6, 2),
  product_weight DECIMAL(5, 2)
);

As part of the checkout process, we need to determine shipping according to the following conditions: if the price of a product is greater than $100 or if the weight of the product is less than 500 grams (0.5 kg), then no charges for shipping are added; otherwise, we add a shipping charge of $4.50 per kilo for any weight in excess of 500 grams.

SELECT @p:=product_price AS p, @w:=product_weight AS w,
 
@s:=IF(@p > 100.00 OR @w > 0.5, 0, @w * 4.50) AS s, FORMAT(@p + @s, 2) AS t
FROM products
WHERE product_id=productid;

When we test this with a bit of sample data, here’s the result:

We employed some user variables to get around the problems that arise when trying to use column aliases to define other columns in the same query.

Null-based Branching with IFNULL()

The IFNULL() operator has the following syntax:

IFNULL(expression1, expression2)

The way the IFNULL() function works may seem a bit counterintuitive: I f expression1 is not NULL, then it is returned; otherwise, expression2 is returned. You could express the same logic using IF() and NOT IS NULL:

IF(NOT IS NULL expression1, expression1, expression2 )

For example, consider an orders table for which a partial definition might be like this:

CREATE TABLE orders (
  orderid INT AUTO_INCREMENT PRIMARY KEY, 
  acctid INT NOT NULL,
  orderdate DATE NOT NULL
  shipdate DATE NULL
);

To produce a list of recent orders (say within the past 30 days) showing which ones have been and haven’t yet been shipped, we could use something like this:

SELECT orderid, acctid, IFNULL(shipdate, ‘PENDING’) AS Shipped
FROM orders
WHERE orderdate >= SUBDATE(CURRENT_DATE, INTERVAL 1 MONTH);

Expression Relation-based Branching with NULLIF()

The NULLIF() operator has the following syntax:

NULLIF(expression1, expression2)

This function tests the relation expression1 = expression2 and if the result is true (1), then the function returns NULL; otherwise, it returns expression2. This could also be written as the following:

IF(expression1 = expression2, NULL, expression2)

or, as you’ll see shortly, like this:

CASE WHEN expression1 = expression2 THEN NULL ELSE expression2 END

The NULLIF() function may not prove to be as efficient a method as some others in many circumstances. This is because if expression1 and expression2 are equal, then expression2 will actually be evaluated twice.

CASE-based Branching

The CASE structure can be used to test an expression against one or more other expressions in a series and to return one of any number of different values based on the outcome. It works in a manner similar to that of the if … then … else … construct found in most programming and scripting languages.

There are two different versions of CASE .

Multiple Conditions with CASE

One version of CASE is as follows:

CASE WHEN condition THEN result
[ELSE else_result | WHEN condition2 THEN result2
[ELSE else_result2 | WHEN...] ]
END

In the simplest instance, the condition is tested, and if true, then the result following the THEN keyword is returned. If condition is not true, then NULL is returned. You can override the latter behavior by including an ELSE clause; in this case, if condition is false, then the else_result expression value is returned instead of NULL . For example, we could rewrite the example we used for IFNULL() like this:

SELECT orderid, acctid,
  CASE
    WHEN shipdate IS NULL
    THEN ‘PENDING’
    ELSE shipdate
 
END AS Shipped
FROM orders
WHERE orderdate >= SUBDATE(CURRENT_DATE, INTERVAL 1 MONTH);

This is a bit more verbose than the IFNULL() version, but it’s also easier to read, particularly with the line breaks and indentation we’ve added here.

We could make the example a bit more selective by introducing additional WHEN … THEN … clauses. Using something like the following, we can return “ORDER PENDING” if no shipping date has yet been set, “ORDER SHIPPED ON … ” plus the shipping date if the shipping date is in the past, “FUTURE” if the shipping date is in the future, and “SHIP TODAY” if the shipping date matches today’s date:

SELECT orderid, acctid, @s := shipdate,
  CASE
    WHEN @s IS NULL THEN ‘ORDER PENDING’ 
    WHEN @s < CURRENT_DATE THEN CONCAT(‘ORDER SHIPPED ON ‘, @s)
    WHEN @s > CURRENT_DATE THEN ‘FUTURE’
    ELSE ‘SHIP TODAY’
 
END AS Shipped
FROM orders
WHERE orderdate >= SUBDATE(CURRENT_DATE, INTERVAL 1 MONTH);

You’ll notice that the result set includes an extra column for the shipping date; very shortly (in the “Our Demonstration Revisited” section), we’ll show you how to get rid of this if you don’t need it.


NOTE  
A given CASE structure may contain any number of WHEN… THEN… clauses and a maximum of one ELSE clause.

Comparisons with CASE

The other form of CASE is as follows:

CASE expression WHEN value THEN result [ELSE else_result | WHEN value2 THEN result2
[ELSE else_result2 | WHEN...] ]
END

This compares an expression to a value, both of which can be any valid MySQL expressions, and returns result if the two are equal. Otherwise, either NULL or an else_result is returned, depending on whether or not an ELSE clause follows.

Here is an example:

SELECT
 
CASE order_date
    WHEN last_pmt_date
    THEN ‘YES’
    ELSE ‘NO’
  END AS pmt_status
FROM orders;

The CASE block will evaluate to ‘YES’ if the order_date and last_pmt_date columns for a given record hold the same value, and ‘NO’ if they don’t.


NOTE  
The syntax for CASE varies somewhat from what we’ve shown in this chapter when it’s used inside stored procedures in MySQL 5.0 and later. See Chapter 8 for more information.

What About Loops?

Given what we’ve just seen in the way of decision-making capabilities, you might expect that there would be some sort of mechanism for performing iterations as well. However, MySQL does not support loops in (normal) queries. In fact, up until MySQL 5.0, it didn’t support any sort of looping constructs at all. Beginning with that version, it’s possible to use loops inside stored procedures and user-defined functions. In Chapter 8, we’ll examine the constructs used for this purpose.

{mospagebreak title=Our Demonstration Revisited}

Let’s look again at the query we used at the beginning of this chapter:

SELECT
  CONCAT(firstname, ‘ ‘, lastname) AS name, 
  CASE
   
WHEN (@age := YEAR(CURRENT_DATE) – YEAR(dob)) > 65 THEN ‘Over 65′
    WHEN @age >= 45 THEN ’45-64′
    WHEN @age >= 30 THEN ’30-44′
    WHEN @age >= 18 THEN ’18-29′
    ELSE ‘Under 18′
 
END AS age_range
FROM members
ORDER BY lastname, firstname;

While it may look complicated, it actually just returns two columns, aliased as name and age_range. The first of these is relatively simple: we simply
concatenate the firstname and lastname columns with a space in between and return the result as name. This is obviously more efficient because we return just one column instead of two.

The age_range column appears a bit more complex. Let’s look at the first WHEN clause by itself:

WHEN (@age := YEAR(CURRENT_DATE) – YEAR(dob)) > 65 THEN ‘Over 65′

Here’s the “trick” that we alluded to earlier: Since we’re not interested in the actual age of each member, but only in the age category to which he or she belongs, we set a user variable @age in the first WHEN clause, and then use that value in each of the succeeding ones. Since we’re not trying to use this value in a GROUP BY, ORDER BY, or HAVING clause, we can do this; the value will remain constant (unless we explicitly set it to another value). Notice that we place the expression in which the value of @age is to be set (the difference in years between the year of birth and the current one) inside parentheses. Otherwise, @age would be set to the value of the expression YEAR(CURRENT_DATE) – YEAR(dob) > 65 ; that is, either 1 or 0 (TRUE or FALSE), and so the column value would always be returned as either ‘Over 65′ or ‘Under 18′.

As for the APIs we used for accessing MySQL from PHP and Python scripts, we’ll cover those in the next chapter.

Summary

In this chapter, we basically gave you a crash course in the most common and useful MySQL operators and built-in functions, all the time keeping in mind our central premise of giving you as many opportunities to consider replacing programming logic with SQL logic for optimization purposes.

Why should you go to the trouble of learning and using all of these constructs? The question contains its own answer: so that you can minimize having to do so in your application logic. This makes database interaction faster and more efficient because you generally need to return less data from the database, and because (particularly in the case of web applications) MySQL can usually manipulate the data faster than your programming code can. This is due to the speed at which set processing (operating on a group of records as a single entity) operates as opposed to row processing (working on each record as a separate iteration of a loop).

Maximizing the work done by MySQL also makes migration of your MySQL applications between programming languages and platforms much easier. This may seem trivially self-evident, but as we showed in our demonstration example in this chapter, if your queries already produce the data in the form required for output by your application, your application only needs to output it.

What’s Next

Now we’ve covered just about everything that you need to know in order to create well-structured tables, put data into them, and get that data back out again. In Chapter 5, we’ll examine some additional MySQL features that you can employ in your applications. The first of these is the join, which is simply a query that selects data from multiple tables. This is obviously more efficient than querying one table, and then using the results to provide parameters for making queries on others. There are several different join types in SQL (and thus MySQL); we’ll look at each of these and provide some examples highlighting their use and the differences between them.

Another helpful MySQL feature is the temporary table, which can be used to store results of queries and calculations for further use in a manner that’s independent of your application. Temporary tables are also convenient in that they only last for the duration of a single session, so there’s usually no need to worry about cleanup after you’re finished with them.

Both of these features can help you to save time and effort when developing applications. They’ll also prove invaluable in getting more work out of MySQL (instead of in your application code) and making more efficient use of MySQL itself.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye