34,588
社区成员
发帖
与我相关
我的任务
分享
-- 给你写个例子,你参考一下
mysql> use test;
Database changed
mysql>
mysql>
mysql> create table test(
-> id int,
-> year int,
-> month int,
-> day int,
-> crdate varchar(10) generated always as (concat_ws('-',year, month, day))
-> );
e = '2018-10-18';
drop table test;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into test(id, year, month,day) values(1,'2018','10','18'),(2,'2018','10','19');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from test where crdate = '2018-10-18';
+------+------+-------+------+------------+
| id | year | month | day | crdate |
+------+------+-------+------+------------+
| 1 | 2018 | 10 | 18 | 2018-10-18 |
+------+------+-------+------+------------+
1 row in set (0.00 sec)
mysql>
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
SELECT * FROM T1 AS tr WHERE STR_TO_DATE(CONCAT_WS('-',tr.`YEAR`,tr.`MONTH`,tr.`DAY`),'%Y-%m-%e') BETWEEN ? AND ?
还有没有更合适的函数或其它方式实现我测试的不可以,还需要转成Date才可以:
drop table if exists `t`;
create table `t`(
`id` int PRIMARY KEY,
`year` int,
`month` int,
`day` int,
`ymd` date generated always as (STR_TO_DATE(concat_ws('-',year, month, day),'%Y-%m-%e'))
);
CREATE index ix_t_ymd on `t`(`ymd`);
insert into `t`(`id`,`year`,`month`,`day`) values(1,2018,10,1);
insert into `t`(`id`,`year`,`month`,`day`) values(2,2018,10,2);
insert into `t`(`id`,`year`,`month`,`day`) values(3,2018,10,3);
insert into `t`(`id`,`year`,`month`,`day`) values(4,2018,10,4);
insert into `t`(`id`,`year`,`month`,`day`) values(5,2018,10,5);
SELECT * from `t` where `ymd` between '2018-10-02' and '2018-10-04';
加的哪一列计算列不是date类型?
[quote=引用 5 楼 xiaofanku 的回复:] 关键 是能否实现between范围查询?加个列例不是事哈
关键 是能否实现between范围查询?加个列例不是事哈