mysql - MySQL:优化格式化日期的左连接
问题描述
我正在尝试优化此查询的速度:
SELECT t.t_date td, v.visit_date vd
FROM temp_dates t
LEFT JOIN visits v ON DATE_FORMAT(v.visit_date, '%Y-%m-%d') = t.t_date
ORDER BY t.t_date
v.visit_date 是 DATETIME 类型,t.t_date 是格式为 '%Y-%m-%d' 的字符串。简单地在 v.visitdate 上创建索引并没有提高速度。因此,我打算尝试@oysteing 在这里给出的解决方案:
How to optimize mysql group by with DATE_FORMAT
我成功地通过此 SQL 创建了一个虚拟列
ALTER TABLE visits ADD COLUMN datestr varchar(10) AS (DATE_FORMAT(visit_date, '%Y-%m-%d')) VIRTUAL;
但是当我尝试在此列上创建索引时,
CREATE INDEX idx_visit_date on visits(datestr)
我收到此错误:
#1901 - 函数或表达式 'date_format()' 不能在 GENERATED ALWAYS AS 子句中使用
datestr
我究竟做错了什么?我的数据库是 Maria DB 10.4.8
最好的问候 - 乌尔里希
解决方案
date_format()
也不能用于持久生成的列。而且在索引中它不能只是虚拟的,它必须被持久化。
我在手册中找不到明确的声明,但我相信这是因为输出date_format()
可以取决于语言环境,因此不是严格确定的。
date_format()
您可以使用确定性函数(例如concat()
、year()
、和)来构建字符串month()
,而不是。day()
lpad()
...
datestr varchar(10) AS (concat(year(visit_date),
'-',
lpad(month(visit_date), 2, '0'),
'-',
lpad(day(visit_date), 2, '0')))
...
但正如我在评论中已经提到的那样,你正在修复错误的结局。日期/时间不应存储为字符串。因此,您应该提升temp_dates.t_date
为 adate
并使用date()
来提取生成的索引列中的date
部分visit_date
...
visit_date_date date AS (date(visit_date))
...
而且您可能还想尝试索引temp_dates.t_date
。
推荐阅读
- javascript - res.json() 以 res.data 的形式发送数据,但在其中访问 _id 是未定义的
- spring-security - spring security数据库登录报错,报错对象不为空
- javascript - 使用 React Hooks + Firestore 将字段值作为 null 添加到集合中
- c++ - LLVM comparing between a pointer and a constant
- python - 是否有生成暴雪 API 访问令牌的 python 脚本?
- reliability - 使用 RandomVector 的子集时,OpenTURNS 可靠性模型的结果非常不准确
- python - Python中有没有办法在熊猫时间序列中标记中国假期
- javascript - 使用 DOMParser 从字符串中解析 HTML 并返回对象数组
- bash - bash 中的多个列表和多个文件类型
- security - 访问 R 包的标准?