Optimizing Queries with Operators, Branching and 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 first 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).

IN THIS CHAPTER, we’re going to explore the central premise of this book: It is almost always the best course of action to make the database perform as much work as possible, thus minimizing the amount of filtering and manipulating data that you do in your programming code. Another way of saying this is that by replacing program logic with SQL logic, it is possible to gain significant optimization benefits. A strong working knowledge of operators, branching, and functions in MySQL, with this optimization goal in mind, is a key part of your database skills.

While the SQL dialect supported in MySQL isn’t a complete programming language in and of itself, it does provide many capabilities for manipulating and filtering data. These include logical and arithmetic operators, functions for working with strings and numbers, and branching operators that allow you to make and act on decisions within queries. As you’ll see in this chapter, using these, you can create powerful queries and open new optimization opportunities.

We’ll start out the chapter by demonstrating that it’s possible to replace a lot of your program logic with SQL logic and achieve optimization benefits. Then we’ll cover the following MySQL features:

  • Logical, arithmetic, and comparison operators
  • Math functions
  • Functions for manipulating strings
  • Date and time functions
  • User-defined variables
  • Decision-making and flow-control constructs

With the possible exception of the basic operators, these are among MySQL’s most underused and underappreciated features.

By the time you’ve finished this chapter, you’ll be familiar with most of these features, and you’ll be able to understand their role in optimizing your own projects to improve their performance.

Replacing Program Logic with SQL Logic: A Demonstration

Many application developers don’t seem to realize that SQL, while not a complete programming language in its own right, still has a wealth of constructs that can be used to fine-tune queries so that only necessary data is returned by them. Quite often, we’ve seen cases where programmers will return overly large result-sets, and then filter or modify them in program code.

For example, consider a scenario in which we wish to output to a web page a list of the members of some organization. Let’s suppose the member data is stored in a members table that was created using this statement:

CREATE TABLE members (
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  #  possibly more column definitions that don’t relate to this example…
  dob DATE NOT NULL
);

We want to format the listing nicely as an HTML table with the full name of each member in one of two columns and an age range in the other. Here’s one way we could do that using PHP 4:

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
    “http://www.w3.org/TR/html4/loose.dtd”> <html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″> <title>List All Members With Age Ranges [PHP4 / Logic In PHP Code]</title>
</head>
<body>
<?php
 
// establish a connection to MySQL, and select the proper database.. .
  $connection = mysql_connect(“localhost”, “jon”, “mypass”);
  mysql_select_db(“mydb”, $connection);
  // get the current year for the age calculation below…
  $year = (int) date(“Y”);
 
// submit a query…
  $query = “SELECT firstname, lastname, dob FROM members ORDER BY lastname”;
  $result = mysql_query($query);
?>
<!– begin table display –>
<table cellpadding=”5″ cellspacing=”0″ border=”1″>
  <tr><th colspan=”2″>MEMBERS BY AGE GROUP</th></tr>
  <tr><th>Name</th><th>Age Group</th></tr>
<?php
  // for each row returned from the database…
  while($row = mysql_fetch_assoc($result))  
  {
    extract($row);
    // get the year of birth from the birthdate column
    // get the age by subtracting it from the current year
    $age = $year – (int) substr($dob, 0, 4);
    // determine the age range based on the year if($age >= 65)
       $age_range = “Over 65″;
     elseif($age >= 45)
       $age_range = “45-64″;
     elseif($age >= 30)
       $age_range = “30-44″;
     elseif($age >= 18)
       $age_range = “18-29″;
     else
      
$age_range = “Under 18″;
    // output the resulting age range along with the member’s name
    echo “<tr><td>$firstname $lastname</td><td>$age_range</td></tr>n”;
  }
?>
</table>
<!– end table display –>
</body>
</html>


NOTE 
Anyone who is familiar with PHP might wonder why we didn’ t use a switch … case block instead of the  if … elseif … else. The reason is that Python doesn’t have an analogue to the former. And the reason why we care about this will become apparent shortly. So please bear with us.

