Home arrow MySQL arrow Page 3 - Optimizing Queries with Operators, Branching and Functions, continued

Other Math Functions - MySQL

This article will give you a good grounding in operators, branching and functions in MySQL, so you can make the database, instead of your own code, do the bulk of the work. It is the second of three parts, and excerpted from chapter four of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324).

  1. Optimizing Queries with Operators, Branching and Functions, continued
  2. Trigonometric Functions
  3. Other Math Functions
  4. Conversion of Numbers Between Bases
  5. String Functions and Regular Expressions
  6. Pattern-Matching and Regular Expressions
By: Apress Publishing
Rating: starstarstarstarstar / 6
April 06, 2006

print this article



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
  • TIME

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:


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

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.

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: