Implementation of "Post count" of HashTag with Mysql stored proc

Recently, I am making a feature similar to Instagram HashTag search in my app, which is searching the hashtag with post count with the given keyword.

To do this, I try to keep the post count in the Tag record and get it when do the search. However, to complete this, I need to handle the way to increase the count when a new post is created.

To do so, I had chosen using stored proc to do this task.

What’s and Why stored procedure ? 

Combine several SQL in a named procedure. It can make the application code more simple.

Before using procedure:
We need to use two SQLs to handle

UPDATE table SET count = count + 1 WHERE tag = ‘myTag’
SELECT count FROM table WHERE tag = ‘myTag’

After using procedure:
Just simply use the following SQL

CALL increase_post_count(‘myTag’)

Stored procedure of “Increase Post count” 

Define the procedure 

DELIMITER //
CREATE PROCEDURE inc_post_count
(
   IN inputTag VARCHAR(100)
)
BEGIN
    UPDATE tbl_hashtag SET postCount = postCount + 1 WHERE tag = inputTag ;
    SELECT postCount FROM tbl_hashtag WHERE tag = inputTag ;
END //
DELIMITER ;

Note: The above statement is working mysql client but not in phpMyAdmin

Using the procedure

CALL inc_post_count(‘MyTag’) 

Make your Mysql friendly to Emoji character

In my last projects, Mysql is usually UTF-8 as the core encoding. However, it is not good for my current project now because we will play with Emoji Character;

Emoji Character is using an extended utf-8 Encoding, called UTF-8 mb4 (utf8mb4).

If inserting string with Emoji Character, the mysql will alert "incorrect string" and make the SQL fail.
To fix this, we need to:

• Change the database and table to utf8mb4 encoding 
• Change the connection encoding to utf8mb4 as well

External Reference:

• http://mathiasbynens.be/notes/mysql-utf8mb4
• http://info.michael-simons.eu/2013/01/21/java-mysql-and-multi-byte-utf-8-support/
• http://my.oschina.net/wingyiu/blog/153357