Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 37.4Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.6Kb
Man Pages (Zip) - 329.3Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Functions and Operators

Chapter 12 Functions and Operators

Table of Contents     [+/-]

12.1 Function and Operator Reference
12.2 Type Conversion in Expression Evaluation
12.3 Operators     [+/-]
12.4 Control Flow Functions
12.5 String Functions     [+/-]
12.6 Numeric Functions and Operators     [+/-]
12.7 Date and Time Functions
12.8 What Calendar Is Used By MySQL?
12.9 Full-Text Search Functions     [+/-]
12.10 Cast Functions and Operators
12.11 XML Functions
12.12 Bit Functions and Operators
12.13 Encryption and Compression Functions
12.14 Information Functions
12.15 Spatial Analysis Functions     [+/-]
12.16 JSON Functions     [+/-]
12.17 Functions Used with Global Transaction IDs
12.18 MySQL Enterprise Encryption Functions     [+/-]
12.19 Aggregate (GROUP BY) Functions     [+/-]
12.20 Miscellaneous Functions
12.21 Precision Math     [+/-]

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 23.2, “Using Stored Routines (Procedures and Functions)”, and Section 28.4, “Adding New Functions to MySQL”. See Section 9.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.8, “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

User Comments
  Posted by Adrian Corston on December 5, 2005
In the absence of a "delta" function to find the change in value for a column from one row to the next, I found this solution using a mysql variable:

mysql> create temporary table t (idx int, val int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values(1, 100), (2, 120), (3, 95), (4, 200);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> set @v = 0; select val - @v delta, @v := val val from t order by idx;
Query OK, 0 rows affected (0.00 sec)

| delta | val |
| 100 | 100 |
| 20 | 120 |
| -25 | 95 |
| 105 | 200 |
4 rows in set (0.00 sec)

Don't swap the argument order on the select - the assignment must come last. And don't forget to prime your variable before the select or it won't have the right type (ie. numeric in this case.)

This is particularly useful with unix timestamps to work out the elapsed seconds between records.
  Posted by Nithiya nandam on July 3, 2006
You can extract multiple column values in a single column


SELECT IF(First_name LIKE '%mathew%',First_name,Last_name) FROM user_tbl

It will work perfectly and allows us to choose a dynamic column.

Bye Bye
Sign Up Login You must be logged in to post a comment.