MySQL Functions

MySQL functions give you the ability to perform data manipulation and aggregation at the server before the final result set of a query is returned to the client or manipulated by the server. There are many functions available for you to use, but we will hit only those that you will probably need in a typical web application with MySQL.

For a more complete reference to MySQL functions and syntax see the online documentation available at www.mysql.com.

Grouping Functions

Functions in this category allow you to return results based on aggregating a set of rows in the database. This would include finding the total number of rows in the result set (COUNT), determining the highest ordinal message number in a table of forum postings (MAX), and calculating the average number of messages posted by forum contributors (AVG).

AVG(expression)

AVG calculates and returns the average value based on all the values of a particular field in a set of rows. This is useful for determining things like the average sale amount for an online store application.

mysql> SELECT AVG(total_sale) FROM purchases; +-----------------+ | AVG(total_sale) | +-----------------| |           24.50 | +-----------------+

MIN(expression)

MIN finds the smallest value of a set of possible values. In a web application involving a discussion forum, this function might be used to find the earliest post in a particular forum.

mysql> SELECT MIN(post_date) FROM forum_messages;  +---------------------+  | MIN(post_date)      |  +---------------------+  | 2002-03-14 12:39:31 |  +---------------------+

MAX(expression)

MAX finds the largest value of a set of possible values. As noted earlier, you might use MAX to find the largest message ID in a table containing all message posts to a particular forum.

mysql> SELECT MAX(message_id) FROM forum_messages;  +-----------------+ | MAX(message_id) | +-----------------+ |           13768 | +-----------------+

SUM(expression)

SUM adds all of the values in a set of values and returns the result. This could be useful for determining the total sales in an online store application as in the following.

mysql> SELECT SUM(total_sale) FROM purchases;  +-----------------+ | SUM(total_sale) | +-----------------+ |         9412.00 | +-----------------+

COUNT(expression)

COUNT returns a count of values in a set of rows. In order to quickly determine the total number of records in a table you could use COUNT(*). Another variation of COUNT is to count only unique occurrences of values in a set of rows. You would accomplish this by prefacing the expression with the keyword DISTINCT. For example, to determine the number of unique message posters in a table containing all messages and the user ID of the message poster, the following would give you the number of unique contributors of messages.

mysql> SELECT COUNT(DISTINCT poster_id)  FROM forum_messages;  +---------------------------+  | COUNT(DISTINCT poster_id) |  +---------------------------+  |                         5 |  +---------------------------+

Numeric Functions

Functions in this category perform arithmetic operations within the MySQL server and return the result as part of the SQL statement. There are many more Numeric functions than are covered here. These are the more common functions you may need in your application.

ABS(expression)

ABS returns the absolute value of a number. This has the effect of removing the sign from negative numbers and returning positive numbers unchanged.

mysql> SELECT ABS(-3), ABS(4);  +---------+--------+  | ABS(-3) | ABS(4) |  +---------+--------+  |       3 |      4 |  +---------+--------+

ROUND(expression,precision)

ROUND returns expression rounded to the nearest integer or to the specified precision (number of decimal places). The precision parameter is optional and will be assumed to be 0 if it is not specified.

mysql> SELECT ROUND(1.45), ROUND(1.55);  +-------------+-------------+  | ROUND(1.45) | ROUND(1.55) |  +-------------+-------------+  |           1 |           2 |  +-------------+-------------+ mysql> SELECT ROUND(123.456,1), ROUND(123.456,2);  +------------------+------------------+  | ROUND(123.456,1) | ROUND(123.456,2) |  +------------------+------------------+  |            123.5 |           123.46 |  +------------------+------------------+

TRUNCATE(expression,precision)

TRUNCATE returns expression truncated to the specified precision (number of decimal places). To truncate the entire decimal portion, specify 0 as the precision. This function differs from ROUND in that it simply cuts off any decimal portion larger than the specified precision

.mysql> SELECT TRUNCATE(1.45,0), TRUNCATE(1.55,0);  +------------------+------------------+  | TRUNCATE(1.45,0) | TRUNCATE(1.55,0) |  +------------------+------------------+  |                1 |                1 |  +------------------+------------------+  mysql> SELECT TRUNCATE(123.456,1), TRUNCATE(123.456,2);  +---------------------+---------------------+  | TRUNCATE(123.456,1) | TRUNCATE(123.456,2) |  +---------------------+---------------------+  |               123.4 |              123.45 |  +---------------------+---------------------+

String Functions

These functions allow you to manipulate string data at the MySQL server prior to your application receiving it (for SELECT statements) or prior to MySQL storing the value (for INSERT or UPDATE statements). Again, the available functions for string manipulation are numerous, so we'll just cover a few that you may find useful.

CONCAT(string1,string2,…)

CONCAT returns the string resulting from combining the argument strings. A typical use of this function is to combine parts of a person's name stored in separate fields in to a single full name value.

