mysql - 从视图中选择时不能在子查询中使用参数
问题描述
系统: MariaDB 10.3.15、python 3.7.2、mysql.connector python包
在使用如下所述的表结构执行查询时,我无法确定问题的确切原因,可能是 MariaDB/mySQL 中的错误。令人困惑的部分是错误消息
1356 (HY000): View 'test_project.denormalized' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
起初这似乎与问题有关,但我越深入研究为什么会发生这种情况,我就越觉得这个错误消息是一个红鲱鱼。
重现步骤:
CREATE DATABASE `test_project`;
USE `test_project`;
CREATE TABLE `normalized` (
`id` INT NOT NULL AUTO_INCREMENT,
`foreign_key` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`value` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
INSERT INTO `normalized` (`foreign_key`, `name`, `value`) VALUES
(1, 'attr_1', '1'),
(1, 'attr_2', '2'),
(2, 'attr_1', '3'),
(2, 'attr_2', '4');
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `denormalized` AS
select
max(`iq`.`foreign_key`) AS `foreign_key`,
max(`iq`.`attr_1`) AS `attribute_1`,
max(`iq`.`attr_2`) AS `attribute_2`
from (
select
`foreign_key` AS `foreign_key`,
if(`name` = 'attr_1',`value`,NULL) AS `attr_1`,
if(`name` = 'attr_2',`value`,NULL) AS `attr_2`
from `normalized`
) as `iq`
group by `iq`.`foreign_key`;
使用 python 连接到数据库并执行以下查询:
conn = mysql.connector.connect(host="somehost", user="someuser", password="somepassword")
cursor = conn.cursor()
query = """select * from denormalized as d
where d.`foreign_key` in
(
SELECT distinct(foreign_key)
FROM normalized
where value = %s
);"""
cursor.execute(query, ["2"])
results = cursors.fetchall()
更多信息:起初我认为这显然是一个权限问题,但即使对所有内容都使用 root 并仔细检查主机和特定权限并没有改变任何东西。
然后我更深入地研究了所涉及的查询和视图的作用(上面的测试用例是我们数据库中实际内容的简化版本)并测试了每个部分。从视图中选择有效。运行视图的查询工作。使用静态子查询从视图中进行选择是可行的。事实上,用它的定义替换有问题的查询中的视图也可以。
我将其归结为使用子查询中的子查询中的where
参数从视图中进行选择。这会导致出现错误。使用静态子查询或用它的定义替换视图就可以了,只是在这种特定情况下它会失败。
我不知道为什么。
解决方案
group by 没有意义;你真的是指其中之一吗?
这将返回一行:
select max(`foreign_key`) AS `foreign_key`,
max(if(`name` = 'attr_1', `value`,NULL)) AS `attribute_1`,
max(if(`name` = 'attr_2', `value`,NULL)) AS `attribute_2`
from `normalized`;
这使用GROUP BY
并且每返回一行foreign_key
:
select `foreign_key`,
max(if(`name` = 'attr_1', `value`,NULL)) AS `attribute_1`,
max(if(`name` = 'attr_2', `value`,NULL)) AS `attribute_2`
from `normalized`
group by `foreign_key`;
您的 python 查询可能在以下任何一种形式中都更好:
select d.*
FROM ( SELECT distinct(foreign_key)
FROM normalized
where
value = %s )
JOIN denormalized as d;
select d.*
FROM denormalized as d
WHERE EXISTS ( SELECT 1
FROM normalized
where foreign_key = d.foreign_key
AND value = %s )
他们将从中受益INDEX(value, foreign_key)
。
推荐阅读
- sql - 我可以每天用一行计算多行的聚合持续时间吗?
- javascript - 从一个函数到另一个函数的全局变量
- c# - 如何向 MVC Core 中的控制器方法添加替代路由以支持旧端点
- javascript - 用于检索有关不同手机的信息的 api?
- javascript - Vue战争:预期数组,得到对象
- python - Docker 烧瓶应用程序不工作 - 端口问题
- angular - 钥匙斗篷和 Nexus
- c# - System.Web.Hosting 的 Nuget 包
- reactjs - 如何验证用户名 minlength 并使用反应挂钩显示错误消息
- arm - STM32H7 上 VTOR 寄存器在复位时的行为