Chapter 11. Functions and Operators

Table of Contents

11.1. Function and Operator Reference
11.2. Type Conversion in Expression Evaluation
11.3. Operators
11.3.1. Operator Precedence
11.3.2. Comparison Functions and Operators
11.3.3. Logical Operators
11.3.4. Assignment Operators
11.4. Control Flow Functions
11.5. String Functions
11.5.1. String Comparison Functions
11.5.2. Regular Expressions
11.6. Numeric Functions and Operators
11.6.1. Arithmetic Operators
11.6.2. Mathematical Functions
11.7. Date and Time Functions
11.8. What Calendar Is Used By MySQL?
11.9. Full-Text Search Functions
11.9.1. Natural Language Full-Text Searches
11.9.2. Boolean Full-Text Searches
11.9.3. Full-Text Searches with Query Expansion
11.9.4. Full-Text Stopwords
11.9.5. Full-Text Restrictions
11.9.6. Fine-Tuning MySQL Full-Text Search
11.9.7. Adding a Collation for Full-Text Indexing
11.10. Cast Functions and Operators
11.11. XML Functions
11.12. Bit Functions
11.13. Encryption and Compression Functions
11.14. Information Functions
11.15. Miscellaneous Functions
11.16. Functions and Modifiers for Use with GROUP BY Clauses
11.16.1. GROUP BY (Aggregate) Functions
11.16.2. GROUP BY Modifiers
11.16.3. GROUP BY and HAVING with Hidden Columns
11.17. Spatial Extensions
11.17.1. Introduction to MySQL Spatial Support
11.17.2. The OpenGIS Geometry Model
11.17.3. Supported Spatial Data Formats
11.17.4. Creating a Spatially Enabled MySQL Database
11.17.5. Analyzing Spatial Information
11.17.6. Optimizing Spatial Analysis
11.17.7. MySQL Conformance and Compatibility
11.18. Precision Math
11.18.1. Types of Numeric Values
11.18.2. DECIMAL Data Type Changes
11.18.3. Expression Handling
11.18.4. Rounding Behavior
11.18.5. Precision Math Examples

Expressions can be used at several points in SQL statements, such as in the ORDER BY or HAVING clauses of SELECT statements, in the WHERE clause of a SELECT, DELETE, or UPDATE statement, or in SET statements. Expressions can be written using literal values, column values, NULL, built-in functions, stored functions, user-defined functions, and operators. This chapter describes the functions and operators that are permitted for writing expressions in MySQL. Instructions for writing stored functions and user-defined functions are given in Section 19.2, “Using Stored Routines (Procedures and Functions)”, and Section 23.3, “Adding New Functions to MySQL”. See Section 8.2.4, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for a particular function or operator.


By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

You can tell the MySQL server to accept spaces after function names by starting it with the --sql-mode=IGNORE_SPACE option. (See Section 5.1.7, “Server SQL Modes”.) Individual client programs can request this behavior by using the CLIENT_IGNORE_SPACE option for mysql_real_connect(). In either case, all function names become reserved words.

For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Rather than showing examples in this format:

mysql> SELECT MOD(29,9);
| mod(29,9) |
|         2 |
1 rows in set (0.00 sec)

This format is used instead:

mysql> SELECT MOD(29,9);
        -> 2
Copyright © 2010-2024 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout