Realize oracle to_char,to_number(UDF) on mysql5.1

很多时候，我们都想代码简单化，但是数据库的不同，这样的想法无法成行，最近在设计一个新项目的时候，想使得应用程序通用，既能够跑在oracle上，也能够跑在mysql上，因此将oracle的一些basic的函数在mysql上也实现了一把,本来想使用procedure实现，但这样的基础函数被应用频繁调用，使用procedure性能肯定是问题，因此改用mysql UDF，目前已经实现的函数有to_char，to_number.
源码如下：
to_char(source code)


/*
* License: This code is in the public domain.
* realize oracle to_char function for mysql5.1.
*
* gcc -fPIC -Wall -I/home/oracle/mysql5.1.55/include/mysql  -shared -o to_char.so to_char.cc
* cp to_char.so  /home/oracle/mysql5.1.55/lib/mysql/plugin
* mysql -e "CREATE FUNCTION to_char RETURNS STRING  SONAME 'to_char.so'"
*
* If you get the error "ERROR 1126 (HY000): Can't open shared library
* 'to_char.so' (errno: 22  undefined symbol:
* __gxx_personality_v0)" then you may need to use g++ instead of gcc.
*
* By:philip zhong
* email:philip.zhong1980@gmail.com
* blog:http://philipzhong.blogspot.com
* Share and Enjoy! :-)
*/
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <ctype.h>
#include <string.h>
#include <m_ctype.h>
#include <m_string.h>


void double2str(double in,char* out)  
{  
    double e=1e-8;  
    unsigned int i=0,j=0;  
    while(e<in)  
    {  
        e*=10;  
        i++;  
    }  
    while(e>1e-8 && j<8)  
    {   e/=10;  
        j++;i--;  
        if((char)(in/e+48)=='/')(*out++)=0;  
        else  
        (*out++)=(char)(in/e+48);  
        if(e==1.0)(*out++)='.';  
        in=in - ((int)(in/e))*e;  
    }  
    if(i>8)for(j=i;j>8;j--)(*out++)='0';  
    *out=0;  
}  

/* Prototypes */
extern "C" {
   my_bool  to_char_init(UDF_INIT* initid, UDF_ARGS* args, char* message );
   char     *to_char(UDF_INIT *initid, UDF_ARGS *args,char *result, unsigned long *length,char *is_null, char *error);
   void     to_char_deinit(UDF_INIT *initid);
}

//to_char
my_bool to_char_init( UDF_INIT* initid, UDF_ARGS* args, char* message ) {
   if (args->arg_count == 0 || args->arg_count>1) {
      strcpy(message,"to_char requires at least one argument");
      return 1;
   }
   initid->const_item = 1;
   return 0;
}

//to_char
char *to_char(UDF_INIT *initid, UDF_ARGS *args,char *result, unsigned long *length,char *is_null, char *error)
{
  uint i;
  for (i = 0; i < args->arg_count; i++)
  {
    if (args->args[i] != NULL)
{
    switch (args->arg_type[i])
{
     case STRING_RESULT:
     case DECIMAL_RESULT:
{
   *length = args->lengths[i];
        strcpy(result, args->args[i]);
        return result;
     }
    case INT_RESULT:
{
  char int_buffer[65];
  long long int_val;
       int_val = *((long long*) args->args[i]);
  ullstr(int_val,int_buffer);
  *length = args->lengths[i];
  strcpy(result, int_buffer);
       return result;
}
    case REAL_RESULT:
{
   double real_val;
char real_buffer[80];
        real_val = *((double*) args->args[i]);
double2str(real_val,real_buffer);
       *length =args->lengths[i];
strcpy(result, real_buffer);
        return result;
}
    default:
      break;
    }
   }
 }
  return result;
}

void to_char_deinit(UDF_INIT *initid)
{
}

to_number(source code):

/*
* License: This code is in the public domain.
* merge oracle to_char, to_number functions into mysql5.1.
*
* gcc -fPIC -Wall -I/home/oracle/mysql5.1.55/include/mysql -shared -o to_number.so to_number.cc
* cp to_number.so  /home/oracle/mysql5.1.55/lib/mysql/plugin
* mysql -e "CREATE FUNCTION to_number RETURNS REAL  SONAME 'to_number.so'"
*
* If you get the error "ERROR 1126 (HY000): Can't open shared library
* 'to_number.so' (errno: 22  undefined symbol:
* __gxx_personality_v0)" then you may need to use g++ instead of gcc.
*
* By:philip zhong
* email:philip.zhong1980@gmail.com
* blog:http://philipzhong.blogspot.com
* Share and Enjoy! :-)
*/

#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <string.h>
#include <m_string.h>

/* Mysql UDF defined functions */
extern "C" {
   my_bool  to_number_init(UDF_INIT* initid, UDF_ARGS* args, char* message );
   double   to_number(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error );
   void     to_number_deinit(UDF_INIT *initid);
}


//to_number,need add exception process.
my_bool to_number_init( UDF_INIT* initid, UDF_ARGS* args, char* message ) {
   if (args->arg_count!=1) {
      strcpy(message,"to_number requires at least one argument");
      return 1;
   }
   initid->maybe_null = 0;      /* The result will never be NULL */
   return 0;
}

//to_number
double to_number( UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error ) {

    if (args->args[0] != NULL)
{
    switch (args->arg_type[0])
{
case DECIMAL_RESULT:
    case STRING_RESULT:
{
   return my_atof((char *)args->args[0]);
     }
case REAL_RESULT:
{
        return *((double*) args->args[0]);
}
    case INT_RESULT:
{
        return (double) *((long long*) args->args[0]);
}
    default:
      break;
    }
   }
   return 0.0;
}

void to_number_deinit(UDF_INIT *initid)
{
}



目前是beta版，大家在使用的过程中有bug，可以email给我。

下面是UDF与cast函数的性能测试结果比较：

mysql> select count(to_number(name)) from test_udf;
+------------------------+
| count(to_number(name)) |
+------------------------+
| 8188608 |
+------------------------+
1 row in set (2.78 sec)

mysql> select count(CAST(name as DECIMAL)) from test_udf;
+------------------------------+
| count(CAST(name as DECIMAL)) |
+------------------------------+
| 8188608 |
+------------------------------+
1 row in set (3.93 sec)

mysql> select count(to_char(name)) from test_udf t;
+----------------------+
| count(to_char(name)) |
+----------------------+
| 8188608 |
+----------------------+
1 row in set (3.81 sec)

mysql> select count(CAST(name as char(30))) from test_udf;
+-------------------------------+
| count(CAST(name as char(30))) |
+-------------------------------+
| 8188608 |
+-------------------------------+
1 row in set (4.45 sec)