There’s nothing really wrong with this approach (it does work, after all), but notice that we need to process each row of the result set as part of the output loop. Wouldn’t it be nice if we could obtain exactly the data we needed from the database, formatted exactly as we would like to use it in the output?

It turns out that we can do exactly that, by moving the formatting and decision-making functionality into the query that we send to MySQL:

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
    “http://www.w3.org/TR/html4/loose.dtd”> <html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″> <title>List All Members With Age Ranges [PHP4 / Logic in SQL]</title>
</head>
<body>
<?php
 
// connect and select.. .
  $connection = mysql_connect(“localhost”, “jon”, “mypass”);
  mysql_select_db(“mydb”, $connection);
  // new and improved query, which handles the logic and formatting
 
$query = “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-19′
                ELSE ‘Under 18′
              END AS age_range
              FROM members ORDER BY lastname”;
  $result = mysql_query($query);
?>
<!– begin table display –>
<table cellpadding=”5″ cellspacing=”0″ border=”1″>
  <tr><th colspan=”2″>MEMBERS BY AGE GROUP</th></tr>
  <tr><th>Name</th><th>Age Group</th></tr>
<?php
  // for each record returned by the query…
  while($row = mysql_fetch_assoc($result)) 
  {
   
// extract and output the data
    extract($row);
   
echo “<tr><td>$name</td><td>$age_range</td></tr> n”;
 
}
?>
</table>
<!– end table display –>
</body>
</html>

By the time you reach the end of this chapter, you’ll be able to come back to this example and see exactly what the complex query does, as well as write your own. For now, just assume that we know what we’re doing, and that the end result is the same as in the first example.

This second method has three main optimization advantages. We’ll cover each advantage one by one now in some detail, as they support the central premise that we are conveying to you in this chapter. We’ll also revisit this example at the end of the chapter.

{mospagebreak title=Optimization 1: Separation of Logic and Formatting} 

First, we can separate the logic required for formatting the data that’s retrieved from the display loop. This is much cleaner code and makes it easier to modify either the data being passed to the display loop (by modifying the query) or the display loop itself.

For example, if we decide to output the names as lastname, firstname, rather than firstname lastname, we simply change the relevant part of the query to CONCAT(lastname, ‘, ‘, firstname) AS name , without needing to touch the PHP code. If we do need to alter the presentation, we can tweak the display code without fear of messing up the derivation of the data or formatting of the data itself.

As an added bonus, you’ll often find that your code is a bit shorter as well, because you can write fewer and/or shorter loops.

Optimization 2: Speed

This method is also faster. If you’re retrieving only a few records, the difference in speed between these two PHP scripts might not be very noticeable, but increase that number to, say, 50,000 records, and you’ll see that the second ver sion executes about 10% to 15% more quickly than the first. Naturally, any improvements you might see in your own applications from adopting this methodology are going to be affected by a wide range of variables, but in an enterprise setting with vast amounts of data and many users, or on a busy web site with hundreds or even thousands of simultaneous visitors, any performance gain you can muster becomes important.


TIP  
You can often pick up an additional speed boost by making use of the query-caching capabilities available in MySQL 4.0 and later. See Chapter 6 for a discussion of query caching.

Optimization 3: Portability

Finally, this method is also more portable. Suppose we’re told that (a) we need to port this script to (just for the sake of example) Python and (b) this really needed to be done yesterday. Which version of the PHP script would you prefer to work from—particularly if you’re not a Python expert?

