正如上面评论中所解释的,INDEX(Dob)
不被使用——因为这是一个索引年月日。你必须创建一个索引月日.
可能不是最优雅的解决方案,但是:
CREATE TABLE `birthdayCard`(`Email` VARCHAR(255), `FirstName` CHAR(30), `LastName` CHAR(30),
`Mob` int, `Dob` int,
PRIMARY KEY (Email), INDEX(`Mob`, `Dob`));
See http://sqlfiddle.com/#!2/db82ff/1 http://sqlfiddle.com/#!2/db82ff/1
为了更好的(?)答案:由于 MySQL 不支持计算列,您可能需要触发器来填充“月日”列,并在其上建立索引:
CREATE TABLE `birthdayCard`(`Email` VARCHAR(255), `FirstName` CHAR(30), `LastName` CHAR(30),
`Dob` DATE,
`Birthday` CHAR(5),
PRIMARY KEY (Email), INDEX(`Birthday`));
CREATE TRIGGER ins_bithdayCard BEFORE INSERT ON `birthdayCard`
FOR EACH ROW
SET NEW.`birthday` = DATE_FORMAT(NEW.`Dob`, "%m%d");
CREATE TRIGGER upd_bithdayCard BEFORE UPDATE ON `birthdayCard`
FOR EACH ROW
SET NEW.`birthday` = DATE_FORMAT(NEW.`Dob`, "%m%d");
这允许“简单”插入,如果需要则保留完整的Dob
就像你原来的例子一样:
insert into birthdayCard (Email, FirstName, LastNAme, Dob)
values ("[email protected] /cdn-cgi/l/email-protection", "Sylvain", "Leroux", '2013-08-05');
The SELECT
必须修改查询才能使用新的“搜索”列:
SELECT * FROM `birthdayCard` WHERE Birthday = DATE_FORMAT(NOW(), "%m%d");
Sett http://sqlfiddle.com/#!2/66111/3 http://sqlfiddle.com/#!2/66111/3