How to prevent two NOW() columns updated at the same time when update a NOW() for one column-CodePudding

I have a table needs two different current times. First, I have an insert procedure for insert actionnumber, msgSentFrom_F_ID, msgSentTo_M_ID, and sentDate. Second, an update procedure for updating respondDate. my problem is, when I updated respondDate, the sentDate would be updated to the same time when I updated respondDate. Where did I do wrong? (my purpose is i want the sentDate to be the current time when i insert, and another current time when i update respondDate.)

CREATE TABLE IF NOT EXISTS actions (
  actionnumber INT AUTO_INCREMENT PRIMARY KEY,
  msgSentFrom_F_ID INT, 
  msgSentTo_M_ID INT,
  sentDate TIMESTAMP,
  respondDate TIMESTAMP NULL,
  FOREIGN KEY (msgSentFrom_F_ID) 
    REFERENCES femaleUsers(femaleuserId)
  FOREIGN KEY (msgSentTo_M_ID) 
    REFERENCES maleUsers(maleuserId)
);

DELIMITER //
create procedure (param_F_ID INT,param_M_ID INT,Sdate TIMESTAMP)
  BEGIN
  INSERT INTO actions (msgSentFrom_F_ID, msgSentTo_M_ID, sentDate) 
    VALUES (param_F_ID,param_M_ID,Now());
  END; //
DELIMITER ;

CALL insert_actions ('5','5',NOW());

DELIMITER //
create procedure update_respondDate (param_ActionNum INT, 
    param_respondDate TIMESTAMP)
  BEGIN
  UPDATE actions set respondDate = param_respondDate 
  WHERE  actionnumber = param_ActionNum;
  END; //
DELIMITER ;

CALL update_respondDate('6',NOW());

CodePudding user response：

It sounds like you have the system variable explicit_defaults_for_timestamp disabled. The documentation explains the result of this:

If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly. To suppress automatic properties for the first TIMESTAMP column, use one of these strategies:

Enable the explicit_defaults_for_timestamp system variable. In this case, the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses that specify automatic initialization and updating are available, but are not assigned to any TIMESTAMP column unless explicitly included in the column definition.

Alternatively, if explicit_defaults_for_timestamp is disabled, do either of the following:

Define the column with a DEFAULT clause that specifies a constant default value.

Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL. Assigning NULL sets the column to NULL, not the current timestamp. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().

Since sentDate is the first TIMESTAMP column in the table, it's automatically set to the current time whenever you make any change to the row.