MySQL 设计数据表时，时间类型 datetime、bigint、timestamp MySQL日期函数

ゝ一纸荒年。 2023-10-07 22:12 27阅读 0赞

#### 阅读目录 ####

*  阐述
 *   *  数据表
     *  MySQL 调用存储过程实现批量插入数据
     *  sql 查询速率测试
     *  sql 分组速率测试
     *  sql 排序速率测试
     *  结论
 *  MySQL日期函数
 *   *  1、DATE()
     *  2、TIME()
     *  3、TIMESTAMP()
     *  4、UNIX\_TIMESTAMP()

## 阐述 ##

数据库中可以用 datetime、bigint、timestamp来表示时间，那么选择什么类型来存储时间比较合适呢？

### 数据表 ###

通过程序往数据库插入 50w 数据。

CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `time_date` datetime NOT NULL,
      `time_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `time_long` bigint(20) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `time_long` (`time_long`),
      KEY `time_timestamp` (`time_timestamp`),
      KEY `time_date` (`time_date`)
    ) ENGINE=InnoDB AUTO_INCREMENT=500003 DEFAULT CHARSET=latin1

### MySQL 调用存储过程实现批量插入数据 ###

CREATE PROCEDURE tb_option ()
    BEGIN
    
    DECLARE i BIGINT DEFAULT 1;
    
    
    WHILE i < 50000001 DO
    	INSERT INTO `yiibaidb`.`users` (
    		`time_date`,
    		`time_timestamp`,
    		`time_long`
    	)
    VALUES
    	(
    		TIMESTAMP (NOW()),
    		TIMESTAMP (NOW()),
    		UNIX_TIMESTAMP(now())
    	);
    
    
    SET i = i + 1;
    
    
    END
    WHILE;
    
    COMMIT;
    
    #提交
    END;
    
    CALL tb_option ();

### sql 查询速率测试 ###

通过 datetime 类型查询：

SELECT
    	count(*)
    FROM
    	users
    WHERE
    	time_date >= "2022-12-01 23:32:44"
    AND time_date <= "2022-12-01 23:41:22"

耗时：0.171

通过 timestamp 类型查询

SELECT
    	count(*)
    FROM
    	users
    WHERE
    	time_timestamp >= "2018-10-21 23:32:44"
    AND time_timestamp <= "2018-10-21 23:41:22"

耗时：0.351

通过 bigint 类型查询

SELECT
    	count(*)
    FROM
    	users
    WHERE
    	time_long >= 1540135964091
    AND time_long <= 1540136482372

耗时：0.130s

> 结论 在InnoDB存储引擎下，通过时间范围查找，性能bigint > datetime > timestamp。

### sql 分组速率测试 ###