Roy Lyseng wrote:
+1 - except that DEFAULT is not the same as an ON INSERT clause/trigger.
A DEFAULT value would be overridden when a user-supplied value is
specified. An ON INSERT value overrides anything specified by the user.
Indeed. Phase 3.75. :)
Baby steps. :)
-jay
p.s. After digging into all this stuff, I actually think a simple hook
system for on update and on insert would be trivial once the frm work is
completed. And it wouldn't be limited to just NOW(). Any niladic
function could be used, which would bring us in line with the SQL
standard AFAIK.
Thanks,
Roy
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
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
