Make TIMESTAMP column update even though I didn't change anything?

Question

I have a MySql table which has a timestamp column.

The table also has alot of other columns.

I have a "change classified" section on my page.

When users change their classifieds, I want the timestamp to update its timestamp. And it does, but sometimes you don't change a classified at all, you just want it to be "renewed".

How can I make my TimeStamp column update even though nothing has been updated?

Ie:

  UPDATE table
  SET text='hello'
  WHERE table.id='id_here';

In the above example, if the column "text" was already hello, nothing would happen here. The Timestamp wouldn't update.

Any ways around this except adding another column? Any MySql settings?

Thanks

Answer 1

This isn't really an answer to your question, but I think timestamp columns are generally a bad idea. The reason they are a bad idea is things like this. You are letting MySQL do stuff automatically, and if its idea of what it should do automatically isn't exactly your idea of what you want it to do, then you're stuck.

I would add an extra column, type datetime (depending on the accuracy you need) and always explicitly update it.

Answer 2

Why don't you just update the timestamp column with timestamp?

  UPDATE table
  SET timestamp = now()
  WHERE table.id='id_here';

Or maybe I am missing part of your problem?

Answer 3

Timestamp columns are an excellent idea, all enterprise DBMSs have them; all operate the same automatic way. The "stuff" it does is standard and clearly documented technical operations. It is not updateable directly, just to prevent people doing silly things like this and damaging the true value of what it is meant to be; the vendor has correctly protected your database from abuse and subversion.

Misusing the Timestamp column, is a bad idea.

Trying to get it to do things it was never designed to do, is a bad idea.

The UPDATE ... SET "hello" to "hello" is a gross misuse, and unnecessary work on the server.

You are trying to get the Timestamp column to identify something other than Timestamp. Bad idea. That (a fact) should be represented in data (a column). Do it properly. Add a DATE column to the table: RenewedDate.