Some Under-Used MySQL Functions

Discussion in 'MySQL' started by pradeep, Aug 16, 2012.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    MySQL is a matured RDMBS with numerous built-in functions in various categories like strings, numbers, conditional operators, etc. Some of these functions have really good potential to help out developers, but it seems these functions are not known to the majority of developers, and therefore are under-used. Here we'll discuss about some such functions with the practical examples.

    IFNULL Function



    The IFNULL function would really be useful if you would like to return a default value in case the field has a NULL value, and you wouldn't need to check in your program code for the same. Some other RDBMS too provide such functions, like Oracle.

    Code:
    mysql> SELECT * FROM t1;
    +----+-----------+
    | id | some_text |
    +----+-----------+
    |  1 | aaa       |
    |  2 | bbb       |
    |  3 | ccc       |
    |  5 | AAAA      |
    |  0 | 6         |
    |  6 | NULL      |
    +----+-----------+
    6 rows in set (0.00 sec)
    
    mysql> SELECT id,IFNULL(some_text,'BLANK') FROM t1;
    +----+---------------------------+
    | id | IFNULL(some_text,'BLANK') |
    +----+---------------------------+
    |  1 | aaa                       |
    |  2 | bbb                       |
    |  3 | ccc                       |
    |  5 | AAAA                      |
    |  0 | 6                         |
    |  6 | BLANK                     |
    +----+---------------------------+
    6 rows in set (0.00 sec)
    

    DATE_FORMAT Function



    A very common routine I have seen developers do is get a date/time value from the database and then format it according to their needs, but MySQL provides a function to format your date/time just like you would do in your code, this function would especially come in handy while generating CSV reports out of just queries.

    Code:
    mysql> SELECT DATE_FORMAT(NOW(),'%D %b %Y %T');
    +----------------------------------+
    | DATE_FORMAT(NOW(),'%D %b %Y %T') |
    +----------------------------------+
    | 16th Aug 2012 18:26:45           |
    +----------------------------------+
    1 row in set (0.02 sec)
    
    mysql> SELECT DATE_FORMAT(NOW(),'%D %M, %Y');
    +--------------------------------+
    | DATE_FORMAT(NOW(),'%D %M, %Y') |
    +--------------------------------+
    | 16th August, 2012              |
    +--------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT DATE_FORMAT(NOW(),'%D %M, %Y %h:%m %p');
    +-----------------------------------------+
    | DATE_FORMAT(NOW(),'%D %M, %Y %h:%m %p') |
    +-----------------------------------------+
    | 16th August, 2012 06:08 PM              |
    +-----------------------------------------+
    1 row in set (0.00 sec)
    
    You can get the full list of format specifier strings at http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

    GROUP_CONCAT Function



    I had written a complete article on [THREAD=28525]GROUP_CONCAT in MySQL[/THREAD] some weeks back, it is also one immensely under-used function. You can use this function to get comma separated values of field of grouped rows when using GROUP BY clause.

    Code:
    mysql> SELECT country,GROUP_CONCAT(language SEPARATOR '#') FROM languages GROUP BY country;
    +---------+--------------------------------------+
    | country | GROUP_CONCAT(language SEPARATOR '#') |
    +---------+--------------------------------------+
    | CN      | Tibetan#Mandarin                     |
    | FR      | French                               |
    | IN      | Urdu#Hindi#Tamil#Gujrati             |
    | LK      | Sinhala#Tamil                        |
    | PK      | Urdu                                 |
    +---------+--------------------------------------+
    

    UUID Function



    We have employed many techniques to generate unique ids to various database rows, and many developers may have brewed their own logic, and devised new ones from known ideas, but UUID is a worldwide standard.

    Code:
    mysql> SELECT UUID();
    +--------------------------------------+
    | UUID()                               |
    +--------------------------------------+
    | 7cf18aea-38f4-1030-bf1c-00163e12264c |
    +--------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT UUID();
    +--------------------------------------+
    | UUID()                               |
    +--------------------------------------+
    | 7e7a55ae-38f4-1030-bf1c-00163e12264c |
    +--------------------------------------+
    1 row in set (0.00 sec)
    

    FIND_IN_SET



    FIND_IN_SET helps you look up a specific value in a list of comma separted values, if the value you are looking for exists the postion of the value in the set, else returns 0.

    Code:
    mysql> SELECT FIND_IN_SET('A','A,B,C,D,E,F,G,H,I,A');
    +----------------------------------------+
    | FIND_IN_SET('A','A,B,C,D,E,F,G,H,I,A') |
    +----------------------------------------+
    |                                      1 |
    +----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT FIND_IN_SET('D','A,B,C,D,E,F,G,H,I,A');
    +----------------------------------------+
    | FIND_IN_SET('D','A,B,C,D,E,F,G,H,I,A') |
    +----------------------------------------+
    |                                      4 |
    +----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT FIND_IN_SET('X','A,B,C,D,E,F,G,H,I,A');
    +----------------------------------------+
    | FIND_IN_SET('X','A,B,C,D,E,F,G,H,I,A') |
    +----------------------------------------+
    |                                      0 |
    +----------------------------------------+
    1 row in set (0.00 sec)
    

    STRCMP Function



    STRCMP is the same as the C implementation, probably it's inspired by the C function. The function accepts 2 strings are argument, if the first string is less than the second one then the function returns -1, if both are equal it returns 0 and if the first string is greater than the second 1 is returned.

    Code:
    mysql> SELECT STRCMP('Asha','Asha');
    +---------------------------+
    | STRCMP('Anjali','Anjali') |
    +---------------------------+
    |                         0 |
    +---------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT STRCMP('Anjali','Sweetu');
    +---------------------------+
    | STRCMP('Anjali','Sweetu') |
    +---------------------------+
    |                        -1 |
    +---------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT STRCMP('Pradeep','Anjali');
    +----------------------------+
    | STRCMP('Pradeep','Anjali') |
    +----------------------------+
    |                          1 |
    +----------------------------+
    1 row in set (0.00 sec)
    
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice