Blog This!   Lee Geistlinger's Web Log

Blog Home
Blog Archives
LittleGhost Home

E-mail: geistlinger AT gmail.com

Everyone loves lists Books, Movies and so on

My Online Aggregator

More mySQL Gripes

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.

<rant>

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:

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):

If I insert into this table - say, add only name and d1 (datetime type; use now() function) values - here is what we will see:

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.

</rant>