Numeric functions perform operations on numeric data types such as INT and FLOAT.
9.2.1. ABS
number1 =ABS (number2 )
ABS returns the absolute value of a numberthat 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
9.2.2. 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
9.2.3. 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
9.2.4. 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
9.2.5. 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
9.2.6. 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
9.2.7. 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
9.2.8. 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
9.2.9. 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 0which happens every 100 rowsa 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
9.2.10. 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
9.2.11. 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; |
9.2.12. ROUND
integer =ROUND (number [,decimals ])
ROUND converts a floating-point number to the nearest integer value orif the second argument is specifiedto 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
9.2.13. 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
9.2.14. 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
9.2.15. 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!
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. |
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Language Fundamentals
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Error Handling
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
Transaction Management
MySQL Built-in Functions
Stored Functions
Triggers
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development