How to correctly realize the design of data table insertion time and update time? - Codes Helper - Programming Question Answer

How to correctly realize the design of data table insertion time and update time?

when using eloquent, you can see from the official document that you can customize the insert field and update the field name, with the link https://laravel-china.org/doc.
. Next, give the design of the data table:

CREATE TABLE `plans`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` mediumtext,
  `status` tinyint(1) NOT NULL DEFAULT 0,
  `create_time` TIMESTAMP,
  `update_time` TIMESTAMP,
  PRIMARY KEY(id),
  FOREIGN KEY(user_id) REFERENCES users(id)
)ENGINE=INNODB;

because I am using the mysql5.6 version, I support setting two timestamp types, but after this setting, I understand that the two fields are created at the same time, but after I update the data, the two fields are still at the same time after the update, which is particularly difficult for me to understand. Then I tried it for a period of time and abandoned this method, so I used the native statement to update the update_time to the current time at the same time. However, I found that create_time has also been updated, or the two time periods can be the same. I would like to ask if there is a good way without changing the field type of the data table, because all my small projects are almost finished, I found this problem and want to find a good solution

.

Eloquent mysql php

Oct.09,2021

you can try to create a separate model

namespace App\Models;

    use Illuminate\Database\Eloquent\Model;

    class Article extends Model
    {
        public $timestamps = false;
        
        public $table = 'article';
    }

and disable automatic time updates, and manually update time fields

create_time CURRENT_TIMESTAMPS remove, update_time add, manually assign creation time when creating data

your current logic, each operation will operate on both create_time and update_time fields. These two fields need to be defined as optional, and then the data is passed from the client and should not be calculated at the database level. In theory, it is not a big problem in database calculation, but in practice, if the user's time is stuck accurately, then the user's save time is 9 / 59 / 59 / 59, but the database trigger may be saved at 10:00, and the data is wrong at this time. So all your data should be obtained from the client, whether create_time or update_time, is the time when the user clicks the save button, not the time when the data triggers the save action in the database. If you consider other factors such as the Internet, your data may be more biased.

because when creating the table, create_time timestamp, defaults to timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, which can be confirmed by show create table plans. In this case, if this row of data is updated, the create_time field will be automatically updated to now ().
the solution can be to display the specified create_time timestamp default current_timestamp, when creating the table so that the ON UPDATE CURRENT_TIMESTAMP will not be added by default, that is, the create_time field will not be updated automatically when the table is updated by default.

Previous: Nginx+uwsgi+django death report 502?

Next: Elements that extract the same attribute values from an array are rearranged into a new array

Menu