MySQL Stored Procedure Programming (2009)

Part II. Stored Program Construction

Chapter 9. MySQL Built-in Functions

This chapter provides a reference to the MySQL built-in functions that you can use in your MySQL stored programs. You can use virtually all of the MySQL functions that are available in SQL statements within stored programs, so if you are already familiar with traditional MySQL functions, you can safely skip this chapter. Because this is a reference chapter, we expect you will come back to it from time to time when you need to use a particular function—so don't feel guilty if you decide to skip or only briefly review this chapter.

In general, you can use any of the standard MySQL functions inside stored programs except those functions that work on groups or sets of data. These functions—often used in combination with the GROUP BY clause in a SQL statement—include MAX, MIN, COUNT, AVERAGE, and SUM. These functions are not applicable in stored programs (other than in SQL statements embedded in the programs) because stored program variables are scalar (consist of only a single value).

This chapter looks at the built-in functions that we anticipate you might want to use in stored programs; we describe these in the following categories:

§ String functions

§ Numeric functions

§ Date and time functions

§ Other functions

MySQL includes a huge number of built-in functions, however, so we can't cover all of them in depth; for a complete list, refer to the online MySQL Reference Manual (http://dev.mysql.com/doc/).

String Functions

String functions perform operations on string data types such as VARCHAR, CHAR, and TEXT.

ASCII

string1=ASCII(string2)

ASCII returns the ASCII character code corresponding to the first character in the provided input string.

Since the ASCII function returns only the ASCII code for the first character, we can create a stored function to extend this capability to allow us to return the ASCII codes corresponding to all of the characters in the string. Example 9-1 shows an implementation of such a stored function. It uses the LENGTH and SUBSTR functions to extract each character in the input string, and then uses the ASCII and CONCAT functions to build up a string consisting of all of the ASCII codes corresponding to the entire input string.

Example 9-1. Using the ASCII function

CREATE FUNCTION ascii_string (in_string VARCHAR(80) )

RETURNS VARCHAR(256)

DETERMINISTIC

BEGIN

DECLARE i INT DEFAULT 1;

DECLARE string_len INT;

DECLARE out_string VARCHAR(256) DEFAULT '';

SET string_len=LENGTH(in_string);

WHILE (i<string_len) DO

SET out_string=CONCAT(out_string,ASCII(SUBSTR(in_string,i,1)),' ');

SET i=i+1;

END WHILE;

RETURN (out_string);

END

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

SELECT ascii_string('MySQL Rocks!')

--------------

+---------------------------------------+

| ascii_string('MySQL Rocks!') |

+---------------------------------------+

| 77 121 83 81 76 32 82 111 99 107 115 |

+---------------------------------------+

1 row in set (0.00 sec)

CHAR

string=CHAR(ascii code [,...])

CHAR returns the characters corresponding to one or more ASCII codes provided. Example 9-2 uses the CHAR function to create a temporary table containing the ASCII characters for the first 128 ASCII codes.

Example 9-2. Using the CHAR function to generate an ASCII chart

CREATE PROCEDURE ascii_chart( )

BEGIN

DECLARE i INT DEFAULT 1;

CREATE TEMPORARY TABLE ascii_chart

(ascii_code INT, ascii_char CHAR(1));

WHILE (i<=128) DO

INSERT INTO ascii_chart VALUES(i,CHAR(i));

SET i=i+1;

END WHILE;

END

--------------

Query OK, 0 rows affected (0.01 sec)

--------------

CALL ascii_chart( )

--------------

Query OK, 1 row affected (5.96 sec)

--------------

SELECT * FROM ascii_chart

--------------

+------------+------------+

| ascii_code | ascii_char |

+------------+------------+

| 1 | ☺ |

| 2 | ☻ |

| 3 | ♥ |

| 4 | ♦ |

| 5 | ♣ |

| 6 | ♠ |

| 7 | |

| 8 | |

| 9 | |

| 10 | |

| 11 | ♂ |

| 12 | ♀ |

| 13 |

| 14 | ♬ |

| 15 | ☼ |

| 16 | ▸ |

| 17 | ◂ |

| 18 | ↕ |

| 19 | !! |

| 20 | ¶ |

| 21 | § |

| 22 | -- |

| 23 | _ |

| 24 | ↑ |

| 25 | ↓ |

| 26 | → |

| 27 | ← |

CHARSET

character_set=CHARSET(string)

CHARSET returns the character set of the supplied string.

SET var1=CHARSET("My name is Guy") ; → latin1

CONCAT

string1=CONCAT(string2 [,...])

CONCAT returns a string consisting of the concatenation of all of the supplied input strings. If any of the input strings is NULL, then CONCAT will also return NULL.

Example 9-3 uses the CONCAT function to create a well-formatted name including—if appropriate—title and middle initial. First, we use the ISNULL function to check for NULLs in the input string so as to avoid inadvertently returning a NULL string if one of the inputs is NULL.

Example 9-3. Using CONCAT to concatenate strings

CREATE FUNCTION concat_example(in_title VARCHAR(4),

in_gender CHAR(1),

in_firstname VARCHAR(20),

in_middle_initial CHAR(1),

in_surname VARCHAR(20))

RETURNS VARCHAR(60)

BEGIN

DECLARE l_title VARCHAR(4);

DECLARE l_name_string VARCHAR(60);

IF ISNULL(in_title) THEN

IF in_gender='M' THEN

SET l_title='Mr';

ELSE

SET l_title='Ms';

END IF;

END IF;

IF ISNULL(in_middle_initial) THEN

SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',in_surname);

ELSE

SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',

in_middle_initial,' ',in_surname);

END IF;

RETURN(l_name_string);

END;

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

SELECT concat_example(null,'F','Mary',null,'Smith')

--------------

+----------------------------------------------+

| concat_example(null,'F','Mary',null,'Smith') |

+----------------------------------------------+

| Ms Mary Smith |

+----------------------------------------------+

1 row in set (0.00 sec)

If your database is running in ANSI mode (sql_mode='ANSI') or if the sql_mode variable includes the PIPES_AS_CONCAT setting, you can use the || (pipe) characters to concatenate strings. The use of pipe characters to indicate concatenation in stored programs is dependent on the setting of sql_mode when the stored program is created, not when it runs. So you can happily use the || method of concatenating strings provided that you set sql_mode='ANSI'when you create the program. If the program runs when sql_mode is set to some other value, the stored program will still return the correct results.

Example 9-4 illustrates the use of ANSI mode and || characters to perform string concatenation. Note that while sql_mode was set to 'ANSI' when the stored function was created, the stored program still returned the correct results even though the sql_mode had been set to 'TRADITIONAL' at runtime.

Example 9-4. Using || to concatenate when sql_mode=ANSI

set sql_mode='ANSI'

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

CREATE FUNCTION concat_example_ansi(

in_title VARCHAR(4),

in_gender CHAR(1),

in_firstname VARCHAR(20),

in_middle_initial CHAR(1),

in_surname VARCHAR(20))

RETURNS VARCHAR(60)

BEGIN

DECLARE l_title VARCHAR(4);

DECLARE l_name_string VARCHAR(60);

IF ISNULL(in_title) THEN

IF in_gender='M' THEN

SET l_title='Mr';

ELSE

SET l_title='Ms';

END IF;

END IF;

IF ISNULL(in_middle_initial) THEN

SET l_name_string=l_title||' '||in_firstname||' '||in_surname;

ELSE

SET l_name_string=l_title||' '||in_firstname||' '||

in_middle_initial||' '||in_surname;

END IF;

RETURN(l_name_string);

END;

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

SET sql_mode='TRADITIONAL'

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

SELECT concat_example_ansi(null,'F','Mary',null,'Smith')

--------------

+---------------------------------------------------+

| concat_example_ansi(null,'F','Mary',null,'Smith') |

+---------------------------------------------------+

| Ms Mary Smith |

+---------------------------------------------------+

CONCAT_WS

string1=CONCAT_WS(delimiter,string2 [,...])

CONCAT_WS acts like the CONCAT function, but it inserts the specified delimiter between each string. Note in Example 9-3 that we manually inserted single space characters between each string, as shown below:

SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',

in_middle_initial,' ',in_surname);

Using CONCAT_WS, we could simplify this statement as follows:

SET l_name_string=CONCAT_WS(' ',l_title ,in_firstname ,

in_middle_initial,in_surname);

INSERT

string=INSERT(original_string,position,length,new_string)

INSERT inserts new_string into the original_string at the specified position, optionally overwriting up to length characters of the original string.

Example 9-5 shows how we might use the INSERT function to emulate the MySQL REPLACE function to implement "search and replace" functionality. We first use the INSTR function to find the location of the "find string" and then replace it with the "replace string." We set length to the length of the find string so that the find string is overwritten with the replace string, even if the two strings are of different lengths.

Example 9-5. Using the INSERT function

CREATE FUNCTION my_replace

(in_string VARCHAR(255),

in_find_str VARCHAR(20),

in_repl_str VARCHAR(20))

RETURNS VARCHAR(255)

BEGIN

DECLARE l_new_string VARCHAR(255);

DECLARE l_find_pos INT;

SET l_find_pos=INSTR(in_string,in_find_str);

IF (l_find_pos>0) THEN

SET l_new_string=INSERT(in_string,l_find_pos,LENGTH(in_find_str),in_repl_str);

ELSE

SET l_new_string=in_string;

END IF;

RETURN(l_new_string);

END

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

SELECT my_replace('We love the Oracle server','Oracle','MySQL')

--------------

+----------------------------------------------------------+

| my_replace('We love the Oracle server','Oracle','MySQL') |

+----------------------------------------------------------+

| We love the MySQL server |

+----------------------------------------------------------+

1 row in set (0.00 sec)

INSTR

position=INSTR(string,substring)

INSTR returns the location of the first occurrence of a substring within a string. If no occurrence of the substring is found, INSTR returns 0.

In Example 9-5 we used INSTR to locate the "find string" within a string prior to using INSERT to replace that string with the "replace string."

LCASE

string1=LCASE(string2)

LCASE returns an input string with any of its uppercase letters translated to lowercase. Nonalphabetic characters are ignored.

Here are some examples of the effect of LCASE:

SET a=LCASE('McTavish Jewelers'); → 'mctavish jewelers'

SET b=LCASE('23rd June'); → '23rd june'

LEFT

string=LEFT(string2,length)

LEFT returns the leftmost characters (the number is specified by length) in the input string.

SET a=LEFT('Hi There',2); → 'Hi'

LENGTH

characters=LENGTH(string)

LENGTH returns the number of bytes in the input string. For single-byte character sets (e.g., English, Swedish), this is equivalent to the number of characters in the string. However, for multibyte character sets (e.g., Kanji, Klingon), you may be better off using the CHAR_LENGTH function, which returns the number of characters rather than the number of bytes.

SET a=LENGTH(null); → NULL

SET b=LENGTH(''); → 0

