PHP & MySQL: Novice to Ninja, 5th Edition (2012)

Appendix C. MySQL Functions

MySQL provides a sizeable library of functions to format and combine data within SQL queries in order to produce the desired results in the preferred format. This appendix provides a reference to the most useful of these functions, as implemented in MySQL as of version 5.5.22 (current this writing). For a complete, up-to-date reference to supported SQL functions, see the MySQL Reference Manual.

Control Flow Functions

IFNULL(expr1, expr2)

This function returns expr1 unless it’s NULL, in which case it returns expr2.

NULLIF(expr1, expr2)

This function returns expr1 unless it equals expr2, in which case it returns NULL.

IF(expr1, expr2, expr3)

If expr1 is TRUE (that is, not NULL or 0), this function returns expr2; otherwise, it returns expr3.

CASE value WHEN [ compare-value1 ] THEN result1 [WHEN …] [ELSE else- result ] END

This function returns result1 when value=compare-value1 (note that several compare-value/result pairs can be defined); otherwise, it returns else-result , or NULL if none is defined.

CASE WHEN [ condition1 ] THEN result1 [WHEN …] [ELSE else-result ] END

This function returns result1 when condition1 is TRUE (note that several condition/result pairs can be defined); otherwise, it returns else-result, or NULL if none is defined.

Mathematical Functions

ABS(expr)

This function returns the absolute (positive) value of expr.

SIGN(expr)

This function returns -1, 0, or 1 depending on whether expr is negative, zero, or positive, respectively.

MOD(expr1, expr2) , expr1 % expr2

This function returns the remainder of dividing expr1 by expr2.

FLOOR(expr)

This function rounds down expr (that is, it returns the largest integer value that is less than or equal to expr).

CEILING(expr) , CEIL(expr)

This function rounds up expr (that is, it returns the smallest integer value that’s greater than or equal to expr).

ROUND(expr)

This function returns expr rounded to the nearest integer. Note that this function’s behavior when the value is exactly an integer plus 0.5 is system-dependent. Thus, you should avoid relying on any particular outcome when migrating to a new system.

ROUND(expr, num)

This function rounds expr to a number with num decimal places, leaving trailing zeros in place. Use a num of 2, for example, to format a number as dollars and cents. Note that the same uncertainty about the rounding of 0.5 applies as discussed for ROUND.

EXP(expr)

This function returns e ^expr , the base of natural logarithms raised to the power of expr.

LOG(expr)

This function returns ln(expr), or log_e(expr), the natural logarithm of expr.

LOG(B, expr)

This function returns the logarithm of expr with the arbitrary base B.

Note: From High School Mathematics …

LOG(B, expr) = LOG(expr) / LOG(B)

LOG10(expr)

This function returns the base-10 logarithm of expr.

POW(expr1, expr2) , POWER(expr1, expr2)

This function returns expr1 raised to the power of expr2.

SQRT(expr)

This function returns the square root of expr.

PI()

This function returns the value of π (pi).

COS(expr)

This function returns the cosine of expr in radians (for example, COS(PI()) = -1).

SIN(expr)

This function returns the sine of expr in radians (for example, SIN(PI()) = 0).

TAN(expr)

This function returns the tangent of expr in radians (for example, TAN(PI()) = 0).

ACOS(expr)

This function returns the arc cosine (cos^-1 or inverse cosine) of expr (for example, ACOS(-1) = PI()).

ASIN(expr)

This function returns the arc sine (sin^-1 or inverse sine) of expr (for example, ASIN(0) = PI()).

ATAN(expr)

This function returns the arc tangent (tan^-1 or inverse tangent) of expr (for example, ATAN(0) = PI()).

ATAN(y, x) , ATAN2(y, x)

This function returns the angle (in radians) made at the origin between the positive x axis and the point (x,y); for example, ATAN(1, 0) = PI() / 2.

COT(expr)

This function returns the cotangent of expr (for example, COT(PI() / 2) = 0).

RAND() , RAND(expr)

This function returns a random, floating point number between 0.0 and 1.0. If expr is specified, a random number will be generated based on that value, which will always be the same.

LEAST(expr1, expr2, …)

This function returns the smallest of the values specified.

GREATEST(expr1, expr2, …)

This function returns the largest of the values specified.

DEGREES(expr)

This function returns the value of expr (in radians) in degrees.

RADIANS(expr)

This function returns the value of expr (in degrees) in radians.

TRUNCATE(expr, num)

This function returns the value of floating point number expr truncated to num decimal places (that is, rounded down).

BIN(expr)

