Faisal Kaleem

Some useful MySQL functions that might make ones life easy

Many developers working in web development does not know the power of database system. They are continuously using the old techniques that makes mess in the code. In any application whether its a web or desktop application there needs many operations on data, some time we have to format data in various shapes to display them at many places. Most of the new developers use simple query to fetch any apply some built-in or custom function to format the data that makes too much code and could create lot of code management issue in future. I am going to share some useful function that could help you writing your queries and you really don’t need your custom or built-in function at many places, thus helps you writing clean and efficient code quickly.

• DEFAULT(col_name): This function returns the default value of a table column.

  Example:

  UPDATE table_name SET col = DEFAULT(col)+1 WHERE id < 100;

• FORMAT(X,D): It Formats the number X to a format like ‘#,###,###.##’ and round it D decimal palces and returns as a string.

  Example:

  SELECT FORMAT(12432.123456, 3);

  Retruns: 12,432.123

• INET_ATON(expr): It converts the Network (aka IP) address to a numeric presentation in integer format .

  Example:

  SELECT INET_ATON('192.168.137.1');
  

  Returns: 3232270593

• INET_NTOA(expr): It converts the numeric Network address in network byte order (4 or 8 byte) to dotted quad representation as a string.

  Example:

  SELECT INET_NTOA(3232270593);
  

  Returns: ’192.168.137.1′

• SLEEP(duration): This function causes a sleep or pause for give number of seconds given by duration parameter.
• UUID(): This function give a Universal Unique Identifier. This identifier is generated according to “DCE 1.1: Remote Procedure Call”. UUID is designed to be unique in space and time. So two number generated by UUID() will return two different number even if calls were made on two different computers. UUID is a 128 bit number in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format. The first three numbers are generated form timestamp, the fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (consider the case: daylight saving time) and the fifth number is an IEEE 802 node number (Physical address or MAC) that provides spatial uniqueness. But this fifth number si only workin on FreeBSD and linux on other operating system MySQL uses a 48bit random number thus causes a very low probability of collision.

  Example:

  SELECT UUID();

  Returns: ’6ccd780c-baba-1026-9564-0040f4311e29′

In my next posts I will post some useful string functions that will help you in routine programming. Come soon to enjoy my posts.

Tags: inet_aton, MySQL, userfull MySQL functions, UUID