create_time字段要加索引吗 create_time和update_time

文章目录

• 1. 建表时时间字段使用 datetime

• 1. 实体类与对应Dao接口
• 2. insert数据实体类不设置createTime和updateTime属性值，SQL中也不指定该字段
• 3. update数据时实体类不设置createTime和updateTime属性值，SQL中也不指定该字段
• 4. insert数据时实体类设置createTime和updateTime属性值，但SQL中不指定该字段
• 5. update数据时实体类设置createTime和updateTime属性值，但SQL中不指定该字段
• 6. insert数据时实体类设置createTime和updateTime属性值，SQL中也指定该字段
• 7. update数据时实体类设置createTime和updateTime属性值，SQL中也指定该字段
• 8. insert数据时实体类未设置createTime和updateTime属性值，SQL中指定了该字段
• 9. 总结

• 2. 建表时时间字段使用timestamp

• 1. 实体类与对应Dao接口
• 2. insert数据实体类不设置createTime和updateTime属性值，SQL中也不指定该字段
• 3. insert数据时实体类设置createTime和updateTime属性值，但SQL中不指定该字段
• 4. insert数据时实体类设置createTime和updateTime属性值，SQL中也指定该字段
• 5. insert数据时实体类未设置createTime和updateTime属性值，SQL中指定了该字段
• 6. 总结

当我们创建业务表的时候 通常都需要设置createTime 和 updateTime，通常需要在代码中设置好时间后再插入数据库，如果想在代码中无需手动给这两个参数赋值，可以在设计表的时间字段上设置下面的两个属性：

• CURRENT_TIMESTAMP 字段设置后，当insert数据时，mysql会自动设置当前系统时间赋值给该属性字段。
• ON UPDATE CURRENT_TIMESTAMP 字段设置后，当update数据并且成功发生更改时，mysql会自动设置当前系统时间赋值给该属性字段。
• create_time 设置 CURRENT_TIMESTAMP属性。
• update_time 设置 ON UPDATE CURRENT_TIMESTAMP属性。

1. 建表时时间字段使用 datetime

datetime 类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以’YYYY-MM-DD HH:MM:SS’格式显示datetime 值。

CREATE TABLE `t_announce_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sourcePlatformOrderId` int(11) NOT NULL COMMENT '来源平台工单id',
  `name` varchar(100) NOT NULL COMMENT '工单名称',
  `sourcePlatform` varchar(100) NOT NULL COMMENT '来源平台',
  `dealStatus` tinyint(4) NOT NULL COMMENT '处置状态(0:待处置,1:已处置)',
  `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` datetime NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sourcePlatformOrderId` (`sourcePlatformOrderId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通告工单表';

1. 实体类与对应Dao接口

@Data
public class AnnounceOrderEntity {
    private Integer id;
    private Integer sourcePlatformOrderId;
    private String name;
    private String sourcePlatform;
    private Integer dealStatus;
    private Date createTime;
    private Date updateTime;
}

@Repository
public interface AnnounceOrderDao {

    /**
     * 新增数据
     * @param announceOrderEntity 数据
     * @return 影响的行数
     */
    int insert(AnnounceOrderEntity announceOrderEntity);

    /**
     * 更新数据
     * @param announceOrderEntity 数据
     * @return 影响的行数
     */
    int update(AnnounceOrderEntity announceOrderEntity);
}

2. insert数据实体类不设置createTime和updateTime属性值，SQL中也不指定该字段

@RunWith(SpringRunner.class)
@SpringBootTest
public class CompanyFrameApplicationTests {
    
    @Autowired
    private AnnounceOrderDao announceOrderDao;

    @Test
    public void test(){
        AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
        announceOrderEntity.setName("工单");
        announceOrderEntity.setDealStatus(0);
        announceOrderEntity.setSourcePlatformOrderId(1);
        announceOrderEntity.setSourcePlatform("来源平台");
        int i = announceOrderDao.insert(announceOrderEntity);
    }
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus
</sql>

<!--新增数据时，未指定createTime和updateTime字段-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
    #{sourcePlatformOrderId},
    #{name},
    #{sourcePlatform},
    #{dealStatus}
    )
</insert>

结论：createTime和updateTime将设置为当前的系统时间。

3. update数据时实体类不设置createTime和updateTime属性值，SQL中也不指定该字段

@RunWith(SpringRunner.class)
@SpringBootTest
public class CompanyFrameApplicationTests {

    @Autowired
    private AnnounceOrderDao announceOrderDao;

    @Test
    public void test1(){
        AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
        announceOrderEntity.setDealStatus(1);
        announceOrderEntity.setId(1);
        int i = announceOrderDao.update(announceOrderEntity);
    }
}

<update id="update">
    update t_announce_order set dealStatus=#{dealStatus} where id=#{id}
</update>

结论：updateTime将设置为当前的系统时间，createTime不变。

4. insert数据时实体类设置createTime和updateTime属性值，但SQL中不指定该字段

@RunWith(SpringRunner.class)
@SpringBootTest
public class CompanyFrameApplicationTests {

    @Autowired
    private AnnounceOrderDao announceOrderDao;

