While mySQL does have a lot going for it, the dearth of some basic functionalities continues to make me nuts – which is why I use other databases (notably Postgres) when I’m able.
When building Web sites, however, mySQL is a necessity, as it’s the only DB you can really get hosted on Linux sites.
But…augh!!! Today’s issue: Defaults and dates.
On just about all databases I’ve worked with, you can set defaults on date fields (even Access, fer christ’s sake!). For example, let’s use this Postgres table:
create table user_profile (
user_profile_id serial primary key,
first_name varchar(255),
last_name varchar(255),
email varchar(255),
user_name varchar(255),
user_password varchar(10),
date_added timestamp default now(),
date_modified timestamp default now(),
date_deleted timestamp default null);
Note how the date_added and date_modified fields default to now(): So, upon profile creation, each is automagically set to the time of creation. Date_deleted, of course, is nulled out – will be filled out when necessary.
But you can’t do this in mySQL – you can only default date fields if you use the timestamp data type. This has the following effects:
- Most (all?) tools – including the command line – display timestamp without formatting (20031230161304) and datestamp formatted (2003-12-30 16:13:04). Why two different views of essentially the same data?
- The first timestamp column will behave differently from second and subsequent timestamp columns in a given table. Timestamp is designed to essentially give a date modified time stamp – any insert or updates to row will increment the timestamp but only if it’s the first timestamp column. That’s wacky.
RE: The second point. Assume the following table (note the two timestamp columns):
mysql> describe test;
+——-+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————+——+—–+———+——-+
| d1 | datetime | YES | | NULL | |
| d2 | timestamp(14) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| d3 | timestamp(14) | YES | | NULL | |
+——-+—————+——+—–+———+——-+
If I insert into this table – say, add only name and d1 (datetime type; use now() function) values – here is what we will see:
+———————+—————-+————+—————-+
| d1 | d2 | name | d3 |
+———————+—————-+————+—————-+
| 2003-12-30 16:22:51 | 20031230162251 | Geistlinge | 00000000000000 |
+———————+—————-+————+—————-+
So, the first timestamp column defaults to current time; the second one just sits there. Ditto for updates. And god forbid if I just reorganize my columns – but keep the same names and types – suddenly, another column may begin timestamping instead of the other. Grrr…
And this second timestamp field defaults to 00000000000, not NULL.
Ungood.
Another example of why I bash mySQL.
Deep breath.