Used by
MySql 5.7 - x64

There is a table

CREATE TABLE `tst_005_tbl_00` ( `id` INT(11) NULL DEFAULT NULL, `pl_1` VARCHAR(1000) NULL DEFAULT NULL, `pl_1_sprch` ENUM('pl_1_1','pl_1_2','pl_1_3','pl_1_4','pl_1_5','pl_1_6') NULL DEFAULT NULL ) COLLATE='utf8_general_ci' ENGINE=InnoDB ;

Scenario
1. In the "pl_1_sprch" field , select a value from the drop-down list;
2. In the "pl_1" field , the trigger adds new values ​​to existing values ​​separated by commas.

Question
1. Is it possible to implement this scenario?
2. If yes, then how should the solution approximately look like?
3. What other ways can this problem be solved?
4. How to make the data in the field where the tags are collected are not repeated?

    1 answer 1

    It is possible (although in question because of enum), but not necessary. Working with data separated by commas in relational databases is extremely inconvenient. Data stored in this form causes a lot of problems. In relational databases, in such cases they create a separate table in which the id records from the main table and a separate value from the list contain several rows. After such a normalization, everything immediately falls into place, enum starts working as it is prescribed for it - to keep one value out of several.

    In almost all cases when it is necessary to store multiple values, the implementation of working with such a table on the client is quite simple. This can be a list with checkmarks, for all selected items just give the insert. Or, for example, tags to articles (as on stackoverflow), there a person clearly adds in turn values ​​(or deletes them) we need to turn his actions into insert or delete.

    By the way, if the list of possible values ​​is extensible, i.e. can grow in the future, instead of enum it is better to use a separate table - a directory of possible values. Then the full structure will look something like this:

     Таблица теги: ID-тега Название Ваша таблица tst_005_tbl_00 ID другие поля Связующая таблица "Теги статьи" ID-tbl_00 ID-тега primary key (ID-tbl_00, ID-тега), foreign key ID-tbl_00 references tst_005_tbl_00(ID), foreign key ID-тега references Теги(ID-тега)
    • So far I have done as I understand: - a reference book of tags; - Table of Articles (articles will be assigned tags); table magazine tags for articles; trigger_update (when updating a tag, the article id and the tag are logged.) 1. Did I understand your proposal correctly? See codeshare.io/GApzdw . 2. How to make the trigger check the label for uniqueness? Those. if there is already a label in the journal for an article, then it is not recorded in the journal ... Or an error message is simply popped - koverflow
    • @koverflow Something I did not understand at all, why do you need a trigger, why do you put a label from the application in the field of the table of articles. How are you going to make multiple tags? for good, your application should not give an update to the articles but immediately write to the table of the connection of tags with articles. And your tables are somehow strange. In a certain directory of tags there is no unique key and no ID at all. And id tags appear in some kind of magazine, where tags are linked to articles and therefore cannot be unique - Mike