MySQL User Defined Function Examples
Posted: Jul 18, 2009
I'm currently in the process of converting one of my web applications from static tables all generated on the server side to a YUI based implementation which uses a lot of JavaScript calls to get the data on the client side to display. This has some distinct advantages but also some disadvantages.
For instance, where I used to use this PHP function to convert a file size (in bytes) to a string that would display nicely:
I need to now use a SQL function to do the processing. (I could still use PHP, but that would require processing the data more than I want to). I've been doing a lot of work in PL/SQL lately so I decided to write a MySQL UDF to accomplish this instead of a huge case statement. While there are a lot of examples for PL/SQL functions, I had a hard time finding a good example of what a MySQL UDF looked like so I thought I would post this in case anyone else is going through what I did.
BTW, in the MySQL documentation it shows the DECLARE statement but doesn't emphasize enough that the DEFAULT is mandatory. It took me a while to figure that out.
For instance, where I used to use this PHP function to convert a file size (in bytes) to a string that would display nicely:
/**
* Format Bytes to a human readable format
* @param int $BYTES Size in bytes
* @param string $SIZE Format to use (k,m,g,t,best)
* @param int $DECIMALS Format string to use in
* @return string Formated string
* @todo Support k-t size
* @deprecated Use the MySQL UDF to do this in database
*/
function byteFormat($BYTES, $SIZE="BEST", $DECIMALS=2){
if(substr(strtolower($SIZE),0,1) == "b"){
if($BYTES > pow(1024,4)){
$SIZE = "t";
} elseif($BYTES > pow(1024,3)){
$SIZE = "g";
} elseif($BYTES > pow(1024,2)){
$SIZE = "m";
} else {
$SIZE = "k";
}
}
switch(substr(strtolower($SIZE),0,1)){
case "k":
$FORMATED_DATA = number_format(($BYTES / pow(1024,1)),$DECIMALS) . "kb";
break;
case "m":
$FORMATED_DATA = number_format(($BYTES / pow(1024,2)),$DECIMALS) . "mb";
break;
case "g":
$FORMATED_DATA = number_format(($BYTES / pow(1024,3)),$DECIMALS) . "gb";
break;
case "t":
$FORMATED_DATA = number_format(($BYTES / pow(1024,4)),$DECIMALS) . "tb";
break;
}
return $FORMATED_DATA;
}
DROP FUNCTION IF EXISTS byteFormat;
CREATE FUNCTION byteFormat (bytes INTEGER
,desiredSize VARCHAR(30)
,decimalPlaces INTEGER)
RETURNS VARCHAR(255)
BEGIN
DECLARE formatedString VARCHAR(250) DEFAULT '';
IF LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'b' THEN
SET desiredSize = CASE
WHEN (bytes > POWER(1024,4)) THEN 't'
WHEN (bytes > POWER(1024,3)) THEN 'g'
WHEN (bytes > POWER(1024,2)) THEN 'm'
ELSE 'k'
END;
END IF;
SET formatedString = CASE
WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'k' THEN CONCAT((FORMAT(bytes / POWER(1024,1),2)) , 'kb')
WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'm' THEN CONCAT((FORMAT(bytes / POWER(1024,2),2)) , 'mb')
WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'g' THEN CONCAT((FORMAT(bytes / POWER(1024,3),2)) , 'gb')
WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 't' THEN CONCAT((FORMAT(bytes / POWER(1024,4),2)) , 'tb')
END;
RETURN formatedString;
END;
BTW, in the MySQL documentation it shows the DECLARE statement but doesn't emphasize enough that the DEFAULT is mandatory. It took me a while to figure that out.