    @Test
    public void test(){
        AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
        announceOrderEntity.setName("工单");
        announceOrderEntity.setDealStatus(0);
        announceOrderEntity.setSourcePlatformOrderId(3);
        announceOrderEntity.setSourcePlatform("来源平台");
        long l = System.currentTimeMillis()[phone];
        Date now = new Date(l);
        // 2022-07-20 11:29:31
        announceOrderEntity.setCreateTime(now);
        // 2022-07-20 11:29:31
        announceOrderEntity.setUpdateTime(now);
        int i = announceOrderDao.insert(announceOrderEntity);
    }
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus
</sql>

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
    #{sourcePlatformOrderId},
    #{name},
    #{sourcePlatform},
    #{dealStatus}
    )
</insert>

结论：实体类设置的createTime和updateTime将不生效，createTime和updateTime将设置为当前的系统时间。

5. update数据时实体类设置createTime和updateTime属性值，但SQL中不指定该字段

@RunWith(SpringRunner.class)
@SpringBootTest
public class CompanyFrameApplicationTests {

    @Autowired
    private AnnounceOrderDao announceOrderDao;

    @Test
    public void test1(){
        AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
        announceOrderEntity.setDealStatus(1);
        announceOrderEntity.setId(8);
        long l = System.currentTimeMillis()[phone];
        Date now = new Date(l);
        // 2022-07-20 11:29:31
        announceOrderEntity.setCreateTime(now);
        // 2022-07-20 11:29:31
        announceOrderEntity.setUpdateTime(now);
        int i = announceOrderDao.update(announceOrderEntity);
    }
}

<update id="update">
    update t_announce_order set dealStatus=#{dealStatus} where id=#{id}
</update>

结论：实体类设置的createTime和updateTime将不生效，updateTime将设置为当前系统时间，createTime不变。

6. insert数据时实体类设置createTime和updateTime属性值，SQL中也指定该字段

@RunWith(SpringRunner.class)
@SpringBootTest
public class CompanyFrameApplicationTests {

    @Autowired
    private AnnounceOrderDao announceOrderDao;

    @Test
    public void test(){
        AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
        announceOrderEntity.setName("工单");
        announceOrderEntity.setDealStatus(0);
        announceOrderEntity.setSourcePlatformOrderId(5);
        announceOrderEntity.setSourcePlatform("来源平台");
        long l = System.currentTimeMillis()[phone];
        // 2022-07-20 11:29:31
        Date now = new Date(l);
        announceOrderEntity.setCreateTime(now);
        announceOrderEntity.setUpdateTime(now);
        int i = announceOrderDao.insert(announceOrderEntity);
    }
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus,createTime,updateTime
</sql>
<!--数据库中插入数据时指定createTime，updateTime-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
    #{sourcePlatformOrderId},
    #{name},
    #{sourcePlatform},
    #{dealStatus},
    #{createTime},
    #{updateTime}
    )
</insert>

结论：createTime和updateTime将设置为实体类中指定的时间。

7. update数据时实体类设置createTime和updateTime属性值，SQL中也指定该字段

@RunWith(SpringRunner.class)
@SpringBootTest
public class CompanyFrameApplicationTests {

    @Autowired
    private AnnounceOrderDao announceOrderDao;

    @Test
    public void test1(){
        AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
        announceOrderEntity.setDealStatus(1);
        announceOrderEntity.setId(9);
        long l = System.currentTimeMillis()[phone];
        // 2022-07-20 11:29:31
        Date now = new Date(l);
        announceOrderEntity.setCreateTime(now);
        announceOrderEntity.setUpdateTime(now);
        int i = announceOrderDao.update(announceOrderEntity);
    }
}

<update id="update">
    update t_announce_order set dealStatus=#{dealStatus},createTime=#{createTime},updateTime=#{updateTime} where id=#{id}
</update>

结论：createTime和updateTime将设置为实体类中指定的时间。

8. insert数据时实体类未设置createTime和updateTime属性值，SQL中指定了该字段

@RunWith(SpringRunner.class)
@SpringBootTest
public class CompanyFrameApplicationTests {

    @Autowired
    private AnnounceOrderDao announceOrderDao;

    @Test
    public void test(){
        AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
        announceOrderEntity.setName("工单");
        announceOrderEntity.setDealStatus(0);
        announceOrderEntity.setSourcePlatformOrderId(6);
        announceOrderEntity.setSourcePlatform("来源平台");
        int i = announceOrderDao.insert(announceOrderEntity);
    }
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus,createTime,updateTime
</sql>

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
    #{sourcePlatformOrderId},
    #{name},
    #{sourcePlatform},
    #{dealStatus},
    #{createTime},
    #{updateTime}
    )
</insert>

结论：将会抛出异常：

更新数据时同理。

9. 总结

