两个表，一个是tag_t，其中包含tagName，另一个是book_t。 当我将新标签添加到tag_t时，我希望在book_t中添加新列

Question

Database scheme:

If i create a new tag then book_table create a new column automatically.
Is it possible?

Answer 1

Create a relationship table instead.

CREATE TABLE book_tag_t
( book_id   INT UNSIGNED NOT NULL
, tag_id    INT UNSIGNED NOT NULL
, PRIMARY KEY (book_id,tag_id)
, CONSTRAINT book_tag_t_book FOREIGN KEY (book_id) REFERENCES book_t (id)
     ON DELETE CASCADE ON UPDATE CASCADE
, CONSTRAINT book_tag_t_tag FOREIGN KEY (tag_id) REFERENCES tag_t (id)
     ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

To relate a `tag\ to a \book`

INSERT INTO book_tag_t (book_id, tag_id) VALUES ( 2, 14 );

To get all of the books related to a specific tag:

SELECT b.id
  FROM book_t b
  JOIN book_tag_t r
    ON r.book_id = b.id
  JOIN tag_t t
    ON t.id = r.tag_id
 WHERE t.tagName = 'Science' 
 ORDER BY b.id

---

To address the question you asked, "Is it possible?"

You could probably get something working. That will require a DDL ALTER TABLE statement to be executed on book_table, and that's going to be expensive in terms of concurrency (exclusive locks), and resources (creating a new copy of the table). And you can't do an ALTER TABLE in a trigger. If you actually need functionality like this, the required SQL would be better executed from the application, not from something "automatic" in the database.

What are you going to do when the number of rows in `tag_t` exceeds the number of columns allowed in a table? When a row is deleted? or updated?

It's a horrible idea.

The normal relational approach is to add a third table, a relationship table, to resolve the many-to-many relationship between `book` and `tag`. As illustrated in the first part of this answer.