This function converts decimal expr to binary, equivalent to CONV(expr, 10, 2).

OCT(expr)

This function converts decimal expr to octal, equivalent to CONV(expr, 10, 8).

HEX(expr)

This function converts decimal expr to hexadecimal, equivalent to CONV(expr, 10, 16).

CONV(expr, from_base, to_base)

This function converts a number (expr) in base from_base to a number in base to_base. Returns NULL if any of the arguments are NULL.

String Functions

ASCII(str)

This function returns the ASCII code value of the left-most character in str, 0 if str is an empty string, or NULL if str is NULL.

ORD(str)

This function returns the ASCII code of the left-most character in str, taking into account the possibility that it might be a multibyte character.

CHAR(expr, …)

This function creates a string composed of characters, the ASCII code values of which are given by the expressions passed as arguments.

CONCAT(str1, str2, …)

This function returns a string made up of the strings passed as arguments joined end to end. If any of the arguments are NULL, NULL is returned instead.

CONCAT_WS(separator, str1, str2, …)

This is CONCAT “with separator” (WS). This function is the same as CONCAT, except that the first argument is placed between each of the additional arguments when they’re combined.

LENGTH(str) , OCTET_LENGTH(str) , CHAR_LENGTH(str) , CHARACTER_LENGTH(str)

All of these return the length in characters of str. CHAR_LENGTH and CHARACTER_LENGTH, however, take multibyte characters into consideration when performing the count.

BIT_LENGTH(str)

This function returns the length (in bits) of str (that is, BIT_LENGTH(str) = 8 * LENGTH(str)).

LOCATE(substr, str) , POSITION(substr IN str)

This function returns the position of the first occurrence of substr in str (1 if it occurs at the beginning, 2 if it starts after one character, and so on). It returns 0 if substr doesn’t occur in str.

LOCATE(substr, str, pos)

This is the same as LOCATE(substr, str), but it begins searching from character number pos.

INSTR(str, substr)

This function is the same as LOCATE(substr, str), but with the argument order swapped.

LPAD(str, len, padstr)

This function shortens or lengthens str so that it’s of length len. Lengthening is accomplished by inserting padstr to the left of the characters of str (for example, LPAD('!', '5', '.') = '....!').

RPAD(str, len, padstr)

This function shortens or lengthens str so that it’s of length len. Lengthening is accomplished by inserting padstr to the right of the characters of str (for example, RPAD('!','5','.') = '!....').

LEFT(str, len)

This function returns the left-most len characters of str. If str is shorter than len characters, str is returned with no extra padding.

RIGHT(str, len)

This function returns the right-most len characters of str. If str is shorter than len characters, str is returned with no extra padding.

SUBSTRING(str, pos, len) , SUBSTRING(str FROM pos FOR len) , MID(str, pos, len)

This function returns a string up to len characters long taken from str beginning at position pos (where 1 is the first character). The second form of SUBSTRING is the ANSI standard.

SUBSTRING(str, pos) , SUBSTRING(str FROM pos)

This function returns the string beginning from position pos in str (where 1 is the first character) and going to the end of str.

SUBSTRING_INDEX(str, delim, count)

MySQL counts count occurrences of delim in str, then takes the substring from that point. If count is positive, MySQL counts to the right from the start of the string, then takes the substring up to but not including that delimiter. If count is negative, MySQL counts to the left from the end of the string, then takes the substring that starts right after that delimiter, and runs to the end of str.

LTRIM(str)

This function returns str with any leading whitespace trimmed off.

RTRIM(str)

This function returns str with any trailing whitespace trimmed off.

TRIM([ [BOTH | LEADING | TRAILING] [ remstr ] FROM] str)

This function returns str with either whitespace (by default) or occurrences of the string remstr removed from the start of the string (LEADING), end of the string (TRAILING), or both (BOTH, the default).

SOUNDEX(str)

This function produces a string that represents how str sounds when read aloud. Words that sound similar should have the same “soundex string.” For example:

SOUNDEX("tire") = "T600"

SOUNDEX("tyre") = "T600"

SOUNDEX("terror") = "T600"

SOUNDEX("tyrannosaur") = "T6526"

SPACE(num)

This function returns a string of num space characters.

REPLACE(str, from_str, to_str)

This function returns str after replacing all occurrences of from_str with to_str.

REPEAT(str, count)

This function returns a string made up of str repeated count times, an empty string if count <= 0, or NULL if either argument is NULL.

REVERSE(str)

This function returns str spelled backwards.

INSERT(str, pos, len, newstr)

