I agree, since it makes sense, but it _may_ make porting some MySQL apps harder.
Mark Atwood wrote: > +1 > > On Feb 16, 2009, at 4:10 PM, Jay Pipes wrote: > >> All, >> >> Has anyone ever been completely confused by MySQL's handling of >> TIMESTAMP columns? I know I have. >> >> For a primer, read here: >> >> http://dev.mysql.com/doc/refman/6.0/en/timestamp.html >> >> In an effort NOT to go down a rathole but to refactor the server's >> handling of TIMESTAMP columns to be less "magic" and more explicit, >> here is my proposed change in behaviour. Please vote! >> >> Currently, there is some "magic" in the way one can define TIMESTAMP >> columns in MySQL. The magic occurs for the *first* TIMESTAMP column >> that is defined without any DEFAULT or ON UPDATE clause, like so: >> >> CREATE TABLE t1 ( >> ts1 TIMESTAMP >> ); >> >> What the above does silently is convert that timestamp column's >> definition to this: >> >> CREATE TABLE t1 ( >> ts1 TIMESTAMP DEFAULT NOW() ON UPDATE NOW() >> ); >> >> The reason for this is for backwards compatibility with Unireg, >> MyISAM's predecessor. The above magic, implicit definition is >> implemented as a hack in the server (namely, in prepare_create_field() >> and in the parser itself. It's a hack that I would prefer to get rid >> of (and I know Stewart would as well, since this would allow us to get >> rid of the unireg_check argument for Field creation...) >> >> If you've ever run into the following behaviour, you've probably >> scratched your head at the error message. The reason is because many >> users try to execute something like the following: >> >> mysql> CREATE TABLE t1 ( >> -> some_time TIMESTAMP >> -> , update_time TIMESTAMP DEFAULT NOW() ON UPDATE NOW() >> -> ); >> ERROR 1293 (HY000): Incorrect table definition; there can be only one >> TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause >> >> Looking at the error, many users think "but I *did* specify only a >> single column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause!!" >> >> So, in concrete terms, here is my proposal to change the behaviour of >> TIMESTAMP columns: >> >> a) If a user specifies TIMESTAMP with no DEFAULT or ON UPDATE clause, >> the TIMESTAMP column will *always* be defined as TIMESTAMP NULL, with >> no magic happening behind the scenes. >> >> b) Users must always explicitly specify what they wish the TIMESTAMP >> column's behaviour on insert and update. This means: >> >> b1) To specify a TIMESTAMP column which sets itself to NOW() when a >> record is inserted, but does NOT set itself to some value on update >> automatically, do: >> >> CREATE TABLE t1 ( >> ts TIMESTAMP DEFAULT NOW() >> ); >> >> b2) To specify a TIMESTAMP column which sets itself to NOW() when a >> record is updated, but does NOT set itself to some value on insert, do: >> >> CREATE TABLE t1 ( >> ts TIMESTAMP NULL ON UPDATE NOW() >> ); >> >> b3) To specify a TIMESTAMP column which sets itself to NOW() when a >> record is inserted, and ALSO sets itself to NOW() on update >> automatically, do: >> >> CREATE TABLE t1 ( >> ts TIMESTAMP DEFAULT NOW() ON UPDATE NOW() >> ); >> >> b4) The final variation on this would be: >> >> CREATE TABLE t1 ( >> ts TIMESTAMP NOT NULL ON UPDATE NOW() >> ); >> >> This would be for timestamp columns that, as a user, you wish to >> manually set to a value on INSERT (and *be forced to set this value*) >> and on update, have the column set itself to NOW() >> >> As far as I can tell, this proposal would be inline with 99% of >> MySQL's timestamp behaviour with the sole exception of no magic on the >> first TIMESTAMP column with no DEFAULT and ON UPDATE clause. >> >> Votes please! >> >> Thanks, >> >> Jay >> >> p.s. Stewart: if this proposal goes through, we can get rid of >> unireg_check and the associated stuff in unireg.cc:pack_screens() and >> pack_header() this week... >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~drizzle-discuss >> Post to : [email protected] >> Unsubscribe : https://launchpad.net/~drizzle-discuss >> More help : https://help.launchpad.net/ListHelp > _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
