是否可以将数据插入到 MySQL 视图中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3825941/
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
Is it possible to insert data into a MySQL view?
提问by mullek
I made a MySQL view with 4 tables. Is it possible to insert data into the view and have MySQL automatically pass the data into the right table?
我用 4 个表创建了一个 MySQL 视图。是否可以将数据插入视图并让 MySQL 自动将数据传递到正确的表中？
回答by Daniel Vassallo
If you are using inner joins, and your view contains all the columns in the base tables, then your view might be updatable. However, for a multiple-table updatable view, INSERTcan work if it inserts into a single table. You could split your insert operation into multiple INSERTstatements.
如果您使用内部联接，并且您的视图包含基表中的所有列，则您的视图可能是可更新的。但是，对于多表可更新视图，INSERT如果它插入到单个表中就可以工作。您可以将插入操作拆分为多个INSERT语句。
You may want to check out the following article for more information on the topic:
您可能需要查看以下文章以获取有关该主题的更多信息：
Consider the following example:
考虑以下示例：
CREATE TABLE table_a (id int, value int);
CREATE TABLE table_b (id int, ta_id int, value int);
INSERT INTO table_a VALUES (1, 10);
INSERT INTO table_a VALUES (2, 20);
INSERT INTO table_a VALUES (3, 30);
INSERT INTO table_b VALUES (1, 1, 100);
INSERT INTO table_b VALUES (2, 1, 200);
INSERT INTO table_b VALUES (3, 2, 300);
INSERT INTO table_b VALUES (4, 2, 400);
Now let's create a view:
现在让我们创建一个视图：
CREATE VIEW v AS
SELECT a.id a_id, b.id b_id, b.ta_id, a.value v1, b.value v2
FROM table_a a
INNER JOIN table_b b ON (b.ta_id = a.id);
SELECT * FROM v;
+------+------+-------+------+------+
| a_id | b_id | ta_id | v1 | v2 |
+------+------+-------+------+------+
| 1 | 1 | 1 | 10 | 100 |
| 1 | 2 | 1 | 10 | 200 |
| 2 | 3 | 2 | 20 | 300 |
| 2 | 4 | 2 | 20 | 400 |
+------+------+-------+------+------+
4 rows in set (0.00 sec)
The following INSERTfails:
以下INSERT失败：
INSERT INTO v (a_id, b_id, ta_id, v1, v2) VALUES (3, 5, 3, 30, 500);
-- ERROR 1393 (HY000): Can not modify more than one base table through a join view
But we can split it into two operations:
但是我们可以将其拆分为两个操作：
INSERT INTO v (a_id, v1) VALUES (3, 30);
-- Query OK, 1 row affected (0.00 sec)
INSERT INTO v (b_id, ta_id, v2) VALUES (5, 3, 500);
-- Query OK, 1 row affected (0.00 sec)
Result:
结果：
SELECT * FROM v;
+------+------+-------+------+------+
| a_id | b_id | ta_id | v1 | v2 |
+------+------+-------+------+------+
| 1 | 1 | 1 | 10 | 100 |
| 1 | 2 | 1 | 10 | 200 |
| 2 | 3 | 2 | 20 | 300 |
| 2 | 4 | 2 | 20 | 400 |
| 3 | 5 | 3 | 30 | 500 |
+------+------+-------+------+------+
6 rows in set (0.00 sec)
