4

I know there is TIMESTAMP data type that automatically updates to timestamp value when a record is updated, and I already have such column.

Besides that I'd like to have a column that automatically populates to NOW() (or CURRENT_TIMESTAMP) and never changes, but MySQL DEFAULT doesn't appear to support function calls.

Please post only pure MySQL answers. I know how to do it at application level.

EDIT: If there's no such feature - I'd appreciate to hear that.

EDIT2: MySQL version is 5.0.32

tishma
  • 1,855
  • 1
  • 21
  • 43
  • OK. My bad. I forgot to mention that I'd like to use REPLACE INTO, and that's why I want that to happen 'automatically' - without being explicit about that. – tishma Dec 11 '10 at 16:38
  • you can, **BUT** only one `default current_timestamp` allowed – ajreal Dec 11 '10 at 17:02

2 Answers2

9

Use a trigger to set the default.

DELIMITER |

CREATE
    TRIGGER trigger_name BEFORE INSERT ON tbl_name FOR EACH ROW 
BEGIN
    SET NEW.colname = NOW();
END;

|

Try this one, including the delimiter.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • Thanks for the edit. I'll accept it. Btw, PHPMyAdmin will have problem with this statement, because of ";" that's used as a statement delimiter. – tishma Dec 11 '10 at 16:45
3

I'd like to have a column that automatically populates to NOW() (or CURRENT_TIMESTAMP) and never changes

By saying you'd like it to populate to NOW() it sounds like you're referring to the initial INSERT. If that's the case, can you just use that? Something like

INSERT INTO table (field1,field2,my_datetime) VALUES (1,'a',NOW())
Riedsio
  • 9,758
  • 1
  • 24
  • 33
  • Exactly. Only this is not quite automatic... I'd like to never mention that column in INSERT statements. – tishma Dec 11 '10 at 16:34
  • Now way I know of then -- I'd lean towards the trigger. – Riedsio Dec 11 '10 at 16:37
  • I guess I'll accept trigger. However, I'm stuck with this MySQL version, and creating a trigger requires SUPER privilege, so it doesn't really work for me :( – tishma Dec 11 '10 at 16:42
  • You need TRIGGER privileges: http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_trigger – Frank Heikens Dec 11 '10 at 16:53