MySQL Functions

Preformatting the results returned by a query makes your data more usable and can cut down on the amount of programming interface required. To do so, you make use of MySQL's built-in functions, first introduced in Chapter 6, "MySQL Functions." Of course, you can also use functions in your clauses to limit what records are returned, to group them, and more.

Table B.10 shows the functions used with strings. Table B.11 has most, but not all, of the number-based functions. Table B.12 lists date-related functions. Table B.13 has the formatting parameters for the DATE_FORMAT() and TIME_FORMAT() functions. Table B.14 covers the aggregate or grouping functions. Table B.15 discusses most of the encryption functions and Table B.16 is the catchall for miscellaneous functions.

Table B.10. These functions are used for manipulating string values, in columns or otherwise.

Text Functions
Function and Usage	Purpose
FIND_IN_SET(string, set)	Returns a positive number if string is found in set; returns 0 otherwise.
LEFT(string, x)	Returns the leftmost x characters from a string.
LENGTH(string)	Returns the length of the string.
LOCATE(substring, string)	Returns the first instance of substring in string, if applicable.
LOWER(string)	Turns the string into an all-lowercase format.
LTRIM(string)	Trims excess spaces from the beginning of the string.
REPLACE(string, find, replace)	Returns the string with every instance of find substituted by replace.
RIGHT(string, x)	Returns the rightmost x characters from a string.
RTRIM(string)	Trims excess spaces from the end of the stored string.
STRCMP(string1, string2)	Returns 0 if the strings are the same, 1 or 1 otherwise.
SUBSTRING(string, start, length)	Returns length characters from string beginning with start (indexed from 1).
trIM(string)	Trims excess spaces from the beginning and end of the string.
UPPER(string)	Capitalizes the entire string.

Table B.11. Here are some of the numeric functions MySQL has, excluding the trigonometric and more esoteric ones.

Numeric Functions
Function and Usage	Purpose
ABS(num)	Returns the absolute value of num.
CEILING(num)	Returns the next-highest integer based upon the value of num.
FLOOR(num)	Returns the integer value of num.
FORMAT(num, y)	Returns num formatted as a number with y decimal places and commas inserted every three spaces.
GREATEST(num1, num2, num3...)	Returns the greatest value from a list.
LEAST(num1, num2, num3...)	Returns the smallest value from a list.
MOD(x, y)	Returns the remainder of dividing x by y (either or both can be a column).
POW(x, y)	Returns the value of x to the y power.
RAND()	Returns a random number between 0 and 1.0.
ROUND(x, y)	Returns the number x rounded to y decimal places.
SIGN(num)	Returns a value indicating whether num is negative (1), zero (0), or positive (+1).
SQRT(num)	Calculates the square root of num.

Table B.12. MySQL uses several different functions for working with dates and times in your databases. In the usage examples, dt could represent a date, a time, or a datetime.

Date and Time Functions
Function and Usage	Purpose
HOUR(dt)	Returns just the hour value of dt.
MINUTE(dt)	Returns just the minute value of dt.
SECOND(dt)	Returns just the second value of dt.
DATE(dt)	Returns just the date value of dt.
DAYNAME(dt)	Returns the name of the day of dt.
DAYOFMONTH(dt)	Returns just the numerical day of dt.
MONTHNAME(dt)	Returns the name of the month of dt.
MONTH(dt)	Returns just the numerical month value of dt.
YEAR(dt)	Returns just the year value of dt.
DATE_ADD(dt, INTERVAL x type)	Returns the value of x units added to dt.
DATE_SUB(dt, INTERVAL x type)	Returns the value of x units subtracted from dt.
ADDTIME(dt, t)	Returns the value of t time added to dt.
SUBTIME(dt, t)	Returns the value of t time subtracted from dt.
DATEDIFF(dt, dt)	Returns the number of days between the two dates.
TIMEDIFF(dt, dt)	Returns the time difference between the dates or dates and times.
CONVERT_TZ(dt, from_zone, to_zone)	Converts dt from one time zone to another.
CURDATE()	Returns the current date.
CURTIME()	Returns the current time.
NOW()	Returns the current date and time.
UNIX_TIMESTAMP(dt)	Returns the number of seconds since the epoch or since the date specified.

Table B.13. The DATE_FORMAT() and TIME_FORMAT() functions make use of these special characters to format a date or time.

DATE_FORMAT() and TIME_FORMAT() Parameters
Term	Meaning	Example
%e	Day of the month	131
%d	Day of the month, two digit	0131
%D	Day with suffix	1st31st
%W	Weekday name	SundaySaturday
%a	Abbreviated weekday name	SunSat
%c	Month number	112
%m	Month number, two digit	0112
%M	Month name	JanuaryDecember
%b	Month name, abbreviated	JanDec
%Y	Year	2002
%y	Year	02
%l	Hour	112
%h	Hour, two-digit	0112
%k	Hour, 24-hour clock	023
%H	Hour, 24-hour clock, two-digit	0023
%i	Minutes	0059
%S	Seconds	0059
%r	Time	8:17:02 PM
%T	Time, 24-hour clock	20:17:02
%p	AM or PM	AM or PM

Table B.14. The grouping functions are frequently tied to a GROUP BY clause to aggregate values in a column.

Grouping Functions
Function and Usage	Purpose
AVG(column)	Returns the average of the values of the column.
COUNT(column)	Counts the number of rows.
COUNT(DISTINCT column)	Counts the number of distinct column values.
GROUP_CONCAT(values)	Returns a concatenation of the grouped values.
MIN(column)	Returns the smallest value from the column.
MAX(column)	Returns the largest value from the column.
SUM(column)	Returns the sum of all of the values in the column.

Table B.15. Different encryption functions are available as of new releases of MySQL, so know what version you are using!

Encryption Functions
Function	Version	Notes
MD5()	3.23.2	Returns a 32-digit hash.
SHA1()	4.0.2	Returns a 40-digit hash.
AES_ENCRYPT()	4.0.2	Encrypts data using AES algorithm.
AES_DECRYPT ()	4.0.2	Decrypts AES_ENCRYPT() data.
ENCODE()	3.x	Older encryption function.
DECODE()	3.x	Decrypts ENCODE() data.
DES_ENCRYPT()	4.0.1	Encrypts data using DES algorithm, requires SSL.
DES_DECRYPT ()	4.0.1	Decrypts DES_ENCRYPT() data.
ENCRYPT()	3.x	May not be available on Windows; no decryption possible.
PASSWORD()	3.x	Used by the mysql database; do not use yourself.

Table B.16. These various functions are for everything from encryption to concatenation.

Other Functions
Function and Usage	Purpose
CONAT(column1, ' - ', column2)	Combines the elements in parentheses into one string.
CONCAT_WS(' - ', column1, column2)	Combines the elements with the one common separator.
DATABASE()	Returns the name of the database currently being used.
LAST_INSERT_ID()	Returns the previous auto-incremented value.
USER()	Returns the name of the user of the current session.

Most every function can be applied either to the value retrieved from a column or to a literal one:

SELECT ROUND (3.142857, 2)
SELECT ROUND (columnname) FROM tablename