MySQL的日期时间类型

一 MySQL的日期时间类型

MySQL数据库的日期时间类型有date、time和datetime类型，还有timestamp类型，在Java代码中无论日期时间是什么样的格式，转换sql语句时统一为yyyy-MM-dd HH:mm:ss.S(Timestamp)的格式，创建案例简单演示。

1.1 创建数据库表

CREATE TABLE `apple` (
  `id` varchar(255) DEFAULT NULL,
  `date_variable` date DEFAULT NULL,
  `time_variable` time DEFAULT NULL,
  `datetime_variable` datetime DEFAULT NULL
)

1.2 创建apple表的基础代码

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("apple")
public class Apple {
    private String id;
    private Date dateVariable;
    private Date timeVariable;
    private Date datetimeVariable;
}

@Mapper
public interface AppleMapper extends BaseMapper<Apple> {
}

public interface IAppleService extends IService<Apple> {
}

@Service
public class AppleServiceImpl extends ServiceImpl<AppleMapper, Apple> 
implements IAppleService {
}

1.3 插入日期时间数据

@SpringBootTest
@RunWith(SpringRunner.class)
public class AppleTest {
    @Autowired
    private IAppleService appleService;

    @Test
    public void test1() throws ParseException {
        Apple apple = new Apple();
        apple.setId("1001");
        SimpleDateFormat simpleDateFormat = 
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = simpleDateFormat.parse("2024-01-01 01:01:01");
        apple.setDatetimeVariable(date);
        appleService.save(apple);
    }

}

当代码中的日期时间格式是yyyy-MM-dd HH:mm:ss，没有指定毫秒值，sql语句解析如下：
==> Preparing: INSERT INTO apple ( id, datetime_variable ) VALUES ( , )
==> Parameters: 1001(String), 2024-01-01 01:01:01.0(Timestamp)
<== Updates: 1

@SpringBootTest
@RunWith(SpringRunner.class)
public class AppleTest {
    @Autowired
    private IAppleSer