SET c=LENGTH('Guy'); → 3

SET d=LENGTH('Guy '); → 4

LOAD_FILE

string=LOAD_FILE(file_name)

LOAD_FILE loads the contents of the specified file into a variable of a suitable data type—usually BLOB or TEXT. The file has to be accessible to the MySQL server—that is, the file needs to exist on the machine that hosts the MySQL server, and the server needs to have sufficient permissions to read the file.

Example 9-6 shows how we can use the LOAD_FILE function to load the contents of an operating system file and report the number of bytes loaded. Note that on Windows we need to use double-backslash characters, \\, instead of single slashes as directory separators. Thus, in order to specify the file 'c:\tmp\mydata.txt' we specified 'c:\\tmp\\mydata.txt'.

Example 9-6. Using LOAD_FILE to read an OS file

CREATE PROCEDURE filesize(in_file_name VARCHAR(128))

BEGIN

DECLARE mytext TEXT;

SET mytext=LOAD_FILE(in_file_name);

SELECT in_file_name||' contains '||length(mytext)||' bytes'

AS output;

END

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

CALL filesize('c:\\tmp\\mydata.txt')

--------------

+-------------------------------------+

| output |

+-------------------------------------+

| c:\tmp\mydata.txt contains 98 bytes |

+-------------------------------------+

1 row in set (0.02 sec)

LOCATE

position=LOCATE(substring, string [,start_position] )

LOCATE is similar to the INSTR function in that it searches for the location of a substring within a string. However, it also allows us to specify a starting position for the search. If the substring is not found, LOCATE returns 0.

In Example 9-7 we use LOCATE to count the number of occurrences of a substring within a string. Once we find an instance of the substring, we set the starting position to just past that string and repeat until all instances of the substring have been found.

Example 9-7. Using LOCATE to find substrings

CREATE FUNCTION count_strings

(in_string VARCHAR(256),in_substr VARCHAR(128))

RETURNS INT

DETERMINISTIC

BEGIN

DECLARE l_count INT DEFAULT 0;

DECLARE l_start INT DEFAULT 1;

DECLARE l_pos INT;

MainLoop:

LOOP

SET l_pos=LOCATE(in_substr,in_string,l_start);

IF l_pos=0 THEN

LEAVE MainLoop;

ELSE

SET l_count=l_count+1;

SET l_start=l_pos+1;

END IF;

END LOOP;

RETURN(l_count);

END

--------------

Query OK, 0 rows affected (0.00 sec)

--------------

SELECT count_strings('She sells sea shells by the sea shore','sea') as count

--------------

+-------+

| count |

+-------+

| 2 |

+-------+

1 row in set (0.00 sec)

LPAD

string1=LPAD(string2,length,pad)

LPAD adds occurrences of the pad string to the input string until the output string reaches the specified length.

SET a=LPAD('Hello',10,'.'); → '.....Hello'

SET b=lpad('hi',10,'( )'); → '()()()( )hi'

LTRIM

string1=LTRIM(string2)

LTRIM trims any leading spaces from a string.

SET a=LTRIM(' Hello'); → 'Hello'

REPEAT

string1=REPEAT(string2,count)

REPEAT returns a string in which the input string is repeated count times.

SET a=REPEAT('Dive! ',3); → 'Dive! Dive! Dive!'

REPLACE

string1=REPLACE(string2,search_string,replace_string)

REPLACE returns a string in which all occurrences of the search_string are replaced by the replace_string.

SET a=REPLACE('Monty & David','&','and'); → 'Monty and David'

RPAD

string1=RPAD(string2,length,pad)

RPAD adds a sequence of pad characters to the string up to the specified length.

SET var1=RPAD("MySQL",10,".") ; → MySQL.....

RTRIM

string1=RTRIM(string2)

RTRIM trims any trailing spaces from a string.

SET a=RTRIM('Guy '); → 'Guy'

STRCMP

position=STRCMP(string1,string2)

STRCMP compares two strings and determines if the first string is "before" or "after" the second string in the ASCII collation sequence. The function returns -1 if the first string is before the second string, 1 if the first string collates after the second string, and 0 if the two strings are identical.

SET a=STRCMP('Guy','Guy') → 0

SET b=STRCMP('Guy','Steven') → -1

SET c=STRCMP('Steven','Guy') → 1

SUBSTRING

string1=SUBSTRING(string2, position [,length])

SUBSTRING returns a portion of the supplied string starting at the specified position from the beginning of the string (starting at 1). If a negative position is specified, then the substring commences from the end of the string; for example, -2 indicates the second to last character of the string. Iflength is omitted, SUBSTRING returns all of the remaining portion of the input string.

SET a=SUBSTR('MySQL AB',7) → 'AB'

SET b=SUBSTR('MySQL AB',-2) → 'AB'

SET c=SUBSTR('MySQL AB',3,3) → 'SQL'

TRIM

string1=TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2)

TRIM strips leading and/or trailing characters from a string. By default, it trims both leading and trailing spaces.

SET a=TRIM(LEADING '>' FROM '>>>>>>>>>Fred'); → 'Fred'

SET b=TRIM(BOTH '-' FROM '---------Fred-------'); → 'Fred'

SET c=TRIM(BOTH FROM ' Guy ') → 'Guy';

SET d=TRIM(' Guy '); → 'Guy'

UCASE

string1=UCASE(string2)

UCASE converts a string to uppercase.

Other String Functions

Table 9-1 lists the string functions not covered in previous sections. Some of these functions are aliases for functions we have already discussed, while others are rarely used in mainstream MySQL programming. You can find out more about these functions by reading the section "Functions and Operators" in the MySQL Reference Manual, available online.

Table 9-1. Additional string functions

Numeric Functions

Numeric functions perform operations on numeric data types such as INT and FLOAT.

ABS

number1=ABS(number2)

ABS returns the absolute value of a number—that is, the magnitude of the value ignoring any minus sign.

SET var1=ABS(2.143); → 2.143

SET var2=ABS(-10); → 10

SET var3=ABS(10); → 10

SET var4=ABS(-2.3); → 2.3

BIN

binary_number=BIN(decimal_number)

BIN returns the binary (base 2) representation of an integer value.

SET var1=BIN(1); → 1

SET var2=BIN(2); → 10

SET var3=BIN(3); → 11

SET var4=BIN(45); → 101101

CEILING

number1=CEILING(number2)

CEILING returns the next integer number that is higher than the input floating-point number.

SET var1=CEILING(3.5); → 4

SET var2=CEILING(-3.5); → -3

CONV

number1=CONV(number2,from_base,to_base)

CONV converts numbers from one base system to another. Although CONV is, in essence, a numeric function, it may return values that you may need to deal with as strings (e.g., hexadecimal numbers).

The following CONV statements convert the number 45 (base 10) into binary (base 2), hexadecimal (base 16), and octal (base 8):

SET var1=CONV(45,10,2); → 101101

SET var2=CONV(45,10,16); → 2D

SET var3=CONV(45,10,8) ; → 55

These statements convert the number 45 (base 2) into base 10, and converts 45 (base 8) into base 2:

SET var4=CONV(101101,2,10); → 45

SET var5=CONV(55,8,2); → 101101

FLOOR

number1=FLOOR(number2)

FLOOR returns the largest integer value not greater than X.

SET var1=FLOOR(3.5); → 3

SET var2=FLOOR(-3.5); → -4

FORMAT

string=FORMAT(number,decimal_places)

FORMAT returns a string representation of a number with comma separators at each thousand and with the specified number of decimal places.

SET var1=FORMAT(21321.3424,2); → 21,321.34

HEX

HexNumber=HEX(DecimalNumber)

HEX returns the hexadecimal representation of a number.

SET var1=HEX(9); → 9

SET var2=HEX(11); → B

SET var3=HEX(32); → 20

LEAST

number1=LEAST(number, number2 [,..])

LEAST returns the number in the input series with the smallest numerical value.

SET var1=LEAST(32,432,-2,-1.4); → -2

MOD

remainder=MOD(numerator,denominator)

MOD returns the remainder (modulus) when the first number is divided by the second number.

MOD is particularly handy when you want something to happen at regular intervals in a loop. For instance, Example 9-8 purges (deletes) rows from the LOG_ARCHIVE table based on some criteria. As we discuss in Chapter 22, reducing commit frequency is an important optimization for transactional storage engines such as InnoDB. However, we do want to commit at regular intervals; otherwise, we risk losing all the work if the program fails midway through execution.

So Example 9-8 calculates the modulus of the delete count divided by 100. If this modulus is 0—which happens every 100 rows—a COMMIT is issued. The end result is that the program commits the delete operations every 100 rows.

Example 9-8. Using the MOD function to perform periodic COMMITs

CREATE PROCEDURE bulk_processing_example( )

MODIFIES SQL DATA

BEGIN

DECLARE delete_count INT DEFAULT 0;

DECLARE last_row INT DEFAULT 0;

DECLARE l_rec_id INT;

DECLARE c1 CURSOR FOR SELECT rec_id FROM log_archive;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row=1;

OPEN c1;

MainLoop:

LOOP

FETCH c1 INTO l_rec_id;

IF last_row THEN

LEAVE MainLoop;

END IF;

IF purge_due(l_rec_id) THEN

DELETE FROM log_archive WHERE rec_id=l_rec_id;

SET delete_count=delete_count+1;

IF MOD(delete_count,100)=0 THEN

COMMIT;

END IF;

END IF;

END LOOP MainLoop;

CLOSE c1;

END;

You can also calculate a modulus using numerator % denominator or numerator MOD denominator. Thus, these three assignments are all equivalent:

SET var1=MOD(5,3); → 2

SET var2=5%3; → 2

SET var3=5 MOD 3 ; → 2

POWER

result=POWER(number,power)

POWER returns the result of raising the first number to the power of the second number. You can use POW as a synonym for POWER.

SET var1=POWER(3,2); → 9 (3*3)

SET var2=POWER(2,3); → 8 (2*2*2)

SET var3=POWER(4,.5); → 2 (square root of 4)

SET var4=POWER(10,-2); → 0.01

SET var5=POWER(10,-3); → 0.001

SET var6=POW(2,2); → 4

RAND

number=RAND([seed])

RAND returns a random floating-point number between 0 and 1. If seed is specified, it is used to initialize the random-number generator, which lets you avoid generating repeatable sequences.

SET var1=RAND( ); → 0.86494333191304

SET var2=RAND( ); → 0.96148952838172

SET var3=RAND(5); → 0.40613597483014

SET var4=RAND( ); → 0.21261767690314

SET var5=RAND(5) ; → 0.40613597483014

SET var6=RAND( ); → 0.17861983010417

RAND can be used within stored programs to generate or select random table data. For instance, in Example 9-9, we use the RAND function to randomly select the employee of the week (and you thought we based it on performance!). We first find the maximum employee_id and then generate a random number between 1 and that number. Since RAND returns a floating-point number between 0 and 1, we multiply that number by the maximum employee number, generating a number between 0 and the maximum employee number. Next, we use FLOOR to convert the number to an integer value, and then add 1 to avoid generating an employee_id of 0.

