MySQL Update Statement Causing Side Effects

I have a MySQL table in the format shown below:

mysql> select event_time, count, result, pcm_id from Events_CallMeBack_2011_08_05 WHERE pcm_id=1566;
+---------------------+-------+--------+--------+
| event_time          | count | result | pcm_id |
+---------------------+-------+--------+--------+
| 2011-08-05 23:45:04 |     0 | NULL   |   1566 |
+---------------------+-------+--------+--------+
1 row in set (0.00 sec)

The problem is that when I run an UPDAT开发者_运维技巧E query modifying the result column, the event_time is also being updated as a side effect. I have no clue why this is happening: I certainly don't want the event_time field to be updated when I try to update the result column.

Here's the query:

UPDATE Events_CallMeBack_2011_08_05 SET result ='D' WHERE pcm_id = '1566'

Here's the resulting column after running the UPDATE statement.

mysql> select event_time, count, result, pcm_id from Events_CallMeBack_2011_08_05 WHERE pcm_id=1566;
+---------------------+-------+--------+--------+
| event_time          | count | result | pcm_id |
+---------------------+-------+--------+--------+
| 2011-08-20 19:40:21 |     0 | D      |   1566 |
+---------------------+-------+--------+--------+
1 row in set (0.00 sec)

The event_time column was update to the current local time of the mysql server.

My initial analysis is that mysql is automatically updating event_time (TIMESTAMP) column on Update statements. The CREATE TABLE statement doesn't specify this so I'm clueless why this is happening.

Create Statement:

strSql = "CREATE TABLE " + strTableName +
"(id INT NOT NULL, event_time TIMESTAMP,....

Table is described below.

mysql> describe Events_CallMeBack_2011_08_05;


+------------+-------------+------+-----+-------------------+-----------------------------+
| Field      | Type        | Null | Key | Default           | Extra                       |
   +------------+-------------+------+-----+-------------------+-----------------------------+
| id         | int(11)     | NO   |     | NULL                  |                             |
| event_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| event_code | varchar(30) | YES  |     | NULL                         |                             |
| count      | int(11)     | YES  |     | 0                 |                             |
| result     | varchar(1)  | YES  |     | NULL              |                             |
| pcm_id     | int(11)     | NO   | PRI | NULL              | auto_increment              |
 +------------+-------------+------+-----+-------------------+-----------------------------+

The event_time column is probably declared with the type TIMESTAMP. Columns of this type are set to the current time when the row is inserted or updated.

Consider using DATETIME instead.

Yes, that is the documented behavior of the TIMESTAMP data type. It updates to the current time every time you update a row.

With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

If you define your TIMESTAMP column with a constant default value (e.g. 0), you can override this behavior. Read the manual page for more details.