Love SQL

Something I’m working on at the moment requires a rather large collection of data to be moved from several places into one clever little table that I’ve created. It’s going to consolidate functionality and make things much easier to script in a few different environments.

Gathering all of this data together has been made pretty damn easy by using a few choice SQL queries. A few months ago I would probably have written a Perl script to do this instead, but that’s really not necessary.

This first query performs a SELECT on one table and uses the values to populate the INSERT statement values. It’s quite simple because all of the required data is in one table on the same database as the table it’s being copied to. All field, table, and database names have been changed to protect the innocent.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, email_address_1, email_type FROM prefs WHERE email_address_1 IS NOT NULL;

The second query is a bit more complicated. It performs a SELECT on a table in one database that does an INNER JOIN on a table in another database and then an INSERT to get all of the collated data into a different table in the first database.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, people.email, email_type FROM prefs INNER JOIN other_db.people ON prefs.person_id=people.id WHERE prefs.password_reminder=1;

I was quite pleased to find this worked first time when I tested. It doesn’t seem complicated to me now, but I know it would have done before I started working where I do now.

MySQL default values should be better

I was creating a table a little while ago that stores a timestamp that is essentially an expiry date. I had wanted this to be a date 2 weeks in advance of the current date and time. With a timestamp field, however, the only non-constant default value available is CURRENT_TIMESTAMP. This, obviously, represents the moment in time that the row is created. I wanted to be able to do something more like the following:

expiry_date TIMESTAMP NOT NULL DEFAULT DATE_ADD(NOW(), INTERVAL 14 DAY)

Unfortunately that’s not allowed at all. Perhaps at some point in the future it will be though, and I look forward to that day.

If you’re doing this yourself and your expiry date is absolutely always going to be the same amount of time into the future then you can use something like the following:

start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

You could then use something like the following query to fetch all rows where the expiry date has not been reached:

SELECT * FROM table_name WHERE DATE_ADD(start_date, INTERVAL 14 DAY) < NOW();

Oh well, just another little MySQL thing I found a bit interesting while I was poking around. 🙂

MySQL case insensitivity on varchar fields

I created a MySQL table like the following:

CREATE TABLE `links` (
`id` int(11) NOT NULL auto_increment,
`short_id` varchar(255) NOT NULL,
`url` text NOT NULL,
`user_id` int(11) default NULL,
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT unique_key_1 UNIQUE (`short_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This structure caused an error for me when it came to inserting an upper-case ‘A’ in addition to the lower-case ‘a’ into the short_id field. I removed the unique constraint for the short_id field and this allowed me to create a row with ‘A’ as the short_id.

Unfortunately

SELECT * FROM links WHERE short_id='A';

gave me both rows (‘a’ and ‘A’), with the ‘a’ row being first. To fix this I had to set the short_id field to be BINARY. When I’d done that I realised I could make the short_id field unique again.

To create the table properly I now use the following query:

CREATE TABLE IF NOT EXISTS `links` (
`id` int(11) NOT NULL auto_increment,
`short_id` varchar(255) character set utf8 collate utf8_bin NOT NULL,
`url` text NOT NULL,
`user_id` int(11) default NULL,
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
CONSTRAINT unique_key_1 UNIQUE (`short_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You can just add the BINARY keyword after the field data type if you like, e.g.:

`short_id` varchar(255) BINARY NOT NULL

Hope this helps someone else out there. 🙂