向视图插入数据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?

解决方案

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:

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 INSERT fails:

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)