Example 9-9. Using the RAND function to retrieve random rows

CREATE PROCEDURE select_winner( )

READS SQL DATA

BEGIN

DECLARE winner_id INT;

DECLARE max_employee_id INT;

DECLARE winner_name VARCHAR(70);

SELECT MAX(employee_id)

INTO max_employee_id

FROM employees;

SET winner_id=FLOOR(RAND( )*max_employee_id)+1;

SELECT CONCAT_WS(' ','Employee of the week is',firstname,surname)

FROM employees

WHERE employee_id=winner_id;

END;

ROUND

integer=ROUND(number [,decimals])

ROUND converts a floating-point number to the nearest integer value or—if the second argument is specified—to the specified number of decimal points.

SET var1=PI( ); → 3.141593

SET var2=ROUND(PI( )); → 3

SET var3=ROUND(PI( ),4); → 3.1416

SET var5=ROUND(4.49); → 4

SET var6=ROUND(4.51); → 5

SIGN

number1=SIGN(number2)

SIGN returns -1 if a number is less than 0, 0 if the number is 0, and 1 if the number is greater than 0.

SET var1=SIGN(-5); → -1

SET var2=SIGN(0); → 0

SET var3=SIGN(5); → 1

SQRT

number1=SQRT(number2)

SQRT returns the square root of a number. It is equivalent to POWER( number ,.5).

SET var1=SQRT(4); → 2

SET var2=SQRT(64); → 8

SET var3=POWER(64,.5); → 8

Other Numeric Functions

Table 9-2 lists additional numeric functions. These functions are rarely used in mainstream MySQL applications; in this category are the trigonometric and logarithmic functions that you probably studied in high school and have never used since!

Table 9-2. Additional numeric functions

Date and Time Functions

Date and time functions operate on MySQL date-time data types such as DATE and DATETIME.

ADDTIME

date1=ADDTIME(date2,time_interval)

ADDTIME adds the specified time interval to the date-time provided and returns the amended date. Time intervals are specified in the format hh:mm:ss.hh, so you can add any time interval down to one-hundredth of a second.

SET var1=NOW( ); → 2005-07-21 18:56:46

SET var2=ADDTIME(NOW( ),"0:00:01.00"); → 2005-07-21 18:56:47

SET var3=ADDTIME(NOW( ),"0:01:00.00"); → 2005-07-21 18:57:46

SET var4=ADDTIME(NOW( ),"1:00:00.00") ; → 2005-07-21 19:56:46

CONVERT_TZ

datetime1=CONVERT_TZ(datetime2,fromTZ,toTZ)

This function converts a date-time value from one time zone to another. The valid time zone values can be found in the table mysql.time_zone_name.

You may have to load the MySQL time zone tables; for instructions, see the MySQL manual section "MySQL Server Time Zone Support."

CURRENT_DATE

date=CURRENT_DATE( )

CURRENT_DATE returns the current date. It does not show the time.

SET var1=CURRENT_DATE( ); → 2005-07-21

CURRENT_TIME

time=CURRENT_TIME( )

CURRENT_TIME returns the current time. It does not show the date.

SET var1=CURRENT_TIME( ); → 22:12:21

CURRENT_TIMESTAMP

timestamp=CURRENT_TIMESTAMP( )

CURRENT_TIMESTAMP returns the current date and time in the format yyyy-mm-dd hh:mm:ss.

SET var1=CURRENT_TIMESTAMP( ); → 2005-07-21 22:15:02

DATE

date=DATE(datetime)

DATE returns the date part of a date-time value.

SET var1=NOW( ); → 2005-07-23 12:08:52

SET var2=DATE(var1) ; → 2005-07-23

DATE_ADD

date1=DATE_ADD(date2, INTERVAL interval_value interval_type)

DATE_ADD returns the date-time that results from adding the specified interval to the date-time provided. Possible intervals are listed in Table 9-3.

SET var1=NOW( ); → 2005-07-20 22:33:21

SET var2=DATE_ADD(NOW( ), INTERVAL 7 DAY); → 2005-07-27 22:33:21

SET var3=DATE_ADD(NOW( ), INTERVAL 0623 DAY_HOUR) ; → 2005-08-15 21:33:21

SET var4=DATE_ADD(NOW( ), INTERVAL 06235959 DAY_SECOND) ; → 2005-10-01 02:46:00

SET var5=DATE_ADD(NOW( ), INTERVAL 2 MONTH); → 2005-09-20 22:33:21

SET var6=DATE_ADD(NOW( ), INTERVAL 10 YEAR); → 2015-07-20 22:33:21

SET var7=DATE_ADD(NOW( ), INTERVAL 3600 SECOND); → 2005-07-20 23:33:21

Table 9-3. Date-time formats for DATE_ADD and DATE_SUB

DATE_FORMAT

string=DATE_FORMAT(datetime,FormatCodes)

DATE_FORMAT accepts a date-time value and returns a string representation of the date in the desired format. Format codes are shown in Table 9-4.

SET var1=NOW( ); → 2005-07-23 13:28:21

SET var2=DATE_FORMAT(NOW( ),"%a %d %b %y"); → Sat 23 Jul 05

SET var3=DATE_FORMAT(NOW( ),"%W, %D %M %Y"); → Saturday, 23rd July 2005

