MySQL 如何修复损坏的 SQL 视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8150804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fix a broken SQL view
提问by Simon Robb
I am using the open source CMS pimcore (http://www.pimcore.org), which runs on a MySQL backend.
我正在使用在 MySQL 后端运行的开源 CMS pimcore (http://www.pimcore.org)。
It uses fairly complicated views to represent objects and one of them has become broken at some stage when a column in another table being referenced by the view was renamed. Whenever I try to interact with the table via SQL commands I receive the error:
它使用相当复杂的视图来表示对象，并且当视图引用的另一个表中的列被重命名时，其中一个在某个阶段已损坏。每当我尝试通过 SQL 命令与表交互时，我都会收到错误消息：
View 'barriste_website.object_6' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
视图“barriste_website.object_6”引用了无效的表或列或函数或视图的定义者/调用者缺乏使用它们的权利
I would like to simply update the view to reference the renamed columns, but I need to know the current structure of the view before I start poking around - how exactly do I retrieve the structure of the view once it is broken? I've tried
我想简单地更新视图以引用重命名的列，但我需要在开始浏览之前知道视图的当前结构 - 一旦视图损坏，我究竟如何检索视图的结构？我试过了
SHOW CREATE VIEW object_6
but I receive the same error.
但我收到同样的错误。
Thanks for your help in advance!
提前感谢您的帮助！
回答by Davide Piras
MySQL:
MySQL：
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
reference: The INFORMATION_SCHEMA VIEWS Table
SQL Server:
SQL 服务器：
USE databasename
GO
EXEC sp_helptext viewName
or also a query like this:
或者也是这样的查询：
SELECT TABLE_NAME as ViewName,
VIEW_DEFINITION as ViewDefinition
FROM INFORMATION_SCHEMA.Views
where you could add a WHEREto only retrieve one view
您可以在其中添加一个WHERE以仅检索一个视图
回答by Ben
Simply delete the view with "drop view object_6", then go into the pimcore backend and save the class again. The view is then regenerated automatically.
只需使用“drop view object_6”删除视图，然后进入 pimcore 后端并再次保存该类。然后会自动重新生成视图。
回答by Nae
In my case the culprit was to use ORDER BYwith an aliascausing the issue. I changed my view from:
在我的情况下，罪魁祸首是ORDER BY与导致问题的别名一起使用。我改变了我的看法：
CREATE VIEW v_storename_totalnamelength AS
SELECT
(char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) AS `TotalNameLength`
FROM
`promoter`.`v_storename_extracted` `a`
ORDER BY
`TotalNameLength` DESC
;
to:
到：
CREATE VIEW v_storename_totalnamelength AS
SELECT
(char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) AS `TotalNameLength`
FROM
`promoter`.`v_storename_extracted` `a`
ORDER BY
(char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) DESC
;
