基于视图的MySQL默认值 [英] MySQL default value based on view

问题描述

基本上，我有一堆基于简单区分符列的视图(例如CREATE VIEW tablename AS SELECT * FROM tablename WHERE discrcolumn ="discriminator value").

Basically I have a bunch of views based on a simple discriminator column (eg. CREATE VIEW tablename AS SELECT * FROM tablename WHERE discrcolumn = "discriminator value").

在此视图中插入新行后，应在"discrcolumn"中插入标识符值".

Upon inserting a new row into this view, it should insert "discriminator value" into discrcolumn.

我尝试了这个，但是显然MySQL本身并没有弄清楚，因为它引发了一个错误视图表名基础表的字段没有默认值".当然，discriminator列设置为NOT NULL.

I tried this, but apparently MySQL doesn't figure this out itself, as it throws an error "Field of view viewname underlying table does not have a default value". The discriminator column is set to NOT NULL of course.

我该如何修补?也许是插入前的触发器?

How do I mend this? Perhaps a pre-insert trigger?

更新:触发器将不适用于视图，请参见下面的评论.

UPDATE: Triggers won't work on views, see below comment.

在使用变量的表上创建触发器，并在建立连接时设置该变量是否可行?对于每个连接，该变量的值都相同，但可能与其他连接不同.

Would it work to create a trigger on the table which uses a variable, and set that variable at establishing the connection? For each connection the value of that variable would be the same, but it could differ from other connections.

这似乎起作用...

设置:

CREATE TRIGGER insert_[tablename] BEFORE INSERT ON [tablename] 
FOR EACH ROW SET NEW.[discrcolumn] = @variable

运行时:

SET @variable = [descrvalue];
INSERT INTO [viewname] ([columnlist]) VALUES ([values]);

推荐答案

我认为您不需要那么复杂的任何东西.如果您创建了一个视图，例如

I don't think you need anything quite so complicated as that. If you created a view such as

CREATE VIEW MYVIEW AS 
    SELECT COLUMN1,
           COLUMN2,
           DISCRIMINATOR_COLUMN
        FROM MYTABLE
        WHERE DISCRIMINATOR_COLUMN = 1;

然后您可以将其插入到该视图中...

you could then insert into this view thus...

INSERT INTO MYVIEW (COLUMN1,
                    COLUMN2,
                    DISCRIMINATOR_COLUMN)
    VALUES (1, 2, 3)

，并且如果视图中不存在的表中所有列均具有适当的默认值，则该视图应正确更新.请注意，DISCRIMINATOR_COLUMN中的新值不必是在视图中选取的值，尽管除非被选中，否则自然不会在视图中出现.

and the view should update correctly if all the columns in the table that aren't present in the view have suitable defaults. Note that the new value in DISCRIMINATOR_COLUMN needn't be the value picked out in the view, although naturally it won't appear in the view when next selected unless it is.

这篇关于基于视图的MySQL默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！