Working from the second version of the PHP script plus a rudimentary knowledge of Python, a copy of the Python documentation, and the MySQLdb module ( http://sourceforge.net/projects/mysql-python ), Jon was able to produce a working script in about 30 minutes (including the time required to download, install, and configure ActivePython and MySQLdb ):

#!/usr/bin/pytho n
import cgi
import MySQLdb
#  connect to MySQL and create a cursor
db = MySQLdb.connect(host=”localhost”, user=”zontar”, passwd=”mypass”, db=”mdbd”) cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
#  the same query used in the “improved” PHP 4 example
query = “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”
#  submit the query and store the result set
cursor.execute(query)
result = cursor.fetchall()
#  begin HTML output
print “Content-Type: text/html”
print
print “<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”  

           “http://www.w3.org/TR/html4/ loose.dtd”>n
<html>n<head>n
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″>n
<title>List All Members With Age Ranges [Python-CGI / Logic in SQL]</title>n </head>n<body>”
# begin HTML table display
print “<table cellpadding=”5″ cellspacing=”0″ border=”1″>n
       <tr><th colspan=”2″>MEMBERS BY AGE GROUP</th></tr>n
       <tr><th>Name</th><th>Age Group</th></tr>”
#  display result rows in HTML table rows for row in result:
  print “<tr><td>%s</td><td>%s</td></tr>” % (row[0], row[2])
# end table display
print “</table>”
#  end HTML page
print “</body>n</html>”
#  end of script

The query does all the hard work of calculation, decision-making, string manipulation, and so forth. This means that the tasks remaining for the script in Python (or whatever language the boss told us to use) are easy to implement, almost trivially so:

  1. Send the query.
  2. Receive the result.
  3. Generate the dynamic output by looping through the result set.

Notice that no heavy-duty parsing, calculation, or string-manipulation code is required; we’ve already done all that using nothing but SQL. In other words, we’ve abstracted all of those tasks out of our display code and into the query.

Figure 4-1 shows the output of the Python script in a web browser.


Figure 4-1. Output of the demonstration example


NOTE
If you haven’t encountered Python before and are interested in checking it out, you can obtain the latest version (Python 2.3.4 as of this writing) for a variety of platforms from either http://www.python.org/  (Unix source code;Windows and Mac OS X binaries) or  http://activestate.com/Products/ ActivePython/
(installers for Windows, Linux, and Solaris). You might also want to pick up a copy of Magnus Lie Hetlend’s Practical Python (Apress, 2002), which we’ve read and can highly recommend.

{mospagebreak title=MySQL Operators}

As we stated at the beginning of this chapter, our central premise is that it is almost always the best course of action to make the database perform as much work as possible, thus minimizing the amount of filtering and manipulating data that you do in your programming code. Simply put, a strong working knowledge of operators increases your options when it comes to replacing your programming logic with SQL logic.


NOTE  Most of the functions and operators that we’ll look at in this chapter can be used in SELECT , INSERT , and UPDATE queries, either as column values or in WHERE clauses. We’ll provide you with examples of all of these.

Most of MySQL’s arithmetic and logical operators are likely to be familiar to you, as they’re more or less the same as you’ll find in most common programming languages. So we won’t spend a great deal of time with them, but we will go over them and point out their MySQL-specific eccentricities where necessary. 


NOTE  
Like most programming languages, MySQL uses parentheses for grouping to force the order in which operations are performed. You can use these whenever you want to override the normal precedence of MySQL operators, which we’ll discuss later in this chapter.

Logical Operators

MySQL has the basic logical operators you would expect to find in any scripting or programming language. The list was completed in MySQL 4.0.2, when support for XOR was added.

Logical operators always evaluate to TRUE, FALSE, or NULL. As we’ve mentioned before, MySQL doesn’t have a Boolean datatype as such; instead, it uses 1 for Boolean TRUE and 0 for Boolean FALSE.

NOT (!): Negates the condition it precedes. Generally speaking, MySQL doesn’t care whether you use ! or NOT , so you can write whichever suits you.

AND (&&): In order for an AND comparison to be true, both conditions to be tested must be true. You may use AND and && interchangeably. Note that 1 AND NULL returns NULL , and 0 AND NULL returns 0 (FALSE).

OR (||): Is true if at least one of the operands is true. 1 OR NULL returns 1 (TRUE), and 0 OR NULL returns NULL . OR and || are synonyms, so you can use either of them as you prefer.

XOR (^): Yields a true result if one and only one of the operands is true. Any value XOR ’ed with NULL yields NULL . XOR and ^ are synonyms.

We recommend that you use AND and OR rather than && and || in order to preclude the possibility of accidentally leaving out one of the doubled symbols and so writing a bitwise operator instead of a logical operator.


NOTE  
Bitwise operators aren’t used that often, and we don’t cover them here. If you need to find out about them, consult the MySQL Manual or other reference.

In addition, the double-pipe version of OR also serves as a concatenation operator when running MySQL in ANSI mode, and it can be used in this manner in Oracle and PostgreSQL as well.

The rules for resolving expressions containing logical operators are shown as in Table 4-1.

Table 4-1. Truth Tables for MySQL Logical Operators

OPERATOR

TRUE

FALSE

NULL

AND TRUE FALSE NULL

TRUE FALSE NULL

FALSE FALSE FALSE

NULL FALSE NULL

OR TRUE FALSE NULL

TRUE TRUE NULL

TRUE FALSE NULL

TRUE NULL NULL

XOR TRUE FALSE NULL

FALSE TRUE NULL

TRUE FALSE NULL

NULL NULL NULL

NOT

FALSE

TRUE

NULL

While you may have seen these before, you may not be familiar with how they act on NULL values in MySQL. Basically, the negation of NULL is NULL , and NULL used with any logical operator and any other value also results in NULL , with one exception: A value of FALSE, when AND ’ed with NULL , results in a Boolean FALSE.

NULL values in MySQL. Basically, the negation of NULL is NULL , and NULL used with any logical operator and any other value also results in NULL , with one exception: A value of , when AND ’ed with NULL , results in a Boolean . While you may have seen these before, you may not be familiar with how they act on NULL values in MySQL. Basically, the negation of NULL is NULL , and NULL used with any logical operator and any other value also results in NULL , with one exception: A value of , when AND ’ed with NULL , results in a Boolean .

Arithmetic Operators

As in the case of logical operators, MySQL’s arithmetic operators are fairly standard. Here they are, along with a few “gotchas” to watch for:

+ (addition / unary positive): Add two numbers together. A unary plus sign can be used to denote a positive number (basically, it is ignored). Note that, unlike the case with some scripting languages and Microsoft SQL Server, you cannot use the plus sign to concatenate strings! (See the notes about type conversion in the next section.)

(subtraction / unary negative or additive inverse): Subtracts one number from another. Used with a single number, it causes that number to be negative.

* (multiplication): Gets the product of two numbers. The order of the arguments does not affect the value returned.

/ (division): Indicates a floating-point division: for example, 3 / 5 returns the value 0.6.

DIV (integer division): Indicates an integer division; all fractions are rounded down. For example, 5 DIV 3 returns 1, and 3 DIV 5 returns 0. This operator was added in MySQL 4.1.

% (modulus): Performs a division and returns the remainder. For example, 5 % 3 yields 2. If the first argument is negative, a negative value is returned, so that -10 % -3 and -10 % -3 both return 1, but 10 % -3 returns 1. Note that where ABS(a) <  ABS(b) , a % b simply returns a; for example, 3 % 10 evaluates to 3, and -3 % 10 evaluates to –3. You may also use the MOD() function for this; for example, MOD(5,  3) returns 2. Beginning with MySQL 4.1, you may also write 5 MOD 3 instead of MOD(5,  3) .

ABS(N) returns the absolute value of a number: if N is positive, the returned value is simply N; if N is negative, the function returns –N. In case you’re unfamiliar with absolute values, we show a quick example here.

{mospagebreak title=Type Conversions with Logical and Arithmetic Operators}

Here are a few rules to keep in mind when working with logical and arithmetic operators in MySQL:

  • Any nonzero integer used with a logical operator evaluates as TRUE, even if it has a negative value.
  • When used with logical operators, fractional values are rounded to the nearest integer. This means that any N where 0.5  > ABS(N)  = 0 evaluates as FALSE. (In other words, any number whose absolute value is less than 0.5 is rounded to 0 when used with a logical operator.)
  • When adding, subtracting, multiplying, or dividing a mix of integers and decimals, the result will always be a decimal; 3 * 2.33 returns 6.99, and the expression 2.1 + 1.145 – 3.245 yields 0.000.
  • Any string value used in a logical or arithmetic context always evaluates to 0 (FALSE). For example, ‘a’ AND 1 returns 0 (FALSE) and ‘b’ + 4 returns 4.
  • Any arithmetic expression containing NULL returns NULL . This includes the expressions +NULL and -NULL .
  • Division by zero produces a NULL result.
  • Operators (like all MySQL keywords) are case-insensitive. So it makes no practical difference whether you write A AND B , A and B , or A && B. However, as stated elsewhere, we generally use uppercase for MySQL keywords throughout this book for the sake of easy recognition and consistency.

While this may all look quite familiar, be aware that what is true in your programming or scripting language of choice may not be so in MySQL. So, if you encounter what seems to be a bug in using arithmetic operators in a query, it may just be that MySQL behaves a bit differently than what you’re used to.

Comparison Operators

Like all programming languages, MySQL’s dialect of SQL has a more or less usual set of comparison operators, as well as a few unusual ones. The equals sign ( = ) serves as both the equality operator and the assignment operator, and an expression containing it evaluates to TRUE (1) if the operands are the same, and FALSE (0) if they’re not. If at least one of the operands is NULL , then the result is always NULL .

As mentioned in our discussion of datatypes in Chapter 2, a special “null-safe” comparison operator, <=> , returns 1 (TRUE) if the arguments are equal values or if both values are NULL , and 0 (FALSE) otherwise. In other words, 1 <=> 1 returns TRUE (1), 1 <=> NULL returns FALSE (0), and NULL <=> NULL returns TRUE (1). (Remember that the NULL value is not equal to any other value, not even itself.)


TIP
IS [NOT] NULL for testing whether or not a value is NULL , as discussed in Chapter 3. IS NULL can also be written as ISNULL() ; for example, SELECT ISNULL(1); returns 0. You are likely to find that ISNULL() is more portable to and from other databases than IS NULL or the <=> operator. In addition, MySQL supports the IFNULL() and NULLIF() flow-control operators that can be used in connection with null values, as discussed in the “Branching: Making Choices in Queries” section later in this chapter.

There are two ways of writing the inequality comparator. You may use either != or <>; they’re exactly the same so far as MySQL is concerned. The <> notation seems to be more common, and it’s what we prefer to use ourselves, but the choice is entirely up to you. (As always, we recommend you choose one or the other, and stick with it.) Note that NULL compared with any value—even itself—using <> or != yields the null value. However, when performing branching operations, you’re generally checking to see whether a condition is true or not, and since NULL isn’t true, a null result still causes you to follow the “false” branch.

We’ll discuss IF() and the other branching operators later in this chapter.


NOTE 
Both forms of the inequality operator (<> and !=) are standard SQL and supported by most other relational databases.

MySQL also has greater-than, less-than, greater-than-or-equal, and less-than-or-equal operators, which are written (in order) >, <, >=, and <=. These behave more or less just as you would expect. As with the equality and inequality operators, any comparison involving NULL and using one of these operators will always yield a NULL result.


CAUTION  
Some relational databases provide additional comparison operators meaning “not greater than” (!> or />) and “not less than” (!< or /< ). These are not part of the SQL standard and are not currently supported in MySQL.

There’s also a shortcut for determining whether a given value lies within a certain range. The BETWEEN operator is used as shown here.

BETWEEN can also be used with nonnumeric datatypes such as strings. Note that (as usual) string comparisons are case-insensitive unless you employ the BINARY modifier.

Dates as well as strings can be compared.

If the value immediately before the AND is not less than the value following it, then 0 will be always be returned.


NOTE  
There’s an additional operator for use in comparing strings.The LIKE operator allows you to do “fuzzy” matching with wildcards to find strings that are similar to one another. We’ll discuss LIKE in more detail in the “String Functions and Regular Expressions” section later in this chapter.

{mospagebreak title=Operators for Working with Sets}

MySQL also has several operators and functions for use with sets of values. The IN operator answers the question, “Does value N match at least one of the values in the set A, B, C, D…?” Here are some examples:

You should note the following points when using the IN operator:

  • The IN operator returns 1 if a match is found (as in the first and third examples) and 0 if no match is found (as in the second and fourth examples).
  • You can use any datatypes for the value to be found and for the values in the list to be searched, and you can mix datatypes in the list.
  • When trying to match a string, comparisons are case-insensitive, unless you use the BINARY qualifier. (Compare the first two examples.)
  • MySQL will attempt to perform a type conversion between strings and numerals, and vice versa. Hence, the number 5 is matched by the string ‘5’ in the fourth example (but see the rules in the following “Type Conversions in Comparisons” section).

IN can be used in place of (and is basically shorthand for) multiple OR operators in a WHERE clause. For example, this query:

SELECT lastname, dob
FROM members
WHERE firstname IN (‘Bill’, ‘Mary’, ‘George’);

produces the same result as this one:

SELECT lastname, dob
FROM members
WHERE firstname=’Bill’ OR firstname=’Mary’ OR firstname=’George';

The IN operator can also be extremely useful when used with subqueries in MySQL 4.1 and above. Let’s return for a moment to our members table from the “Replacing Program Logic with SQL Logic: A Demonstration” section earlier in this chapter. Suppose that now we would like to know if any members were born in the year 1961. If you’re a programmer, you might have visions of writing some sort of for or while loop that iterates over the dates extracted from a query. Or you might be thinking that you could use something like this to accomplish this task:

SELECT COUNT(*) FROM members WHERE YEAR(dob)=’1961′;

You could do that, but you would be getting your original question answered only in an indirect fashion; you would still need to test the result to see whether the result was greater than zero. By using IN and a subquery, however, you can obtain a Yes or No (actually 1 or 0) answer:

SELECT ‘1961’ IN (SELECT YEAR(dob) FROM members);

The inner SELECT returns a list of years, and the outer SELECT looks for a match in that list. By the way, the inner SELECT must return a single column; otherwise,
MySQL will signal an error.


NOTE  
Subqueries were introduced in MySQL 4.1.We’ll discuss them in more detail in Chapter 8.

In place of IN, you can also use a comparison operator followed by either of the keywords ANY or SOME.

We show an equivalent using the COUNT() function and a comparison operator in the same example. This example can be thought of as saying, “Is there some
member whose first name is Bill? Yes, there is someone in the members list whose first name is Bill.”

The following query and result can be thought of as saying, “There is not any member whose first name is Andy, correct? Yes, that’s correct.”


CAUTION
 Through MySQL 5.0.0, it is not possible to use a LIMIT clause in a subquery following an IN, ANY, or SOME clause.

You can use any of the comparison operators =, !=, <>, <, <=, >, and >= with ANY or SOME. (You cannot use <=>.) Here’s an example:

This is equivalent to asking the question, “Do we have any members who were born in the year 1922 or earlier?” and getting the answer, “No.”

Finally, you can find the greatest and least values in a set by using GREATEST() and LEAST(). They’re used in a similar fashion: each accepts a comma-delimited set of values and returns the largest or smallest value from the set. The following is an example of using both of these functions.

Note that since we’ve mixed integer and decimal values, the result is expressed as a decimal with a precision equal to that of the argument with the greatest number of decimals.


NOTE
 The order in which arguments are passed to GREATEST() and LEAST() has no effect on the result.

You can also use the GREATEST() and LEAST() functions with strings:

Both functions are case-insensitive. Although you can use the BINARY modifier with either one of them without generating any errors, doing so has no effect on the result.

It’s possible to use both numeric and string arguments in one call to GREATEST() or LEAST(), but all of the strings are converted to 0.


CAUTION
 In older versions of MySQL (prior to 3.23), it was possible to use MAX() and MIN() as synonyms for GREATEST() and LEAST(), and you may see this usage in older references. This is no longer the case, and attempting to use one of these functions on a set will cause an error.

Finally, there are two functions for working with sets of values: ELT() and FIELD(), which complement one another. ELT() is used to retrieve the value of the element at the specified index within a set. FIELD() finds the position or index of a given value within a set. Each takes as parameters a set of values, all of which except the first make up a list to be tested.

For ELT(), the first element in the set is the index of the value to be retrieved.

The first argument used with ELT() must be an integer; if it is less than 1 or greater than the number of elements in the list, the function returns NULL. Note that using 1 for the first argument will return the first element in the list that follows it. You can think of this function as working like an array index does in most programming languages.

The FIELD() function attempts to match the first argument in the list that follows it. Here, too, the numbering of the list starts with 1.

Note that these two functions are usually employed with lists of strings, but it’s also possible to use them with numbers, as shown in the next example.

As you can likely deduce from these examples, the value used for the first argument to ELT() can be a string, as long as that string contains only digits, and the first argument to FIELD() can be a number.

{mospagebreak title=Type Conversions in Comparisons}

Here are few points to keep in mind when comparing values of different types using any of the comparison operators =, <>, !=, >, >=, <, and <=:

  • Comparing any value with the null value returns NULL .
  • String comparisons are case-insensitive unless you use the BINARY qualifier in the query or in the definition of the column whose value is being compared: ‘a’ = ‘A’ returns 1 (Boolean TRUE), and BINARY ‘a’ = ‘A’ returns 0 (Boolean FALSE).
  • When comparing a string with a number, any leading spaces in the string are discarded, then any leading digits in the string that remains are converted to a number and any remaining (trailing) characters are dropped, so both 5 < ‘8a’ and ‘ 15x’ = 15 return 1. If the string does not begin with a digit, the string is evaluated as 0, so both the expressions 5 < ‘a8′ and ‘ x15′ = 15 return 0. And 3.0 >= ‘2f5′ evaluates as TRUE (returns 1) and 14 < ‘311mft7′ is FALSE.
  • When comparing strings and numbers, if the string (after any leading spaces are dropped) begins with one or more digits followed by a decimal point and then by one or more digits, any additional characters following the second set of digits are dropped; for example, all three of the expressions ‘3.28’ = 3.28, ‘3.28.6’ = 3.28, and 3.28 = ‘3.28x’ will evaluate to TRUE (1).

Operator Precedence

When multiple operators are involved in a single expression, things can get a bit complicated. For example, how should MySQL resolve an expression like 2 + 3 AND 1? Should this evaluate to 3 (3 AND 1 evaluates to 1; 2 + 1 is equal to 3) or to 1 (2 + 3 is equal to 5; 5 AND 1 evaluates as 1)? Of course, you can test this directly, as shown here:

However, it isn’t really practical to take the time to do this every time you encounter such an expression, not to mention the fact that, in real life, such expressions are likely to be working with values that aren’t known ahead of time. Fortunately, there’s a set order of precedence that determines which operations are to be done first.

The order of precedence for operators in MySQL is almost identical to that found in ANSI SQL. Table 4-2 shows the order of precedence from highest to lowest.

Table 4-2. MySQL Operator Precedence

OPERATOR ( )

DESCRIPTION Parentheses (force grouping)

+, -; ~

Unary plus and minus; bitwise inverse

*, /, % / MOD() / MOD, DIV

Multiplication and division

+, -

Addition and subtraction

<=>

Null-safe comparison

=, <>, !=, >=, <=

Comparison

&, |, ^

Bitwise AND, OR, and exclusive OR (XOR)

NOT / !

Logical negation

AND / &&

Logical AND

OR / ||; IN, SOME, ANY; LIKE

Logical OR; set membership; “fuzzy” (wildcard) comparison

<<, >>

Bitwise shift

=

Variable assignment

If you want operations to be performed in some other order, you can always force the issue by using parentheses.

When evaluating complex expressions involving parentheses, MySQL follows what’s sometimes known as the “inside-outside” rule: operations inside the innermost set(s) of parentheses are performed first, followed by the next set outward, and then by the next set outward from that, and so on. If you’ve written code in practically any other modern programming or scripting language, you should be used to this already.

Please check back next week for the second part of this article.

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

chat sex hikayeleri Ensest hikaye