Top 10 MySQL Functions

Posted by Andrey in Programming, Query Languages | 4 comments

Introduction

As of MySQL 5.0 built-in (native) functions, user-defined functions and stored functions are now supported by the mysql engine. Although these functions are widely available to the developer their use isn’t fully exploited. In this article I am going to present the top MySQL functions that come in handy.

Hash functions

MySQL natively supports the fallowing hash functions:

1. MD5(string);
2. SHA(string);

SELECT MD5('area72') as md5,
       SHA('area72') as sha

-- OUTPUT: MD5 e82b6069c7e968485f0de6d756d622dd
-- OUTPUT: SHA 4feb5546a60f18df755395d1ff39e29a2cab7234

Cryptographic functions

To my surprise I also found out that MySQL also supports some basic cryptographic functions like AES and DES, functions that have a high degree of security especially AES which I recommend being the toughest to crack at the moment.

1. AES_ENCRYPT(string, key) AES_DECRYPT(string, key);
2. DES_ENCRYPT(string, key) DES_DECRYPT(string, key);
3. ENCODE DECODE(string, key);

SELECT AES_DECRYPT(
                   AES_ENCRYPT('text to encrypt', 'secret key'),
                  'secret key') as encryption;
-- OUTPUT: text to encrypt

Explanation: As you can see the output is the same as the text we wanted to encrypt, this is because we encrypted and as soon as we encrypted it we decrypted it back giving the original text.

AES_ENCRYPT() and AES_DECRYPT() can be considered the most cryptographically secure encryption functions currently available in MySQL.

Date functions

Some basic date functions that can ease up searching or comparison

1. ADDDATE()
2. CURDATE()

SELECT ADDDATE('2010-05-01', 7) as nextweek, CURDATE() as today;
SELECT ADDDATE(CURDATE(), 7) as nextweek;

-- OUTPUT: 2010-05-08, 2010-05-01
-- OUTPUT: 2010-05-08

As you can see we can also use them together and we will always have the desired result, the second method is recommended for use.

Text functions

COMPRESS() AND UNCOMPRESS()

Having Long Text in your database will affect both your performance and HDD usage so it is recommended that you compress long texts using the compress function and when you need it just uncompress it.

SELECT LENGTH(COMPRESS(REPEAT('A', 1000000)));
-- OUTPUT: ~1000

As you see having 1 million A’s compressed reduces their number to approximately 1000.

REGEXP()

Yes! REGEXP in MySQL queries, no introduction required.

id	name
1	Samsung Widescreen 1080p
2	Geforce GT 8800
3	Mini Tv
4	Audio System
5	HTC PRO 2

SELECT id FROM table WHERE name REGEXP '[0-9]';
-- OUTPUT: 1, 2, 5

Using the query above we successfully selected the id of the items that have numbers in their name, although this example is simple just think of the possibilities of using REGEXP in your queries.

Miscellaneous functions

These two functions I guess do not need any further introduction

1. RAND()
2. COUNT()

SELECT * FROM table ORDER BY RAND();
SELECT COUNT(*) FROM table;

The RAND() function is most commonly used to order the results of a table in a randomly order, for example if you want to display random images in your homepage.

The COUNT() function returns the total number of rows in a query.

Conclusion

I hope that this article will get you more involved in using functions already available to you rather than using third-party code, just for curiosity did this article seem interesting to you ? Give some feedback.

Related posts:

1. Extracting a random row from sql database
2. Simple XOR Encryption Algorithm
3. Recursive Functions
4. Autocomplete Combo Box in C# with SQL values