mariadb视图不起作用，但语句起作用

nvbavucw 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(361)

我的新主机提供商运行的是mysql版本10.0.31-mariadb-cll-lve
我有一个观点在mysql 5.6中运行良好，但在mariadb中不起作用。
我创建了一个简单的简化版本，只是为了说明是什么导致了错误。
我可以创建视图，但不能使用它：

CREATE VIEW `test_date` 
AS select (case 
   when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') 
     then '2018-06-30' 
   else NULL end) - interval 4 month

尝试打开时出现的错误：


# 1064 - You have an error in your SQL syntax; check the manual that

corresponds to your MariaDB server version for the right syntax to use 
near '21:05:05 between 2018-01-01 00:00:00 and 2018-06-30 23:59:59) 
then '2018-06-30' ' at line 1

我看不出有什么问题，它在普通的mysql服务器上运行良好。
我尝试过删除'-interval 4 month'，效果很好：

CREATE VIEW `test_date` 
AS select case 
  when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') 
    then '2018-06-30' 
  else NULL end

我试过用简单的数字代替日期，效果很好：

CREATE VIEW `test_date` 
AS select (case 
   when (3 between 1 and 5) 
     then '2018-06-30' 
   else NULL end) - interval 4 month

那么这里真正的问题是什么？我被难住了。

sql mysql mariadb sql-view sqldatetime

来源：https://stackoverflow.com/questions/48299559/mariadb-view-not-working-but-statement-does

1条答案

按热度按时间

vhmi4jdf1#

“2018-06-30”并不是隐式转换为日期（我猜这是mysql版本或是mariadb的fork版本之间被收紧的事情之一），请尝试显式转换它。

drop view if exists test_date;
CREATE VIEW `test_date` AS 
select (case when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') then str_to_date('2018-06-30','%Y-%m-%d') else NULL end) 
- interval 4 month;

select * from test_date;

+------------+
| Name_exp_1 |
+------------+
| 2018-02-28 |
+------------+
1 row in set (0.00 sec)

奇怪的是，select单独工作很好，只有在视图中使用时（可能与between语句结合使用），它才不会工作

MariaDB [sandbox]> select (case
    ->    when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59')
    ->      then '2018-06-30'
    ->    else NULL end) - interval 4 month rd;
+------------+
| rd         |
+------------+
| 2018-02-28 |
+------------+
1 row in set (0.00 sec)

在视图中使用时会引发错误

MariaDB [sandbox]> create view test_date as
    -> select
    -> (
    -> case when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') then '2018-06-30'
    -> else NULL
    -> end
    -> ) - interval 4 month as rd
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [sandbox]> select rd from test_date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '08:16:57 between 2018-01-01 00:00:00 and 2018-06-30 23:59:59) then '2018-06-30' ' at line 1

如果between语句替换为>=，<=

MariaDB [sandbox]> create view test_date as
    -> select
    -> (
    -> case when (now() >= '2018-01-01 00:00:00' and now() <= '2018-06-30 23:59:59') then '2018-06-30'
    -> else NULL
    -> end
    -> ) - interval 4 month as rd
    -> ;
Query OK, 0 rows affected (0.04 sec)

MariaDB [sandbox]> select rd from test_date;
+------------+
| rd         |
+------------+
| 2018-02-28 |
+------------+
1 row in set (0.00 sec)

赞(0）回复(0）举报 2021-06-21

我来回答

mariadb视图不起作用，但语句起作用

1条答案

相关问题

热门标签

最新问答