MYSQL: How can I create a MySQL table so, that a field's default value is the output of a function.

Question

QUESTION How can I create a MySQL table so, that a field's default value is the output of a function.

Can it be done without an insert trigger?

BACKGROUND: Some entities and the groups they belong to need to be observed on a daily basis.

To do that I created the following MySQL table:

CREATE TABLE entity (
    entity_id  VARCHAR(10) NOT NULL,
    group_id   VARCHAR(10) NOT NULL,
    first_seen DATE        NOT NULL,
    last_seen  DATE        NOT NULL,
    PRIMARY KEY (entity_id,group_id)
)

A script parses the entity logs on a daily basis and writes to to entity_raw, I then want to update the tables using

REPLACE INTO entity (entity_id,group_id,last_seen) 
SELECT entity_id,group_id,record_date 
  FROM entity_raw 
 WHERE (record_date = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY))

Naturally I get an error when inserting new (entity_id,group_id) pairs, since first_seen may not be NULL...

Can this be done in one statement, short of using a trigger (I dislike them, too surprising for maintenance)

or do I need to select for existance first, then insert/update accordingly?

Answer 1

To be honest it's pretty much impossible to combine an update or insert into one statement.

If you are using a script, I would do as you suggested. First check for existence, then update or insert accordingly.

Otherwise a trigger or stored procedure would do the job.

Answer 2

No, what you're asking isn't possible without triggers. The only example in MySQL where a field can be assigned a default value from a function is the TIMESTAMP type that can have CURRENT_TIMESTAMP as default.

Answer 3

You could use the TIMESTAMP data type, which allows a default value of CURRENT_TIMESTAMP, but that would set it to NOW() and not to a day ago.

So, I would rather use use an INSERT... ON DUPLICATE KEY UPDATE statement:

INSERT INTO entity (entity_id,group_id,first_seen,last_seen) 
SELECT entity_id,group_id,record_date, record_date 
    FROM entity_raw 
    WHERE (record_date = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY))
ON DUPLICATE KEY UPDATE last_seen = VALUES(last_seen)

with a unique index en entity_id and/or group_id. This will overwrite only last_seen when an existing row is found.

Answer 4

After some searching I came up with this .. it solves my "problem", but not the question I asked :-(

REPLACE INTO entity (entity_id,group_id,first_seen,last_seen) 
SELECT r.entity_id              AS entity_id
     , r.group_id               AS group_id
     , ISNULL( e.first_seen
              ,r.record_date
             )                  AS first_seen
     , r.record_date            AS last_seen
  FROM entity_raw r
  LEFT OUTER JOIN entity e ON (    r.entity_id=e.entity_id 
                               AND r.group_id=e.group_id
                              )
 WHERE (record_date = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY))

The initial insert needs to be done with the where clause disabled (there were already 5weeks of data in the entity_raw table)