I'm moving a PgSql database over to MySql and am having problems with a DATETIME column. Is it not possible in MySql for the column default to be a function instead of a constant? I need the default to be now(), for obvious reasons. TFM doesn't seem to speak to this, not in any way I can manage to find. TIA!!!
Not sure if it can be done at table level, but you can use MySQL functions at the point of insertion.
Eg:
INSERT INTO foo (foo_date, foo_value) VALUES (NOW(), LOG(2))
HTH...
Clearly I can do that; but the design of the database contains two date/time column: one to capture the time of insertion, and one for the last update. It will mean far fewer changes to the code if I can have the DB itself do it. In the PostgreSQL version, it does: the creation-time column has a default function that provides the current timestamp, and there's an update trigger that provides the last-update timestamp. Since the only purpose of the porting is to allow the product to be demo'd on MySQL, I'm trying to keep the number of source changes to a minimum.
If you make the column a TIMESTAMP, mysql automatically updates it with the current time, but only the first TIMESTAMP column.
It's going to require some code changes to make it work, since mysql doesn't have default functions or triggers.
---JH
It's going to require some code changes to make it work, since mysql doesn't have default functions
That's the first definitive answer I've seen. Ok, thanks, and I'll try not to shoot the bearer of bad news! :-)
