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

Function

Syntax

Description

BINARY

string1=BINARY(string2)

Returns the binary representation of a string. This function can be used to force case-sensitive comparisons when they would otherwise not occur.

BIT_LENGTH

bits=BIT_LENGTH(string)

Returns the number of bits in a string.

CHAR_LENGTH

length=CHAR_LENGTH(string)

Returns the number of characters in a string. Like LENGTH, except that it returns the number of characters, rather than the number of bytes, for multibyte character sets.

CHARACTER_LENGTH

length=CHARACTER_LENGTH(string)

Alias for CHAR_LENGTH.

COMPRESS

string1=COMPRESS(string2)

Returns a compressed version of a string.

DECODE

string1=DECODE(string2,password)

Decrypts a string that has been encrypted with ENCODE .

ELT

string1=ELT(number,string2[,...])

Returns one of the elements in a list.

ENCODE

string1=ENCODE(string2,password)

Encrypts a string. The string can be decrypted with DECODE.

ENCRYPT

string1=ENCRYPT(string2,seed)

Encrypts a string. The string cannot be decrypted with DECODE.

EXPORT_SET

string=ENCODE_SET(number,on_stringoff_string,seperator,no_of_bits)

Returns the binary representation of a number encoded with strings for on and off bits.

FIELD

number=FIELD(string1,string2[,...])

Searches for a string in a list of strings.

INET_ATON

number=INET_ATON(IPAddress)

Converts an IP address into a numeric representation.

INET_NTOA

IPAddress=INET_NTOA(number)

Converts a number into a corresponding IP address.

LOWER

string1=LOWER(string2)

Synonym for LCASE.

MID

string1=MID(string2,start [,length])

Returns a substring. Similar to SUBSTR.

OCTET_LENGTH

length=OCTET_LENGTH(string)

Alias for LENGTH.

ORD

position=ORD(string)

Returns the ordinal value of the character in the ASCII character set.

PASSWORD

string1=PASSWORD(string2)

Encrypts the given string as a MySQL password.

POSITION

position=POSITION(substring IN string)

Returns the position of the substring in the string. Similar to LOCATE.

QUOTE

string1=QUOTE(string2)

Returns a string with special characters preceded by an escape character.

REVERSE

string1=REVERSE(string2)

Reverses the order of characters in a string.

RIGHT

string1=RIGHT(string2,length)

Returns the rightmost portion of a string.

SHA

string1=SHA(string2)

Returns a 160-bit Secure Hash Algorithm (SHA) checksum for the string.

SHA1

string1=SHA1(string2)

Alias for SHA.

SOUNDEX

string1=SOUNDEX(string2)

Returns the SOUNDEX for a string. In theory, two strings that "sound alike" will have similar SOUNDEX values.

SPACE

spaces=SPACE(count)

Returns the specified number of space characters.

SUBSTRING_INDEX

string1=SUBSTRING_INDEX(string2delimiter,count)

Returns a string from a character-delimited set of strings.

UNCOMPRESSED_LENGTH

length=UNCOMPRESSED_LENGTH( compressed_string)

Returns the length of a compressed string as if it were decompressed.

UNCOMPRESS

string1=UNCOMPRESS(string2)

Reverses the effect of COMPRESS.

UNHEX

character=UNHEX(HexNumber)

Converts a hexadecimal number to its ASCII equivalent.

UPPER

string1=UPPER(string2)

Converts a string to uppercase. Synonym for UCASE.

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

Function

Syntax

Description

ACOS

number1=ACOS(number2)

Arc cosine of a number.

ASIN

number1=ASIN(number2)

Arc sine of a number.

ATAN

number1=ATAN(number2)

Arc tangent of a number.

COT

number1=COT(number2)

Cotangent of a number.

CRC32

number=CRC32(string)

Cyclic redundancy check value for a string.

DEGREES

degrees=DEGREES(radians)

Converts radians to degrees.

EXP

number1=EXP(number2)

Natural logarithm (base e) to the power of a number.

LN

number1=LN(number2)

Natural logarithm of a number.

LOG

number1=LOG(number2,base)

Logarithm of a number in the base specified.

LOG10

number=LOG10(number2)

Base 10 logarithm of a number.

LOG2

number1=LOG2(number)

