Update: This stuff has its own MySQL page now where we continue 🙂

As might have been inferred from earlier posts, I’m not exactly a star when it comes to MySQL, therefore I’ve learned some neat stuff during the past months, working with legacy code.

I suppose the main take away here is that all this is in the manual, it could pay off greatly to actually read up on all those obscure built-in functions. The pay off is then to not have to do follow up in the actual logic, whether it be PHP or something else, faster and less hassle.

The first one is not really that clever, it’s merely an observation that has been reached through trial and error:

ALTER DATABASE `database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Without setting the collation of the whole database to utf8_general_ci non latin languages might get screwed, even though the table and field were set correctly. Don’t ask me why, probably got something to do with the encoding in the connection itself, ie the data is garbled even before it’s inserted.

SELECT UNIX_TIMESTAMP(DATE(`timestamp`)) as time, 
COALESCE(COUNT(DATE(`timestamp`)), 0) as clicks 
FROM `$table` 
WHERE `ad_id`={$sql->escape($ad_id)} AND 
DATE(`timestamp`) > DATE_SUB(NOW(), INTERVAL 3 MONTH)
GROUP BY DATE(`timestamp`)

Note COALESCE in this example (apart from the date stuff which is subtracting 3 months from the current date), it will return the first non null value, which will be 0 here if COUNT(DATE(`timestamp`)) doesn’t return anything meaningful.

The stuff with $ in front of it are PHP variables, in case you didn’t already know, they will of course expand into their contents, so will the heredoc syntax {$var}.

INSERT INTO `table` (`value`, `alias`) VALUES ('value', 'alias') ON DUPLICATE KEY UPDATE value = 'value'

I think this is a really neat one, the old update if already there insert otherwise, in plain SQL. Another variety is this one:

REPLACE table SET `page_id`= 5, `alias`= 'alias', `caching_time`=NOW()

Note that you already need to know the id you want to use in this case, in most cases the earlier example will be preferable. Sometimes you have a combination of for instance a non-unique user id and non-unique date that together is unique, then you can do:

ALTER TABLE `table` ADD UNIQUE `custId` ( `custId` , `date` )

Then you can use REPLACE like this:

REPLACE INTO table SET `cash`= 5000, `username`= 'balleapanson', `caching_time`=NOW(), custId = 65232, date = '2009-06-03'

The above is basically:

UPDATE table SET `cash`= 5000, `username`= 'balleapanson', `caching_time`=NOW() WHERE custId = 65232 AND date = '2009-06-03'

If the entry already exists, otherwise we insert, pretty neat. Note though that REPLACE will remove the prior entry prior to inserting so if there is an autoincrement ID in the picture it will continue counting upwards even though no really new entries are added.

Let’s take a look at sub queries as temporary tables:

SELECT COUNT( * )
FROM (
	SELECT userid
	FROM jos_session
	WHERE guest =0
	GROUP BY userid
) AS sess

The above query will count all people whose user id’s are in the session table and group them, so we might be getting a result back looking like this from the sub query:
userid
1
4
5
What we want to retrieve now is the count of that result, which is 3, and that is made through the main SELECT COUNT( * ) query. Note that we need to alias the sub query / temporary table with AS sess even though this particular query is simple enough that we won’t have any further need for the sess alias.

Related Posts

• One Master, Two Slaves – Daisy Chaining MySQL
• MySQL 5.5 Replication Bash Script
• MySQL Slave Replication in 6 Quick Steps
• Moving the MySQL database directory to different drive on Ubuntu
• MySQL replication in PHP – on the same machine