• 如果在insert或者update一条数据时，实体类没有设置createTime和updateTime属性值，且数据库SQL中也没指定该字段，那么插入数据时，将以系统时间为准。
• 如果在insert或者update一条数据时，实体类设置了createTime和updateTime属性值，但是数据库SQL中没指定该字段，那么插入数据时，将以系统时间为准。
• 如果在insert或者update一条数据时，实体类设置了createTime和updateTime属性值，数据库SQL中也指定该字段，那么插入数据时，将以实体类中设置的时间为准。
• 如果在insert或者update一条数据时，实体类没有createTime和updateTime属性值，但是数据库SQL中指定了该字段，那么插入数据时，将会报错Column createTime cannot be null。
• 如果在建表时设置了 CURRENT_TIMESTAMP，那么创建时间时会设置当前系统时间，且更新数据时不会更改；
• 如果在建表时设置了 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，那么创建时间时会设置当前系统时间，更新数据时会修改为当前系统时间；

2. 建表时时间字段使用timestamp

CREATE TABLE `t_announce_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sourcePlatformOrderId` int(11) NOT NULL COMMENT '来源平台工单id',
  `name` varchar(100) NOT NULL COMMENT '工单名称',
  `sourcePlatform` varchar(100) NOT NULL COMMENT '来源平台',
  `dealStatus` tinyint(4) NOT NULL COMMENT '处置状态(0:待处置,1:已处置)',
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sourcePlatformOrderId` (`sourcePlatformOrderId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通告工单表';

1. 实体类与对应Dao接口

@Data
public class AnnounceOrderEntity {
    private Integer id;
    private Integer sourcePlatformOrderId;
    private String name;
    private String sourcePlatform;
    private Integer dealStatus;
    private Timestamp createTime;
    private Timestamp updateTime;
}

@Repository
public interface AnnounceOrderDao {

    /**
     * 新增数据
     * @param announceOrderEntity 数据
     * @return 影响的行数
     */
    int insert(AnnounceOrderEntity announceOrderEntity);

    /**
     * 更新数据
     * @param announceOrderEntity 数据
     * @return 影响的行数
     */
    int update(AnnounceOrderEntity announceOrderEntity);
}

2. insert数据实体类不设置createTime和updateTime属性值，SQL中也不指定该字段

@Test
public void test(){
    AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
    announceOrderEntity.setName("工单");
    announceOrderEntity.setDealStatus(0);
    announceOrderEntity.setSourcePlatformOrderId(6);
    announceOrderEntity.setSourcePlatform("来源平台");
    int i = announceOrderDao.insert(announceOrderEntity);
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus
</sql>

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
        #{sourcePlatformOrderId},
        #{name},
        #{sourcePlatform},
        #{dealStatus}
    )
</insert>

结论：createTime和updateTime将设置为系统当前时间

结论：更新数据时，updateTime将设置为系统当前时间。

3. insert数据时实体类设置createTime和updateTime属性值，但SQL中不指定该字段

@Test
public void test1(){
    AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
    announceOrderEntity.setDealStatus(1);
    announceOrderEntity.setId(12);
    long current = System.currentTimeMillis()[phone];
    Timestamp now = new Timestamp(current);
    announceOrderEntity.setCreateTime(now);
    announceOrderEntity.setUpdateTime(now);
    int i = announceOrderDao.update(announceOrderEntity);
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus
</sql>

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
        #{sourcePlatformOrderId},
        #{name},
        #{sourcePlatform},
        #{dealStatus}
    )
</insert>

结论：createTime和updateTime将设置为系统当前时间 。

4. insert数据时实体类设置createTime和updateTime属性值，SQL中也指定该字段

@Test
public void test2(){
    AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
    announceOrderEntity.setName("工单");
    announceOrderEntity.setDealStatus(0);
    announceOrderEntity.setSourcePlatformOrderId(8);
    announceOrderEntity.setSourcePlatform("来源平台");
    long current = System.currentTimeMillis()[phone];
    Timestamp now = new Timestamp(current);
    announceOrderEntity.setCreateTime(now);
    announceOrderEntity.setUpdateTime(now);
    int i = announceOrderDao.insert(announceOrderEntity);
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus,createTime,updateTime
</sql>

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
        #{sourcePlatformOrderId},
        #{name},
        #{sourcePlatform},
        #{dealStatus},
        #{createTime},
        #{updateTime}
    )
</insert>

结论：createTime和updateTime将设置为实体类中指定的时间。

5. insert数据时实体类未设置createTime和updateTime属性值，SQL中指定了该字段

@Test
public void test(){
    AnnounceOrderEntity announceOrderEntity = new AnnounceOrderEntity();
    announceOrderEntity.setName("工单");
    announceOrderEntity.setDealStatus(0);
    announceOrderEntity.setSourcePlatformOrderId(9);
    announceOrderEntity.setSourcePlatform("来源平台");
    int i = announceOrderDao.insert(announceOrderEntity);
}

<sql id="insertFields">
    sourcePlatformOrderId,name,sourcePlatform,dealStatus,createTime,updateTime
</sql>

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into t_announce_order(<include refid="insertFields"></include>)
    values(
        #{sourcePlatformOrderId},
        #{name},
        #{sourcePlatform},
        #{dealStatus},
        #{createTime},
        #{updateTime}
    )
</insert>

结论：将会抛出异常

6. 总结

结论同datetime的使用情况