SET var4=DATE_FORMAT(NOW( ),"%H:%i:%s") ; → 13:28:21

SET var5=DATE_FORMAT(NOW( ),"%T"); → 13:28:21

SET var6=DATE_FORMAT(NOW( ),"%r"); → 01:28:22 PM

Table 9-4. Format codes for DATE_FORMAT

DATE_SUB

date1=DATE_SUB(date2, INTERVAL interval_value interval_type)

DATE_SUB returns the date-time resulting from subtracting the specified interval from the date-time provided. Possible intervals are listed in Table 9-3.

Example 9-10 shows a stored procedure that determines if an employee's date of birth indicates an age of greater than 18 years. DATE_SUB is used to create a date 18 years earlier than the current date. This date is compared to the date of birth and, if it is earlier, we can conclude that the employee is less than 18 years old.

Example 9-10. Using DATE_SUB

CREATE PROCEDURE validate_age

(in_dob DATE,

OUT status_code INT,

OUT status_message VARCHAR(30))

BEGIN

IF DATE_SUB(now( ), INTERVAL 18 YEAR) <in_dob THEN

SET status_code=-1;

SET status_message="Error: employee is less than 18 years old";

ELSE

SET status_code=0;

SET status_message="OK";

END IF;

END;

DATEDIFF

days=DATEDIFF(date1,date2)

DATEDIFF returns the number of days between two dates. If date2 is greater than date1, then the result will be negative; otherwise, it will be positive.

Example 9-11 uses DATEDIFF to calculate the number of days that have elapsed since a bill due date, and returns appropriate status and messages if the bill is more than 30 or 90 days old.

Example 9-11. Using DATEDIFF

CREATE PROCEDURE check_billing_status

(in_due_date DATE,

OUT status_code INT,

OUT status_message VARCHAR(30))

BEGIN

DECLARE days_past_due INT;

SET days_past_due=FLOOR(DATEDIFF(now( ),in_due_date));

IF days_past_due>90 THEN

SET status_code=-2;

SET status_message='Bill more than 90 days overdue';

ELSEIF days_past_due >30 THEN

SET status_code=-1;

SET status_message='Bill more than 30 days overdue';

ELSE

SET status_code=0;

SET status_message='OK';

END IF;

END;

DAY

day=DAY(date)

DAY returns the day of the month (in numeric format) for the specified date.

SET var1=NOW( ); → 2005-07-23 13:47:13

SET var2=DAY(NOW( )); → 23

DAYNAME

day=DAYNAME(date)

DAYNAME returns the day of the week—as in Sunday, Monday, etc.—for the specified date.

SET var1=NOW( ); → 2005-07-23 13:50:02

SET var2=DAYNAME(NOW( )); → Saturday

DAYOFWEEK

day=DAYOFWEEK(date)

DAYOFWEEK returns the day of the week as a number, where 1 returns Sunday.

SET var1=NOW( ); → 2005-07-23 13:53:07

SET var2=DATE_FORMAT(NOW( ),"%W, %D %M %Y"); → Saturday, 23rd July 2005

SET var3=DAYOFWEEK(NOW( )); → 7

DAYOFYEAR

day=DAYOFYEAR(date)

DAYOFYEAR returns the day of the year as a number, where 1-JAN returns 1 and 31-DEC returns 365 (except in leap years, where it returns 366).

SET var1=NOW( ); → 2005-07-23 13:55:57

SET var2=DAYOFYEAR(NOW( )); → 204

EXTRACT

date_part=EXTRACT(interval_name FROM date)

EXTRACT returns a specified portion of a date-time. The applicable intervals are shown in Table 9-3.

SET var1=NOW( ); → 2005-07-23 14:01:03

SET var2=EXTRACT(HOUR FROM NOW( )); → 14

SET var3=EXTRACT(YEAR FROM NOW( )); → 2005

SET var4=EXTRACT(MONTH FROM NOW( )); → 7

SET var5=EXTRACT(HOUR_SECOND FROM NOW( )); → 140103

SET var6=EXTRACT(DAY_MINUTE FROM NOW( )); → 231401

GET_FORMAT

format=GET_FORMAT(datetime_type,locale)

GET_FORMAT returns a set of date formatting code—suitable for use with DATE_FORMAT—for various date-time types and locales.

Format type can be one of the following:

§ DATE

§ TIME

§ DATETIME

§ TIMESTAMP

Format code can be one of the following:

§ INTERNAL

§ ISO

§ JIS

§ USA

§ EUR

SET var1=GET_FORMAT(DATE,"USA"); → %m.%d.%Y

SET var2=GET_FORMAT(DATE,"ISO"); → %Y-%m-%d

SET var3=GET_FORMAT(DATETIME,"JIS") ; → %Y-%m-%d %H:%i:%s

SET var4=NOW( ); → 2005-07-24 13:27:58

SET var5=DATE_FORMAT(NOW( ),GET_FORMAT(DATE,"USA")); → 07.24.2005

MAKEDATE

date=MAKEDATE(year,day)

MAKEDATE takes the year (YYYY) and day-of-year arguments and converts them to a date value. The day-of-year argument is in the form that would be returned by DAYOFYEAR.

SET var1=MAKEDATE(2006,1); → 2006-01-01

SET var2=MAKEDATE(2006,365); → 2006-12-31

SET var3=MAKEDATE(2006,200); → 2006-07-19

MAKETIME

time=MAKETIME(hour,minute,second)

MAKETIME takes the hour, minute, and second arguments and returns a time value.

SET var4=MAKETIME(16,30,25); → 16:30:25

