This will no doubt be old hat to mysql and postgres users, but I came from sql server..

I was working on a site called visualchat.co.uk which allows users to chat to one another – and, as you might expect we get our fair share of nob ends.. As such we give our users the ability to block one another.

I recently made another version of this site and moved the entire stack from asp.net, sql server flashcom, to mysql, ruby on rails and red5, I was pleasantly surprised when it came to implementing the blocks in mysql.

I store the blocks in a table with the following field:

id, user_id, to_user_id, block_type

All of these are integers.

I found with mysql I can create an index on user_id and to_user_id
  1. CREATE UNIQUE INDEX user_block ON blocks(user_id, to_user_id)

Now users can change their blocks to block, or unblock individuals, and also use that integer setting to indicate if they want to turn a clean chat filter on or off, as these might change several times in a users history, I can’t (in sql server anyway) do a straight update, I need to do a select, and then update or insert depending if the value is present.

I optimised that in sql server using temp tables, but I always was unhappy with the way it was implemented. When I moved to mysql I found that it has this great feature called ON DUPLICATE KEY UPDATE.

It allows you to insert into a table, and when an insert would cause a duplicate key, update the existing row. Now this is just what I wanted – I could now do code like:

INSERT INTO blocks (user_id, to_user_id, block_type) VALUES (1,2,6) ON DUPLICATE KEY UPDATE block_type=7;

This has made my life so much easier, I can now get my chat server to dump all of the changed block settings into a given session into a temp table and use this niffty command to persist the blocks!

I’m sure I’ll be using this again in the future too – super handy!