* Arnold Daniels <info@stripped> [07/07/29 10:45]:
> Why can there be only one timestamp field with current_timestamp in
> default or on update?
> I've been wondered about this for a long time. A table often has a field
> `date_added`, with the current timestamp only as default, and a field
> `date_changed` with a changing timestamp upon updating. Basically it is
> irritating me, since now I have to either solve this in code or use a
> trigger. Also because I do not understand why this limitation is necessary.
AFAIU this limitation stems only from the way in which this feature is
currently implemented in the server and there are no other reasons for
its existence. SQL standard does not mention such limitation. Moreover
in addition to CURRENT_TIMESTAMP SQL standard allows several other
niladic functions as default values (e.g. CURRENT_USER).
> Is this something that is subjected to change in MySQL 6? I haven't seen
> any plans for it.
AFAIK this item is on our TODO list but not very high. So it is not
planned for any particular version. Actually rather than implementing
support for multiply TIMESTAMP fields with DEFAULT CURRENT_TIMESTAMP
values it makes sense to implement support for more general expressions
in DEFAULT clause. And the latter feature strongly depends on switch
to new text-based .FRM format (for which there are no fixed plans as
well). Having said that I should mention that, of course, we will
consider a community contributed patch solving only the former problem.
I also should mention that if someone from community wants to work on
removing this limitation he should be aware that one of prerequisites
for such a change is moving logic responsible for auto-magical setting
of TIMESTAMP values from handlers (e.g. ha_myisam.cc) to SQL-layer.
Dmitri Lenev, Software Developer
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification