Essential MySQL Functions

MySQL Functions

MySQL can do far more than just insert, update and select data. In fact the world’s most popular open source database has a whole host of incredibly useful functions built in that you might never know about until you find one day that you need them – and then you wonder how you coped without them!

Essential Functions

There are literally hundreds of build-in functions available in MySQL, and it would be pointless covering them all, seeing as the MySQL manual does that already! Instead I am going to introduce you to some useful functions that you may well have heard of before, and hopefully some that you never encountered until now. This post is intended to grow as and when I have the time to add new functions.

Note: in general MySQL is not case-sensitive, so the examples show below should work in either uppercase or lowercase. It is recommended to use certain uppercase words throughout a query for ease of understanding.

DATE_FORMAT()

Usage: DATE_FORMAT(date,format)

DATE_FORMAT(), as the name suggests, formats the date value according to the format string.

If you’ve stored a date (or datetime) in one of MySQL’s native formats, you can select it in your chosen display format (e.g. “Sunday 24th January”). This is rather like PHP’s date() function, except that it works with MySQL date stamps rather than Unix timestamps.

Examples:

SELECT DATE_FORMAT('2010-01-24', '%W %D %M');
# RETURNS 'Sunday 24th January'
 
SELECT DATE_FORMAT(`date_column`, '%W %D %M') AS `date` FROM `table`;
# RETURNS the stored DATE(s) formatted AS you chose

When formatting a date you can give it a different name using the “AS” modifier so that you can still use the original column name in an “ORDER BY” clause.

MySQL Manual: DATE_FORMAT()

REPLACE()

Usage: REPLACE(column name, old value, new value)

REPLACE() is commonly used in an UPDATE query to change one string to another. It lets you replace a string within a larger string, for example changing a word or phrase within a larger block of text without having to update the whole text block itself. It’s provides a nifty search and replace facility.

Example:

UPDATE `table_name` SET `column_name` = REPLACE(`column_name`, 'old text', 'new text');
# Changes any 'old text' strings TO 'new text' IN the COLUMN specified

MySQL Manual: REPLACE()

CONCAT()

Usage: CONCAT(str1, str2, …)

CONCAT() returns a string that results from concatenating the arguments given. Arguments can be strings or even column names.

Examples:

SELECT CONCAT('My', 'S', 'QL');
# RETURNS 'MySQL'
 
SELECT CONCAT(`first_name`, ' ', `last_name`) AS `full_name` FROM `users`;
# RETURNS 'John Smith' WHERE COLUMN `first_name` IS 'John' AND `last_name` IS 'Smith' etc...

As well as being useful for selecting concatenated columns and text together, CONCAT() can also be used in UPDATE queries etc…

MySQL Manual: CONCAT()

NOW()

Usage: NOW()

NOW() returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ format.

Examples:

UPDATE `table` SET `updated` = NOW() WHERE `id` = 1;
# SET the `updated` COLUMN TO the CURRENT DATE AND TIME FOR the record specified

NOW() makes setting a column value as the current date and time a piece of cake!

MySQL Manual: NOW()

UNIX_TIMESTAMP()

Usage: UNIX_TIMESTAMP([date])

UNIX_TIMESTAMP() returns a Unix timestamp if called without the date option, or the Unix timestamp of the date argument is one is supplied. The date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.

This is similar to the NOW() function except that you would use it if your date columns are Unix timestamp integers rather than datetime formats.

Examples:

UPDATE `table` SET `updated` = UNIX_TIMESTAMP() WHERE `id` = 1;
# SET the `updated` COLUMN TO the CURRENT Unix TIMESTAMP FOR the record specified
 
SELECT UNIX_TIMESTAMP('2010-02-11 19:31:57');
# SELECT the Unix TIMESTAMP FOR a given DATE AND TIME

MySQL Manual: UNIX_TIMESTAMP()

FROM_UNIXTIME()

Usage: FROM_UNIXTIME(Unix timestamp[,format])

FROM_UNIXTIME() does the reverse of UNIX_TIMESTAMP(), it takes a Unix timestamp and returns a representation of the datetime format – ‘YYYY-MM-DD HH:MM:SS’. If the format argument is given, the result is formatted according to the format string, which is used the same way as the DATE_FORMAT() function.

Examples:

SELECT FROM_UNIXTIME(1265917455);
# RETURNS 2010-02-11 19:44:15
 
SELECT FROM_UNIXTIME(1265917455, '%D %M %Y');
# RETURNS 11th February 2010

MySQL Manual: FROM_UNIXTIME()

IF()

Usage: IF(expr1,expr2,expr3)

IF() can be used inside an SQL statement to return different values by evaluating the expression given.

If expr1 is TRUE then IF() returns expr2; otherwise it returns expr3.

Examples:

SELECT IF(1 < 2, 'yes', 'no');
# RETURNS 'yes' because 1 IS less than 2
 
UPDATE `people` SET `legal_status` = IF(`age` >= 18, 'adult', 'minor');
# Updates ALL people whose age IS 18 OR OVER TO 'adult', otherwise TO 'minor'

IF() is useful for updating a record where new values can vary depending on the existing data. Rather than selecting the current data, checking it with PHP and then deciding what the new values should be, you may be able to make your choice within the one single UPDATE statement itself by using the IF() function.

MySQL Manual: IF()