SET var5=MAKETIME(0,0,0); → 00:00:00

SET var6=MAKETIME(23,59,59); → 23:59:59

MONTHNAME

monthname=MONTHNAME(date)

MONTHNAME returns the full name of the month corresponding to the provided date.

SET var1=NOW( ); → 2005-07-24 13:44:54

SET var2=MONTHNAME(NOW( )); → July

NOW

datetime=NOW( )

NOW returns the current date and time. We have used this function in many previous examples as input to date and time functions.

SEC_TO_TIME

time=SEC_TO_TIME(seconds)

SEC_TO_TIME returns a time value for a given number of seconds. The time is shown in hours, minutes, and seconds.

SET var1=SEC_TO_TIME(1); → 00:00:01

SET var2=SEC_TO_TIME(3600); → 01:00:00

SET var3=SEC_TO_TIME(10*60*60); → 10:00:00

STR_TO_DATE

date=STR_TO_DATE(string,format)

STR_TO_DATE takes a string representation of a date (as might be returned by DATE_FORMAT) and returns a standard date data type in the format specified by the format argument. The format string is the same as that used in DATE_FORMAT; possible values are listed in Table 9-4.

SET var1=STR_TO_DATE("Sun 24 Jul 05","%a %d %b %y"); → 2005-07-24

SET var2=STR_TO_DATE("Sunday, 24th July 2005","%W, %D %M %Y"); → 2005-07-24

SET var3=STR_TO_DATE("3:53:54","%H:%i:%s"); → 03:53:54

SET var4=STR_TO_DATE("13:53:54","%T"); → 13:53:54

SET var5=STR_TO_DATE("01:53:54 PM","%r"); → 13:53:54

TIME_TO_SEC

seconds=TIME_TO_SEC(time)

TIME_TO_SEC returns the number of seconds in the specified time value. If a date-time is provided, TIME_TO_SEC provides the number of seconds in the time part of that date only.

SET var1=NOW( ); → 2005-07-24 14:05:21

SET var2=TIME_TO_SEC("00:01:01"); → 61

SET var3=TIME_TO_SEC(NOW( )); → 50721

TIMEDIFF

time=TIMEDIFF(datetime1,datetime2)

TIMEDIFF returns the time difference between two arguments specified as date-time data types.

SET var1=TIMEDIFF("2005-12-31 00:00:01","2005-12-31 23:59:59"); → -23:59:58

TIMESTAMP

datetime=TIMESTAMP(date,time)

TIMESTAMP returns a date-time value from a specified date and time.

SET var2=TIMESTAMP("2005-12-31","23:30:01"); → 2005-12-31 23:30:01

TIMESTAMPADD

date_time=TIMESTAMPADD(interval_type,interval_value,date_time)

TIMESTAMPADD adds the specified interval_value, which is of the interval_type data type, to the datetime provided and returns the resulting date-time.

Possible values for interval_type are listed in Table 9-3.

SET var1=NOW( ); → 2005-07-31 16:08:18

SET var2=TIMESTAMPADD(YEAR,100,NOW( )); → 2105-07-31 16:08:18

SET var3=TIMESTAMPADD(HOUR,24,NOW( )); → 2005-08-01 16:08:18

TIMESTAMPDIFF

interval_value=TIMESTAMPDIFF(interval_type,date_time1,date_time2)

TIMESTAMPDIFF returns the difference between two date-times, expressed in terms of the specified interval_type.

SET var1=NOW( ); → 2005-07-31 16:12:30

SET var2=TIMESTAMPDIFF(YEAR,NOW( ),"2006-07-31 18:00:00"); → 1

SET var3=TIMESTAMPDIFF(HOUR,NOW( ),"2005-08-01 13:00:00"); → 20

WEEK

number=WEEK(date_time[,start_of_week])

WEEK returns the number of weeks since the start of the current year. Weeks are considered to start on Sunday unless you specify an alternative start day (1=Monday) in the second argument.

SET var1=NOW( ); → 2005-07-31 16:20:09

SET var2=WEEK(NOW( )); → 31

WEEKDAY

number=WEEKDAY(date)

WEEKDAY returns the number for the current day of the week, with Monday returning a value of 0.

SET var1=NOW( ); → 2005-07-31 16:22:05

SET var2=DAYNAME(NOW( )); → Sunday

SET var3=WEEKDAY(NOW( )); → 6

YEAR

number=YEAR(datetime)

YEAR returns the year portion of the datetime argument, which is specified in date-time format.

SET var1=NOW( ); → 2005-07-31 16:27:12

SET var2=YEAR(NOW( )); → 2005

YEARWEEK

YearAndWeek=YEARWEEK(datetime[,StartOfWeek])

YEARWEEK returns the year and week of the year for the given date. Weeks are considered to start on Sunday unless you specify an alternative start day (1=Monday) in the second argument.

SET var1=NOW( ); → 2005-07-31 16:30:24

SET var2=DAYNAME(NOW( )); → Sunday

SET var3=YEARWEEK(NOW( )); → 200531

SET var4=YEARWEEK(NOW( ),1); → 200530

Other Date and Time Functions

Table 9-5 lists date and time functions not discussed in previous sections. Some of these are synonyms for functions we have discussed above, while others are rarely required in MySQL programming.

Table 9-5. Additional date-time functions

Other Functions

The miscellaneous built-in functions described in the following sections perform operations that do not fall into the categories described in earlier sections.

BENCHMARK

zero=BENCHMARK(no_of_repeats, expression)

