Other Math Functions - MySQL

As noted earlier, ABS() returns the absolute value of its argument. The SIGN() function (known in mathematics as the signum function and often written sgn(x) ) tests the sign of its argument, and returns 1 if the argument is positive, –1 if it’s negative, and 0 if the argument is 0.

To obtain the square root of a number, use the SQRT() function, as shown here:

It’s often useful or necessary in programming to generate random numbers. MySQL provides this capability using the RAND() function, which returns a pseudo-random floating-point value from 0 to 1. An integer may be passed to this function as an optional argument; this serves to “seed” the function and allows you to produce a fairly random but repeatable sequence of values. You can also use RAND() to obtain a set of values in random order from a query by including it in an ORDER BY clause.

Conversion Functions

In nearly any programming application, you encounter situations in which it’s necessary to convert data from one type to another. For example, you may have a variable whose value is the string "535" , and you may need to be able to treat this like the number 535 in order to multiply it by another value. Much the same is true in MySQL. In many cases, MySQL handles the conversion for you. For example, if you try to multiply the number 5 by the string "5", the latter is automatically converted to a number, so that the result is the number 25. However, there are times when MySQL’s built-in type conversions don’t do what you want them to, and you need to force the issue.

MySQL has two types of conversion functions for this purpose: the first is for casting datatypes to other datatypes (and in MySQL 4.01 and newer, for converting strings in one character set to another), and the second is for converting numbers in one base to another.

Type Conversions

While MySQL generally does a pretty good job of converting between datatypes, there may be times when it’s necessary to perform such conversions explicitly. Beginning in MySQL 4.0.2, two functions are provided for this purpose. Both CAST() and CONVERT() take an argument of one type and attempt to return the same value converted to a different datatype. As of MySQL 4.1, CONVERT() can also be used to convert strings from one character set to another.

The CAST() function has the following syntax:

CAST(expression AS type)

where expression is a value or an expression that evaluates to a value, and type is one of the following datatypes:

• BINARY (Note that BINARY colname  is an alias for CAST(colname  AS BINARY)) 
  
• CHAR (available in MySQL 4.0.6 and later)
  
• DATE
  
• DATETIME
  
• SIGNED or SIGNED INTEGER
  
• TIME
  
• UNSIGNED or UNSIGNED INTEGER

When used to convert between datatypes, the CONVERT() function has this syntax:

CONVERT(expression,  type)

This function can use any of the same values for type as CAST() does.

These functions are most useful when creating a new table using CREATE ... SELECT ... or copying data between tables using an INSERT ... SELECT ... query. For instance, suppose that you have a TEXT column in a table that you would like to have sorted using case-sensitivity. Instead of using BINARY textcol  in each of your queries, you can copy the table definition and use this statement:

CREATE newtable  SELECT CAST(textcol  AS BINARY) FROM oldtable;

or this one:

INSERT INTO newtable  SELECT CAST(textcol  AS BINARY) FROM oldtable;

In the latter case, you’ll need to make sure that the column into which you’re selecting was declared as BINARY. (If you copied the definition of the old table, you’ll need to change the column type using ALTER TABLE.)

The second form of CONVERT() has the following syntax:

CONVERT(expression USING encoding)

where encoding is the name of a character set, such as in this example:

SELECT CONVERT('I like MySQL' USING utf8);

You can see which character sets are available on your installation by using SHOW CHARACTER SET; .

---

NOTE  In this book, we assume the use of the default Latin 1 (latin1) character set and latin1_swedish_ci collation. For information about configuring other character sets and collations, consult the MySQL Manual.

---