This function takes str, and removes the substring beginning at pos (where 1 is the first character in the string) with length len, then inserts newstr at that position. If len = 0, the function simply inserts newstr at position pos.

ELT(N, str1, str2, str3, …)

This function returns the N ^th string argument (str1 if N = 1, str2 if N = 2, and so on), or NULL if there’s no argument for the given N.

FIELD(str, str1, str2, str3, …)

This function returns the position of str in the subsequent list of arguments (1 if str = str1, 2 if str = str2, and so on).

FIND_IN_SET(str, strlist)

When strlist is a list of strings of the form 'string1,string2,string3,…', this function returns the index of st****r in that list, or 0 if str is not in the list. This function is ideally suited (and optimized) for determining if str is selected in a column of type SET (see Appendix D).

MAKE_SET(bits, str1, str2, …)

This function returns a list of strings of the form 'string1,string2,string3,…' using the string parameters ( str1 , str2 , and so on) that correspond to the bits that are set in the number bits. For example, if bits = 10 (binary 1010), bits 2 and 4 are set, so the output of MAKE_SET will be'str2,str4'.

EXPORT_SET(bits, on_str, off_str[, separator[, number_of_bits]])

This function returns a string representation of which bits are—and are not—set in bits. Set bits are represented by the on_str string, while unset bits are represented by the off_str string. By default, these bit representations are comma-separated, but the optional separator string lets you define your own. By default, up to 64 bits are read; however, number_of_bits lets you specify that a smaller number be read. For example:

EXPORT_SET(10, 'Y', 'N', ',', 6) = 'N,Y,N,Y,N,N'

LCASE(str) , LOWER(str)

This function returns str with all letters in lowercase.

UCASE(str) , UPPER(str)

This function returns str with all letters in uppercase.

LOAD_FILE(filename)

This function returns the contents of the file specified by filename (an absolute path to a file readable by MySQL). Your MySQL user should also have file privileges.

QUOTE(str)

This function returns str surrounded by single quotes, and with any special characters escaped with backslashes. If str is NULL, the function returns the string NULL (without surrounding quotes).

Date and Time Functions

DAYOFWEEK(date)

This function returns the weekday of date in the form of an integer, according to the ODBC standard (1 = Sunday, 2 = Monday, 3 = Tuesday … 7 = Saturday).

WEEKDAY(date)

This function returns the weekday of date in the form of an integer (0 = Monday, 1 = Tuesday, 2 = Wednesday … 6 = Sunday).

DAYOFMONTH(date)

This function returns the day of the month for date, from 1 to 31.

DAYOFYEAR(date)

This function returns the day of the year for date, from 1 to 366—remember leap years!

MONTH(date)

This function returns the month for date, from 1 (January) to 12 (December).

DAYNAME(date)

This function returns the name of the day of the week for date (for example, 'Tuesday').

MONTHNAME(date)

This function returns the name of the month for date (for example, 'April').

QUARTER(date)

This function returns the quarter of the year for date (for example, QUARTER('2005-04-12') = 2).

WEEK(date [, mode ])

This function returns the week of the year for date by default in the range 0-53 (where week 1 is the first week of the year), assuming that the first day of the week is Sunday. By specifying one of the mode values in Table C.1, you can alter the way this value is calculated.

Table C.1. Modes for week calculations

YEAR(date)

This function returns the year for date, from 1000 to 9999.

YEARWEEK(date) , YEARWEEK(date, first)

This function returns the year and week for date in the form 'YYYYWW'. Note that the first or last day or two of the year may often belong to a week of the preceding or following year, respectively. For example:

YEARWEEK("2006-12-31") = 200701

HOUR(time)

This function returns the hour for time, from 0 to 23.

MINUTE(time)

This function returns the minute for time, from 0 to 59.

SECOND(time)

This function returns the second for time, from 0 to 59.

PERIOD_ADD(period, num_months)

This function adds num_months months to period (specified as 'YYMM' or 'YYYYMM') and returns the value in the form 'YYYYMM'.

PERIOD_DIFF(period1, period2)

This function returns the number of months between period1 and period2 (each of which should be specified as 'YYMM' or 'YYYYMM').

DATE_ADD(date, INTERVAL expr type) , DATE_SUB(date, INTERVAL expr type) , ADDDATE(date, INTERVAL expr type) , SUBDATE(date, INTERVAL expr type)

