Home arrow MySQL arrow Page 6 - Optimizing Queries with Operators, Branching and Functions

Type Conversions in Comparisons - MySQL

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

TABLE OF CONTENTS:
  1. Optimizing Queries with Operators, Branching and Functions
  2. Optimization 1: Separation of Logic and Formatting
  3. MySQL Operators
  4. Type Conversions with Logical and Arithmetic Operators
  5. Operators for Working with Sets
  6. Type Conversions in Comparisons
By: Apress Publishing
Rating: starstarstarstarstar / 13
March 30, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- 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: