Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   Some Under-Used MySQL Functions (http://www.go4expert.com/articles/mysql-functions-t28893/)

pradeep 16Aug2012 18:55

Some Under-Used MySQL Functions
 
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/...on_date-format

GROUP_CONCAT Function



I had written a complete article on GROUP_CONCAT in MySQL 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)



All times are GMT +5.5. The time now is 05:37.