是否可以将数据插入MySQL视图? [英] Is it possible to insert data into a MySQL view?

问题描述

我用4个表制作了一个MySQL视图.是否可以在视图中插入数据并使MySQL自动将数据传递到正确的表中?

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?

推荐答案

如果您正在使用内部联接，并且您的视图包含基表中的所有列，那么您的视图可能是可更新的.但是，对于多表可更新视图，如果INSERT插入单个表中，则它可以工作.您可以将插入操作拆分为多个INSERT语句.

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, INSERT can work if it inserts into a single table. You could split your insert operation into multiple INSERT statements.

您可能想查看以下文章，以获取有关该主题的更多信息:

You may want to check out the following article for more information on the topic:

• MySQL参考手册::可更新和可插入视图

请考虑以下示例:

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)

以下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)

结果:

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)

这篇关于是否可以将数据插入MySQL视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！