mysql视图字段默认值,基于视图的MySQL默认值

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").

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

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.

EDIT:

This appears to work...

Setup:

CREATE TRIGGER insert_[tablename] BEFORE INSERT ON [tablename]

FOR EACH ROW SET NEW.[discrcolumn] = @variable

Runtime:

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)

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.