MySQL 自动更新日期字段

Question

我正在开发一个 swing 应用程序，在测试期间我遇到了一些更新数据的问题。

我有一个名为“剂量”的表格:

create table Dose (id_dose int auto_increment primary key ,
        id_cmd varchar(50),
        t_inj TimeStamp,
        a_inj int,
        id_e_d int,
        id_dose_inc varchar(50),
        poid int ,
        Constraint fkk_et_doses foreign key (id_e_d) references Etat_dose (id_e_d),
        Constraint fkk_et_cmds foreign key (id_cmd) references Commande (id_cmd)ON DELETE CASCADE);

id_e_d 字段引用 etat_dose 表中的 id_e_d :

create table Etat_dose (id_e_d int primary key,
                    libele varchar(50));

当我将数据插入其中时，一切正常，为此我使用查询:

Methodes.UpdateData("insert into Dose(id_cmd,t_inj,a_inj,id_e_d,id_dose_inc,poid)  values (?,?,?,?,?,?)", dose, 6);

但是当我更新 id_e_d 时，剂量表中的 T_inj 会自动更改为系统日期。例如:

在我插入之前:

insert into doses values(1,'CMFDG121031-1',' 2012-10-02 10:30:55',400,1,'CMFDG121031-1-1',30)

当我编辑它时，我得到:

mysql> select * from dose where id_dose=1;
    +---------+---------------+---------------------+-------+--------+-----------------+------+
    | id_dose | id_cmd        | t_inj               | a_inj | id_e_d | id_dose_inc      | poid 
    +---------+---------------+---------------------+-------+--------+-----------------+------+
    |       1 | CMFDG121031-1 | 2012-10-02 10:30:55 |   400 |      1 | CMFDG121031-1-1 |   30 |
    +---------+---------------+---------------------+-------+--------+-------------------------

在我执行的更新中:

update dose set id_e_d=2 where id_dose=1

我得到:

   mysql> select * from dose where id_dose=1;
    +---------+---------------+---------------------+-------+--------+-----------------+------+
    | id_dose | id_cmd        | t_inj               | a_inj | id_e_d | id_dose_inc      | poid 
    +---------+---------------+---------------------+-------+--------+-----------------+------+
    |       1 | CMFDG121031-1 | 2012-10-08 16:15:11 |   400 |      2 | CMFDG121031-1-1 |   30 |
    +---------+---------------+---------------------+-------+--------+-------------------------

如您所见，T_inj 的值已自动更改。我可以做些什么来追踪这个错误。

Answer 1

根据 Automatic Initialization and Updating for TIMESTAMP 记录:

The TIMESTAMP data type offers automatic initialization and updating to the current date and time (that is, the current timestamp). You can choose whether to use these properties and which column should have them:

[ deletia ]

• If the column is auto-updated, it is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. The column remains unchanged if all other columns are set to their current values. To prevent the column from updating when other columns change, explicitly set it to its current value. To update the column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

[ deletia ]

The following rules describe the possibilities for defining the first TIMESTAMP column in a table with the current timestamp for both the default and auto-update values, for one but not the other, or for neither:

[ deletia ]

• With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value and is not automatically updated to the current timestamp.

  The default depends on whether the DEFAULT clause specifies CURRENT_TIMESTAMP or a constant value. With CURRENT_TIMESTAMP, the default is the current timestamp.

  CREATE TABLE t1 (  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

  With a constant, the default is the given value. In this case, the column has no automatic properties at all.

  CREATE TABLE t1 (  ts TIMESTAMP DEFAULT 0);