mysql> SELECT CONCAT('John', ' ', 'Doe');  +----------------------------+  | CONCAT('John', ' ', 'Doe') |  +----------------------------+  |  John Doe                  |  +----------------------------+

INSTR(string,findstring)

INSTR returns the starting position of the string findstring within string or 0 if the string findstring is not found within string.

mysql> SELECT INSTR('abcdef', 'ef'), INSTR('abcdef', 'yz');  +-----------------------+-----------------------+  | INSTR('abcdef', 'ef') | INSTR('abcdef', 'yz') |  +-----------------------+-----------------------+  |                     5 |                     0 |  +-----------------------+-----------------------+

LCASE(string) / LOWER(string)

Both LCASE and LOWER change every character in the argument to lowercase.

mysql> SELECT LCASE('Macromedia Dreamweaver MX');  +------------------------------------+  | LCASE('Macromedia Dreamweaver MX') |  +------------------------------------+  | macromedia dreamweaver mx          |  +------------------------------------+

UCASE(string) / UPPER (string)

Both UCASE and UPPER change every character in the argument to uppercase.

mysql> SELECT UCASE('Macromedia Dreamweaver MX');  +------------------------------------+  | UCASE('Macromedia Dreamweaver MX') |  +------------------------------------+  | MACROMEDIA DREAMWEAVER MX          |  +------------------------------------+

LEFT(string,count)

LEFT returns count characters from the beginning of string.

mysql> SELECT LEFT('Dreamweaver',5);  +-----------------------+  | LEFT('Dreamweaver',5) |  +-----------------------+  | Dream                 |  +-----------------------+

RIGHT(string,count)

RIGHT returns count characters from the end of string.

mysql> SELECT RIGHT('Dreamweaver',2);  +------------------------+  | RIGHT('Dreamweaver',2) |  +------------------------+  | er                     |  +------------------------+

TRIM(string)

TRIM returns string with leading and trailing spaces removed. There are variations of TRIM that allow you to specify explicitly what character or string to remove. Additionally, you can control whether TRIM affects characters at the beginning or end of the string or both. You can also use LTRIM to remove leading spaces from a string and RTRIM to remove trailing spaces from a string.

mysql> SELECT TRIM(' Dreamweaver ');  +---------------------------+  | TRIM(' Dreamweaver ')     |  +---------------------------+  | Dreamweaver               |  +---------------------------+  mysql> SELECT TRIM(LEADING '*' FROM '***Dreamweaver');  +-----------------------------------------+  | TRIM(LEADING '*' FROM '***Dreamweaver') |  +-----------------------------------------+  | Dreamweaver                             | +-----------------------------------------+

Date and Time Functions

One of the more common aspects of web applications will involve manipulating dates to determine things like new articles since the last visit by a user, recently modified links within a site, and for setting expiration dates on user submissions.

NOW()

NOW returns the current system date and time of the MySQL server. This is useful for recording events such as when a row in the database was last changed or the specific time a web user began a session with the web server.

mysql> SELECT NOW();  +---------------------+  | NOW()               |  +---------------------+  | 2002-08-31 10:23:40 |  +---------------------+

DAYOFWEEK(date)

DAYOFWEEK returns an index that can be used to determine the day of the week that a particular date represents. The return value is an integer from 1 through 7 where 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday and 7 = Saturday. For example to determine that the 4th of July 2002 fell on a Thursday, we would use the following

mysql> SELECT DAYOFWEEK('2002-07-04');  +-------------------------+  | DAYOFWEEK('2002-07-04') |  +-------------------------+  |                       5 |  +-------------------------+

Warning 

There is another function in MySQL for determining the day of the week from a date called WEEKDAY. Functionally, it is the same as DAYOFWEEK, the difference is that it uses a different indexing scheme to represent the days of the week. The index values returned by DAYOFWEEK correspond to the ODBC standard. The index values returned by WEEKDAY are integer values between 0 and 6 such that 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday and 6 = Sunday.

DAYOFYEAR(date)

DAYOFYEAR returns the number of the day within the year represented by the date argument. The return value is in the range 1 through 366.

mysql> SELECT DAYOFYEAR('2002-08-30');  +-------------------------+  | DAYOFYEAR('2002-08-30') |  +-------------------------+  |                     242 |  +-------------------------+

DAYNAME(date)

DAYNAME returns the name of the day represented by the date argument.

mysql> SELECT DAYNAME('2002-08-30');  +-----------------------+  | DAYNAME('2002-08-30') |  +-----------------------+  | Friday                |  +-----------------------+

MONTHNAME(date)

MONTHNAME returns the name of the month represented by the date argument.

MYSQL SYNTAX 153mysql> SELECT MONTHNAME ('2002-08-30');  +-------------------------+  | MONTHNAME('2002-08-30') |  +-------------------------+  | August                  |  +-------------------------+