MySQL 如何更新 phpMyAdmin 中的视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15156308/
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 update a view in phpMyAdmin?
提问by MarcinWolny
How can I update (or edit) a View in MySQL database using phpMyAdmin.
如何使用 phpMyAdmin 更新（或编辑）MySQL 数据库中的视图。
I got a view that's made of columns from two tables - I added a new column to one of these, but the view doesn't have it. I can't find the MySQL query I used to get this view (it's quite obscure one) - so how can I edit the MySQL query that created this view to add a new column into it?
我得到了一个由两个表中的列组成的视图 - 我在其中一个表中添加了一个新列，但该视图没有它。我找不到用于获取此视图的 MySQL 查询（它非常晦涩难懂） - 那么如何编辑创建此视图的 MySQL 查询以向其中添加新列？
采纳答案by Jason
How about using (Your view is called viewname)
如何使用（您的视图称为视图名称）
SHOW CREATE VIEW viewnameto get the SQL for the view as it isDROP VIEW viewnameto remove the view- Modify the SQL from the first step to add the new column to that SQL
- Run the modified SQL
SHOW CREATE VIEW viewname按原样获取视图的 SQLDROP VIEW viewname删除视图- 修改第一步中的 SQL 以将新列添加到该 SQL
- 运行修改后的 SQL
That would create the view with the additional column(s)
这将创建带有附加列的视图
http://dev.mysql.com/doc/refman/5.0/en/show-create-view.html
http://dev.mysql.com/doc/refman/5.0/en/show-create-view.html
回答by user2775080
To take the view for edit/update,we have used two way:
为了获取编辑/更新视图，我们使用了两种方法：
Step 1:
select your view in phpmyadmin and click Export(make sure click check box of Structure& Add DROP VIEW) & GO.you'll see a CREATE VIEW query like as:CREATE ALGORITHM=UNDEFINED DEFINER=`dbname`@`localhost` SQL SECURITY DEFINER VIEW `vw_name` AS select ..etc..And then remove 'ALGORITHM.....to...DEFINER' part from this query and update/added required field/make changes to the query.and then execute modified view.`
step 2:
Run the query: SHOW CREATE VIEW `vw_name`
Expand the result and choose Full Texts.
Copy entire contents of the Create View column.
Make changes to the query.
Run the query directly (with out the CREATE VIEW... syntax) to make sure it runs as you expect it to.
回答by Aris
You can also use CREATE OR REPLACE VIEW, to avoid the step of dropping the view:
您还可以使用CREATE OR REPLACE VIEW, 以避免删除视图的步骤：
show create view viewname. Find definition in 'Create View' column- use below command to add new columns:
CREATE OR REPLACE VIEW
show create view viewname. 在“创建视图”列中查找定义- 使用以下命令添加新列：
CREATE OR REPLACE VIEW
回答by Nahser Bakht
On phpMyADmin Go to Export -> (select) 'Quick - display only the minimal options' -> GO.
在 phpMyADmin 上，转至导出 ->（选择）“快速 - 仅显示最小选项”-> 转。
That will give you the create statements that you made in a text file, view or save that text file and you should have all the info there.
这将为您提供在文本文件中创建的创建语句，查看或保存该文本文件，您应该在那里拥有所有信息。
回答by Henrik Erlandsson
A Simpler Way
更简单的方法
Most of the time, not being able to edit views etc. is due to the DEFINER being set to root@localhost, and if you're coming from a web host control panel, that user is not you.
大多数情况下，无法编辑视图等是由于 DEFINER 设置为 root@localhost，如果您来自 Web 主机控制面板，则该用户不是您。
- Click Home (house icon) top left, copy your username to the right.
- Click your view on the left > Structure > Edit View and edit.
- Paste your username in place of 'root@localhost' and click Go.
- 单击左上角的主页（房子图标），将您的用户名复制到右侧。
- 单击左侧的视图 > 结构 > 编辑视图并进行编辑。
- 将您的用户名粘贴到“root@localhost”的位置，然后单击 Go。
This has been tested on PHPMyAdmin 4.9.0.1, it may also work on earlier and later versions. PHPMyAdmin seems to remember your username for the rest of the session, so that you can edit freely.
这已经在 PHPMyAdmin 4.9.0.1 上测试过，它也可能适用于早期和更高版本。PHPMyAdmin 似乎会在剩余的会话中记住您的用户名，以便您可以自由编辑。
