MySQL 设计数据表时,时间类型 datetime、bigint、timestamp MySQL日期函数
2023-09-11 14:14:56 时间
阅读目录
阐述
数据库中可以用 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 分组速率测试
使用 bigint 进行分组会每条数据进行一个分组,如果将bigint做一个转化在去分组就没有比较的意义了,转化也是需要时间的。
通过 datetime 类型分组:
select time_date, count(*) from users group by time_date
耗时:0.176s
通过 timestamp 类型分组:
SELECT
time_timestamp,
count(*)
FROM
users
GROUP BY
time_timestamp
耗时:0.173s
结论 在InnoDB存储引擎下,通过时间分组,性能 timestamp > datetime,但是相差不大。
sql 排序速率测试
通过 datetime 类型排序:
select * from users order by time_date
耗时:1.038s
通过 timestamp 类型排序
select * from users order by time_timestamp
耗时:0.933s
通过 bigint 类型排序
select * from users order by time_long
耗时:0.775s
结论 在InnoDB存储引擎下,通过时间排序,性能bigint > timestamp > datetime。
结论
如果需要对时间字段进行操作(如通过时间范围查找或者排序等),推荐使用bigint,如果时间字段不需要进行任何操作,推荐使用 timestamp,使用4个字节保存比较节省空间,但是只能记录到2038年记录的时间有限。
MySQL日期函数
1、DATE()
返回 日期。格式:YYYY-MM-DD
SELECT DATE(NOW());
> 2022-04-04
2、TIME()
返回 日期。格式:HH-mm-ss
SELECT TIME(NOW());
> 16:25:09
3、TIMESTAMP()
返回 日期时间。格式:YYYY-MM-DD HH-mm-ss
SELECT TIMESTAMP(NOW());
> 2022-04-04 16:31:12
4、UNIX_TIMESTAMP()
获取日期的时间戳(10位)
SELECT unix_timestamp(now());
> 1649083817
相关文章
- Mysql加锁过程详解(7)-初步理解MySQL的gap锁
- Mysql加锁过程详解(3)-关于mysql 幻读理解
- mysql中查看某个日期是星期几?如何知道某个日期是星期几?某个日期是周几?
- 报错:MySQL Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
- Mysql运行模式及1690错误处理
- MySQL 8 中的数据类型转换 | 学习函数CAST() 和 CONVERT()
- 在 MySQL 中处理日期和时间 - 第一章节
- Linux有问必答:如何通过命令行创建和设置一个MySQL用户
- 连接Mysql提示Can’t connect to local MySQL server through socket的解决方法
- 【MySQL进阶-06】深入理解mysql的内核查询成本计算
- Mysql事物与二阶段提交
- mysql 诡异的1054错误
- 【mysql问题】解决2003-Can‘t connect to MySQL server on ‘ ‘(10060“Unknown error“)
- Mysql date_to_str()函数 日期格式化详细解析
- MYSQL日期函数
- mysql知识集锦
- MySQL实战之四:Kettle连接与操作
- idea 社区版连接mysql数据库教程,Cannot connect to “MySQL-5.1.43“. CLIENT_PLUGIN_AUTH is required
- Windows环境下 PyQt5 如何安装MySql驱动 (PyQt5连接MYSQL时显示Driver not loaded解决方案)
- MySQL 5.6 解决InnoDB: Error: Table "mysql"."innodb_table_stats" not found.问题
- 【MySQL】15-超详细MySQL日期类型函数总结
- 【MySQL】2.2 SQL语言规则与规范
- mysql-日期时间函数大全
- MySQL统计相关日期和case
- mysql_ado的demo
- mysql半同步复制实现
- 【MySQL】mysql更换root密码,全网唯一有用!
- (5.5)mysql高可用系列——MySQL半同步复制(实践)
- (4.15)mysql备份还原——物理备份之XtraBackup的下载与安装
- mysql测试spring事务是否生效
- Java新手入门200例124之用JDBC连接Mysql数据库
- Mysql 中的日期时间函数汇总
- mysql 日期转换sql函数