MySQL日期时间索引在使用'赞'时不起作用 [英] Mysql datetime index is not working while using 'like'
问题描述
我在MySQL中创建了一个表:
I created a table in MySQL:
CREATE TABLE index_test(
moment DATETIME,
one_more_attr VARCHAR(10)
);
ALTER TABLE index_test ADD INDEX(moment);
有两个查询字符串:
A: `SELECT * FROM index_test WHERE moment LIKE '2015-06-08%'`
B: `SELECT * FROM index_test WHERE moment BETWEEN '2015:06:08 00:00:00' AND '2015:06:08 23:00:00'`
使用'explain'语句,我发现语句B正在使用索引,而A没有.
Using the 'explain' statement, I found that statement B is using the index, but A not.
当我进行"moment" varchar时,它们都使用索引.有人可以告诉我原因吗?
And when I make 'moment' varchar, they both use the index. Is anybody can tell me the reason?
推荐答案
在MySQL中, DATETIME
类型在内部存储为8字节数字,而 not 作为文本存储细绳.因此,您在 moment
列上创建的索引将用于查找给定的数字日期 value .
In MySQL the DATETIME
type is stored internally as an 8 byte number, and not as a text string. Therefore, the index you created on the moment
column will work for looking up a given numerical date value.
在您的查询中:
SELECT * FROM index_test WHERE moment LIKE '2015-06-08%'
MySQL引擎首先将 moment
列隐式转换为 VARCHAR
类型,然后与'2015-06-08%'
.
the MySQL engine first implicitly casts the moment
column to a VARCHAR
type and then does the comparison against '2015-06-08%'
.
这篇关于MySQL日期时间索引在使用'赞'时不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!