This function returns the result of either adding or subtracting the specified interval of time to or from date (a DATE or DATETIME value). DATE_ADD and ADDDATE are identical, as are DATE_SUB and SUBDATE. expr specifies the interval to be added or subtracted and may be negative if you wish to specify a negative interval, and type specifies the format of expr, as shown in Table C.2. If date and expr involve only date values, the result will be a DATE value; otherwise, this function will return a DATETIME value. Here are a few examples to help you see how this family of functions works. The following both return the date six months from now:

ADDDATE(CURDATE(), INTERVAL 6 MONTH)

DATE_ADD(CURDATE(), INTERVAL '0-6' YEAR_MONTH)

The following all return this time tomorrow:

ADDDATE(NOW(), INTERVAL 1 DAY)

SUBDATE(NOW(), INTERVAL -1 DAY)

DATE_ADD(NOW(), INTERVAL '24:0:0' HOUR_SECOND)

DATE_ADD(NOW(), INTERVAL '1 0:0' DAY_MINUTE)

Table C.2. Interval types for date addition/subtraction functions

TO_DAYS(date)

This function converts date to a number of days since year 0. It allows you to calculate differences in dates (that is, TO_DAYS(date1) - TO_DAYS(date2) = days between date1 and date2).

FROM_DAYS(days)

Given the number of days since year 0 (as produced by TO_DAYS), this function returns a date.

DATE_FORMAT(date, format)

This function takes the date or time value date and returns it formatted according to the formatting string format, which may contain as placeholders any of the symbols shown in Table C.3.

Table C.3. DATE_FORMAT symbols (2004-01-01 01:00:00)

TIME_FORMAT(time, format)

This function is the same as DATE_FORMAT, except that the format string may only contain symbols referring to hours, minutes, and seconds.

CURDATE() , CURRENT_DATE

This function returns the current system date in the SQL date format 'YYYY-MM-DD' (if used as a date) or as YYYYMMDD (if used as a number).

CURTIME() , CURRENT_TIME , CURRENT_TIME()

This function returns the current system time in the SQL time format 'HH:MM:SS' (if used as a time) or as HHMMSS (if used as a number).

NOW() , SYSDATE() , CURRENT_TIMESTAMP , CURRENT_TIMESTAMP() , LOCALTIME , LOCALTIME() , LOCALTIMESTAMP , LOCALTIMESTAMP()

This function returns the current system date and time in SQL date/time format 'YYYY-MM-DD HH:MM:SS' (if used as a date/time) or as YYYYMMDDHHMMSS (if used as a number).

UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)

This function returns either the current system date and time, or the specified date/time as the number of seconds since 1970-01-01 00:00:00 GMT.

FROM_UNIXTIME(unix_timestamp)

The opposite of UNIX_TIMESTAMP, this function converts a number of seconds from 1970-01-01 00:00:00 GMT to 'YYYY-MM-DD HH:MM:SS' (if used as a date/time) or YYYYMMDDHHMMSS (if used as a number), local time.

FROM_UNIXTIME(unix_timestamp, format)

This function formats a UNIX timestamp according to the format string, which may contain any of the symbols listed in Table C.3.

SEC_TO_TIME(seconds)

This function converts some number of seconds to the format 'HH:MM:SS' (if used as a time) or HHMMSS (if used as a number).

TIME_TO_SEC(time)

This function converts a time in the format 'HH:MM:SS' to a number of seconds.

Miscellaneous Functions

DATABASE()

This function returns the currently selected database name, or an empty string if no database is currently selected.

USER() , SYSTEM_USER() , SESSION_USER()

This function returns the current MySQL username, including the client host name (for example, 'kevin@localhost'). The SUBSTRING_INDEX function may be used to obtain the username alone:

SUBSTRING_INDEX(USER(), "@", 1) = 'kevin'

CURRENT_USER()

This function returns the user entry in the MySQL access control system that was used to authenticate the current connection—and which controls its privileges—in the form 'user@host'. In many cases, this will be the same as the value returned by USER, but when entries in the access control system contain wild cards, this value may be less specific (for example, '@%.mycompany.com').

PASSWORD(str)

This is a one-way password encryption function that converts any string (typically a plain-text password) into an encrypted format precisely 16 characters in length. A particular plain-text string always will yield the same encrypted string of 16 characters; thus, values encoded in this way can be used to verify the correctness of a password without actually storing the password in the database. This function uses a different encryption mechanism to UNIX passwords; use ENCRYPT for that type of encryption.

ENCRYPT(str [, salt ])