Base 2 logarithm of a number.

PI

number=PI( )

Returns the value of PI.

RADIANS

radians=RADIANS(degrees)

Converts radians to degrees.

SIN

number1=SIN(number2)

Sine of a number (expressed in radians).

TAN

number1=TAN(number2)

Tangent of a number expressed in radians.

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

Interval name

Interval format

DAY

dd

DAY_HOUR

ddhh

DAY_MINUTE

dd hh:mm

DAY_SECOND

dd hh:mm:ss

HOUR

hh

HOUR_MINUTE

hh:mm

HOUR_SECOND

hh:mm:ss

MINUTE

mm

MINUTE_SECOND

mm:ss

MONTH

mm

SECOND

ss

YEAR

yyyy

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

Code

Explanation

%%

The % sign

%a

Short day of the week (Mon-Sun)

%b

Short month name (Jan-Feb)

%c

Month number (1-12)

%d

Day of the month (1-31)

%D

Day of the month with suffix (1st, 2nd, 3rd, etc.)

%e

Day of the month, numeric (1-31)

%h

12-hour clock hour of the day (1-12)

%H

24-hour clock hour of the day (00-23)

%i

Minute of the hour (00...59)

%I

12-hour clock hour of the day (1-12)

%j

Day of the year (1-365)

%k

24-hour clock hour of the day (00-23)

%l

12-hour clock hour of the day (1-12)

%m

Month of the year (1-12)

%M

Long month name (January-December)

%p

AM/PM

%r

Hour, minute, and second of the day, 12-hour format (hh:mm:ss AM|PM)

%s

Seconds within a minute (0-59)

%S

Seconds within a minute (0-59)

%T

Hour, minute, and second of the day, 24-hour format (HH:mm:ss)

%u

Week of the year (0-52) (Monday is the first day of the week)

%U

Week of the year (0-52) (Sunday is the first day of the week)

%v

Week of the year (1-53) (Monday is the first day of the week)

%V

Week of the year (1-53) (Sunday is the first day of the week)

%w

Numeric day of the week (0=Sunday, 6=Saturday)

%W

Long weekday name (Sunday, Saturday)

%y

Year, numeric, 2 digits

%Y

Year, numeric, 4 digits

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

Function

Syntax

Description

ADDDATE

datetime=ADDDATE(date,interval_value, intervaltype)

Synonym for DATE_ADD.

CURDATE

datetime=CURDATE( )

Alias for NOW.

CURTIME

time=CURTIME( )

Current time.

DAYOFMONTH

day=DAYOFMONTH(datetime)

Day of the month.

FROM_DAYS

days=FROM_DAYS(datetime)

Number of days since the start of the current calendar.

HOUR

number=HOUR(datetime)

Hour of the day for the given date.

LAST_DAY

date=LAST_DAY(date)

Returns the last day of the month for the given date.

LOCALTIME

datetime=LOCALTIME( )

Synonym for NOW.

LOCALTIMESTAMP

datetime=LOCALTIMESTAMP( )

Synonym for NOW.

MICROSECOND

microseconds=MICROSECOND(datetime)

Microsecond portion of the provided time.

MINUTE

minute=MINUTE(datetime)

Minute part of the given time.

MONTH

month=MONTH(datetime)

Month part of the given time.

PERIOD_ADD

date=PERIOD_ADD(year_month, months)

Adds the specified number of months to the provided year_month value.

PERIOD_DIFF

date=PERIOD_DIFF( year_month_1,year_month_2)

Returns the number of months between the two year_month values provided.

QUARTER

quarter=QUARTER(datetime)

Returns the quarter of the given date.

SECOND

seconds=SECOND(datetime)

Returns the seconds portion of the provided datetime.

SUBDATE

date1=SUBDATE(date2, interval_value, interval_type)

Synonym for DATE_SUB.

SUBTIME

datetime1=SUBTIME(datetime2, time)

Subtracts the time from the datetime.

SYSDATE

datetime=SYSDATE( )

Synonym for NOW.

TO_DAYS

datetime=TO_DAYS(days)

Adds the days argument to the start of the standard calendar.

WEEKOFYEAR

week=WEEKOFYEAR(datetime)

Synonym for WEEK.

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/ ).