In the following examples, it is assumed that the sample data tables include the fields created_at, updated_at, and deleted_at, and that the field type is datetime.

Example SQL

This is the MySQL table structure used in the subsequent example code.

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `status` tinyint DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `user_detail` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `address` varchar(45) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

tip

• If you choose to use the time field type, you need to set the field to allow NULL, so that soft delete can take effect.
• If you try to test and view the SQL statements executed by ORM operations, it is recommended to enable debug mode (related document: ORM Senior - Debug Mode), and SQL statements will be automatically printed to the log output.

created_at Writing Time

This time is automatically written when executing the Insert/InsertIgnore/BatchInsert/BatchInsertIgnore methods, and subsequent update/delete operations will not change the content of the created_at field.

warning

It should be noted that the Replace method will also update this field, as this operation is equivalent to deleting the existing old data and writing a new record.

// INSERT INTO `user`(`name`,`created_at`,`updated_at`) VALUES('john', `2020-06-06 21:00:00`, `2020-06-06 21:00:00`)
g.Model("user").Data(g.Map{"name": "john"}).Insert()

// INSERT IGNORE INTO `user`(`id`,`name`,`created_at`,`updated_at`) VALUES(10000,'john', `2020-06-06 21:00:00`, `2020-06-06 21:00:00`)
g.Model("user").Data(g.Map{"id": 10000, "name": "john"}).InsertIgnore()

// REPLACE INTO `user`(`id`,`name`,`created_at`,`updated_at`) VALUES(10000,'john', `2020-06-06 21:00:00`, `2020-06-06 21:00:00`)
g.Model("user").Data(g.Map{"id": 10000, "name": "john"}).Replace()

// INSERT INTO `user`(`id`,`name`,`created_at`,`updated_at`) VALUES(10001,'john', `2020-06-06 21:00:00`, `2020-06-06 21:00:00`) ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`updated_at`=VALUES(`updated_at`)
g.Model("user").Data(g.Map{"id": 10001, "name": "john"}).Save()

deleted_at Data Soft Delete

When soft delete exists (i.e., when the deleted_at field exists), all query statements will automatically add the deleted_at condition.

// UPDATE `user` SET `deleted_at`='2020-06-06 21:00:00' WHERE id=10 AND `deleted_at` IS NULL
g.Model("user").Where("id", 10).Delete()

Some changes occur during the query, for example:

// SELECT * FROM `user` WHERE id>1 AND `deleted_at` IS NULL
g.Model("user").Where("id>?", 1).All()

As you can see, when the deleted_at field exists in the data table, all query operations involving that table will automatically add the condition deleted_at IS NULL.

updated_at Updating Time

This time is automatically written when executing the Save/Update methods.

warning

It should be noted that the Replace method will also update this field, as this operation is equivalent to deleting the existing old data and writing a new record.

// UPDATE `user` SET `name`='john guo',`updated_at`='2020-06-06 21:00:00' WHERE name='john' AND `deleted_at` IS NULL
g.Model("user").Data(g.Map{"name" : "john guo"}).Where("name", "john").Update()

// UPDATE `user` SET `status`=1,`updated_at`='2020-06-06 21:00:00' WHERE `deleted_at` IS NULL ORDER BY `id` ASC LIMIT 10
g.Model("user").Data("status", 1).OrderAsc("id").Limit(10).Update()

// INSERT INTO `user`(`id`,`name`,`update_at`) VALUES(1,'john guo','2020-12-29 20:16:14') ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`update_at`=VALUES(`update_at`)
g.Model("user").Data(g.Map{"id": 1, "name": "john guo"}).Save()

Scenarios of Join Query

If several tables involved in the join query have enabled the soft delete feature, the following situation will occur, that is, the conditional statement will include the soft delete time checks of all related tables.

// SELECT * FROM `user` AS `u` LEFT JOIN `user_detail` AS `ud` ON (ud.id=u.id) WHERE (`u`.`id`=10) AND `u`.`deleted_at` IS NULL LIMIT 1
g.Model("user", "u").LeftJoin("user_detail", "ud", "ud.id=u.id").Where("u.id", 10).One()

Unscoped Ignoring Time Features

Unscoped is used to ignore automatic time update features in chained operations. For example, in the above example, after adding the Unscoped method:

// SELECT * FROM `user` WHERE id>1
g.Model("user").Unscoped().Where("id>?", 1).All()

// SELECT * FROM `user` AS `u` LEFT JOIN `user_detail` AS `ud` ON (ud.id=u.id) WHERE u.id=10 LIMIT 1
g.Model("user", "u").LeftJoin("user_detail", "ud", "ud.id=u.id").Where("u.id", 10).Unscoped().One()