This function uses standard UNIX encryption (via the crypt() system call) to encrypt str. The salt argument is optional, and lets you control the seed that’s used for generating the password. If you want the encryption to match a UNIX password file entry, the salt should be the two first characters of the encrypted value you’re trying to match. Depending on the implementation of crypt() on your system, the encrypted value may only depend on the first eight characters of the plain-text value. On systems where crypt() is unavailable, this function returns NULL.

ENCODE(str, pass_str)

This function encrypts str using a two-way password-based encryption algorithm with password pass_str. To subsequently decrypt the value, use DECODE.

DECODE(crypt_str, pass_str)

This function decrypts the encrypted crypt_str using two-way password-based encryption with password pass_str. If the same password is given that was provided to ENCODE, the original string will be restored.

MD5(string)

This function calculates an MD5 hash based on string. The resulting value is a 32-digit hexadecimal number. A particular string will always produce the same MD5 hash; however, MD5(NOW()) may be used, for instance, to obtain a semi-random string when one is needed (as a default password, for instance).

LAST_INSERT_ID()

This function returns the last number that was automatically generated for an AUTO_INSERT column in the current connection.

FOUND_ROWS()

When you execute a SELECT query with a LIMIT clause, you may sometimes want to know how many rows would have been returned if you omitted a LIMIT clause. To do this, use the SQL_CALC_FOUND_ROWS option for the SELECT query (see Appendix B), then call this function in a secondSELECT query. Calling this function is considerably quicker than repeating the query without a LIMIT clause, since the full result set doesn’t need to be sent to the client.

FORMAT(expr, num)

This function formats a number expr with commas as “thousands separators” and num decimal places (rounded to the nearest value and padded with zeros).

VERSION()

This function returns the MySQL server version (for example, '5.1.34').

CONNECTION_ID()

This function returns the thread ID for the current connection.

GET_LOCK(str, timeout)

If two or more clients must synchronize tasks beyond what table locking can offer, named locks may be used instead. GET_LOCK attempts to obtain a lock with a given name (str). If the named lock is already in use by another client, this client will wait up to timeout seconds before giving up waiting for the lock to become free. Once a client has obtained a lock, it can be released either using RELEASE_LOCK or by using GET_LOCK again to obtain a new lock. GET_LOCK returns 1 if the lock was successfully retrieved, 0 if the time specified by timeout elapsed, or NULL if some error occurred. GET_LOCK is not a MySQL command in and of itself; it must appear as part of another query. For example:

SELECT GET_LOCK("mylock", 10)

RELEASE_LOCK(str)

This function releases the named lock that was obtained by GET_LOCK. It returns 1 if the lock was released, 0 if the lock wasn’t locked by this thread, or NULL if the lock doesn’t exist.

IS_FREE_LOCK(str)

This function checks if the named lock is free to be locked. It returns 1 if the lock was free, 0 if the lock was in use, or NULL if an error occurred.

BENCHMARK(count, expr)

This function repeatedly evaluates expr count times for the purposes of speed testing. The MySQL command line client allows the operation to be timed.

INET_NTOA(expr)

This function returns the IP address represented by the integer expr. See INET_ATON to create such integers.

INET_ATON(expr)

This function converts an IP address expr to a single integer representation. For example:

INET_ATON('64.39.28.1') = 64 * 2553 + 39 * 2552 + 28 * 255 + 1

= 1063751116

Functions for Use with GROUP BY Clauses

Also known as summary functions, the following are intended for use with GROUP BY clauses, where they’ll produce values based on the set of records making up each row of the final result set. If used without a GROUP BY clause, these functions will cause the result set to be displayed as a single row, with a value calculated based on all the rows of the complete result set. Without a GROUP BY clause, mixing these functions with columns where there are no summary functions will cause an error, because you’re unable to collapse those columns into a single row and gain a sensible value.

COUNT(expr)

This function returns a count of the number of times that expr had a non-NULL value in the ungrouped result set. If COUNT(*) is used, it will simply provide a count of the number of rows in the group, irrespective of NULL values.

COUNT(DISTINCT expr [, expr … ])

This function returns a count of the number of different non-NULL values (or sets of values, if multiple expressions are provided).

AVG(expr)

This function calculates the arithmetic mean (average) of the values appearing in the rows of the group.

MIN(expr) , MAX(expr)

This function returns the smallest or largest value of expr in the rows of the group.

SUM(expr)

This function returns the sum of the values for expr in the rows of the group.

STD(expr) , STDDEV(expr)

This function returns the standard deviation of the values for expr in the rows of the group (either of the two function names may be used).

BIT_OR(expr) , BIT_AND(expr)

This function calculates the bit-wise OR and the bit-wise AND of the values for expr in the rows of the group, respectively.