BENCHMARK executes the specified expression repeatedly. It is intended to be used to benchmark MySQL performance. This function has very little applicability in a stored program context, although in theory you could use it to repeatedly execute a stored program.

COALESCE

value=COALESCE(value[,...])

COALESCE returns the first non-NULL value in the provided list of values.

SET var1=1; → 1

SET var2=2; → 2

SET var3=NULL; →

SET var4=COALESCE(var1,var2,var3); → 1

SET var5=COALESCE(var3,var2,var1) ; → 2

CURRENT_USER

username=CURRENT_USER( )

CURRENT_USER returns the username and hostname of the current MySQL user. It may report a different value from that returned by USER, since the USER function reports the connection requested by the user, rather than the connection that was actually used.

SET var1=CURRENT_USER( ); → root@%

SET var2=USER( ); → root@mel601439.quest.com

DATABASE

database_name=DATABASE( )

DATABASE returns the name of the database currently in use.

USE prod;

SET var1=database( ); → prod

GET_LOCK

return_code=GET_LOCK(lock_name,timeout)

GET_LOCK allows you to define and acquire a user-defined lock. The lock_name can be a string of your choice. GET_LOCK will attempt to acquire the lock; then, if no other session holds the lock, it will return 1. If the lock is held by another session, GET_LOCK will wait until timeout seconds has elapsed; then, if the lock can still not be acquired, it will return 0.

Only one "user" lock can be held at any time—that is, each invocation of GET_LOCK releases any previous locks.

GET_LOCK can be used to ensure that only one copy of a stored program is executing a particular segment of code at any one time. Note, however, that for most activities that might be performed by a stored program, table locking is preferable.

Example 9-12 provides an example of both the GET_LOCK and RELEASE_LOCK functions.

Example 9-12. Example of GET_LOCK and RELEASE_LOCK

CREATE PROCEDURE sp_critical_section( )

BEGIN

DECLARE lock_result INT;

IF get_lock('sp_critical_section_lock',60) THEN

/* This block can only be run by one user at a time*/

SELECT 'got lock';

/* Critical code here */

SET lock_result=release_lock('sp_critical_section_lock');

ELSE

SELECT 'failed to acquire lock';

/* Error handling here */

END IF;

END;

IFNULL

value1=IFNULL(value2,nullvalue)

IFNULL returns the value provided as value2. If that value is NULL, it returns the value provided in the second argument.

INTERVAL

position=INTERVAL(search,number, ...)

INTERVAL returns the position (starting at 0) that the search value would take within the specified list of numbers. The list must be in ascending order.

SET var2=INTERVAL(20,5,10,30,50); → 2

IS_FREE_LOCK

integer=IS_FREE_LOCK(string)

IF_FREE_LOCK returns 1 if the specified user-defined lock is available (e.g., not locked) and 0 if the lock is taken. See GET_LOCK.

ISNULL

integer=ISNULL(value)

ISNULL returns 1 if the parameter value is NULL and returns 0 otherwise.

NULLIF

value1=NULLIF(value2,value3)

NULLIF returns NULL if the two values provided are equal. Otherwise, it returns the first value.

RELEASE_LOCK

integer=RELEASE_LOCK(string)

RELEASE_LOCK releases a lock acquired by the GET_LOCK function. See GET_LOCK for more details and an example of usage.

SESSION_USER

Synonym for USER .

SYSTEM_USER

Synonym for USER.

USER

username=USER( )

USER returns the username and hostname for the current MySQL connection. This function reports the username and hostname that were used to establish the connection, while the CURRENT_USER function reports the username from the mysql.user table that is actually in use.

SET var1=CURRENT_USER( ); → root@%

SET var2=USER( ); → root@mel601439.quest.com

UUID

string=UUID( )

UUID returns a 128-bit Universal Unique Identifier (UUID). Each invocation of UUID returns a unique value. Part of the UUID is generated from your computer name and part from the current date and time. Therefore, you can be quite confident that UUIDs are unique across the world (subject to the very small chance that a computer with your exact configuration generated a UUID at the exact same time).

SET var1=UUID( ); → 7a89e3d9-52ea-1028-abea-122ba2ad7d69

SET var2=UUID( ); → 7a9ca65d-52ea-1028-abea-122ba2ad7d69

SET var3=UUID( ); → 7aa78e82-52ea-1028-abea-122ba2ad7d69

VERSION

string=VERSION( )

VERSION reports the current version of the MySQL server software.

SET var1=VERSION( ); → 5.0.18-nightly-20051211-log

In Example 9-13 we extract the major version of the version string and print an (impossible) error message if the version does not support stored programs.

Example 9-13. Using the VERSION function

CREATE PROCEDURE sp_mysql_version( )

BEGIN

DECLARE major_version INT;

SET major_version=SUBSTR(version(),1,INSTR(version( ),'.')-1);

IF major_version>=5 THEN

SELECT 'Good thing you are using version 5 or later';

ELSE

SELECT 'This version of MySQL does not support stored procedures',

'you must be dreaming';

END IF;

END;

This function returns the MySQL server version. There are no arguments for the function.

Conclusion

In this chapter we took a quick look at the built-in functions that you can use in your stored programs. In general, these are the same functions that you can use in standard MySQL. The only exception is that you cannot use aggregate functions that might be used in SQL statements that include a GROUP_BY clause.

We did not want to bulk up this book with verbose descriptions of every single function supported by MySQL. For functions not listed—or for those that received only cursory treatment in this chapter—refer to the MySQL Reference Manual available online (http://dev.mysql.com/doc/ ).