At 14:31 -0800 2/26/02, John D. Kirkpatrick wrote:
>I have tried to create a MySQL table with the following lines:
>
>$query[] = "CREATE TABLE member (
>ID bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
>userName varchar(40) NOT NULL,
>.
>.
>.
>
>RecordCreationTime TIMESTAMP NOT NULL DEFAULT '0',
>LastLogin TIMESTAMP NOT NULL DEFAULT 'now()',
>RecordTouchTime TIMESTAMP NOT NULL DEFAULT 'now()',
>)";
>
>No matter what I set the defualt values to, I get the timestamp value in the
>first timestamp field and zeros in all after the first one.
According to the manual, default values must be constants, not functions
as you are attempting to use:
http://www.mysql.com/doc/C/R/CREATE_TABLE.html
Although actually, 'now()' is a literal string, not a function.
Also, the other behavior you're observing about which TIMESTAMP
columns are updated and when, is exactly as documented in the manual:
http://www.mysql.com/doc/D/A/DATETIME.html
>
>The above give the following result:
>RecordCreationTime: 20020224233339
>LastLogin: 00000000000000
>RecordTouchTime: 00000000000000
>
>I had to currently use a separate insert statement after the table creation
>and it works fine... but the default doesn't seem to work with timestamp...
>yet no matter what I do, I always get the current timestamp if I don't
>update the first timestamp field.
>
>$query[] = "CREATE TABLE member (
>ID bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
>userName varchar(40) NOT NULL,
>.
>.
>.
>
>RecordCreationTime TIMESTAMP,
>LastLogin TIMESTAMP,
>RecordTouchTime TIMESTAMP,
>)";
>
>This STILL update the first TIMESTAMP field as a defualt when updating
>another column... but only the first TIMESTAMP field.
>I've had to put a dummy field as TIMESTAMP as the first one... the rest are
>fine.
