mysql基于另一个表中的列创建表并自动更新 [英] mysql create table based on columns from another table and auto updated

问题描述

我这里可能有错误"的字眼(这是mysql的新功能)，但我希望我已经解释了我要做好的事情.

I could have the wording 'wrong' here (new to mysql) but i hope i've explained what I'm trying to do well.

我有一个名为Submits的表，其中包含4个字段SubmitId，studentName，submitDate，状态

i have a table called submissions with 4 fields submitId, studentName, submitDate, status

• 状态是指他们是否被录取.

• status refers to whether they got admitted or not.

submitId自动增加

submitId is auto incremented

现在，我想基于此创建另一个表，但只有状态为true时，此新表才会具有SubmitId，studentName，submitDate和其他字段.

Now i wanted to create another table based on that, but only if the status is true, this new table would have the submitId, studentName, submitDate, plus additional fields.

• 此表将具有一个新的自动递增的StudentId

我该怎么做，以便它自动将任何新条目更新到第二个表中的第一个表，但不覆盖表2的其他内容.

how would i do that so it automatically updates any new entry to the first table on the second table, but not overwrite the additional content of table 2.

我考虑过使用视图，但是您不能在视图上添加新列，对吧? 我在这里有错误的逻辑还是有什么选择，有人可以指出正确的方向吗，谢谢

i thought of using a view, but u can't add new columns on the view, right? do i have the logic wrong here or what are the options, could someone please point me in the right direction, thanks

推荐答案

您需要做的第一件事是退后一步，从逻辑实体的角度考虑问题.

The first thing you need to do is step back and consider the problem from the perspective of logical entities.

您已经确定了我可以看到的两个实体-学生和提交. 学生"是一个显而易见的实体，您可以选择不存储在数据库中，但是这样做可能更好. 提交"是一个更明显的例子，但不是很明显的是""实际上是什么.我们假设这是某种交易.

You've identified two entities that I can see - student and submission. "Student" is an obvious entity which you may choose NOT to store in your database, but it may be better that you do. "Submission" is a more obvious one, but what is not so obvious is what a "submission" actually is. Let's assume it is some sort of transaction.

您已经提到了第二张表"，但没有明确说明其在解决方案中的作用.我所能推断的最好是，这是针对提交的活动的某种历史线索.如果为true，那么我可以设想如下所示的物理模式:

You've mentioned a "second table" without a clear indication of its role in the solution. The best I could infer is that it is meant to be some sort of historical trail on activity against a submission. If true, then I could envision a physical schema sketched out as follows:

学生表.每个学生一行；包含有关学生的信息(姓名，身份证等).主键可能是一个自动递增的数字.

Student table. One row per student; contains information about a student (name, id, etc.). Primary key would probably be an auto-incremented number.

提交表.每次提交一行包括学生表的外键(引用主键)；有自己的主键，也是一个自动递增的整数.还具有为INSERT和UPDATE定义的触发器. INSERT触发器导致INSERT进入 submission_log 表； UPDATE触发器还会使INSERT插入 submission_log 表.

Submission table. One row per submission; includes a foreign key to the student table (referencing the primary key); has its own primary key, also an auto-incremented integer. Also has triggers defined for INSERT and UPDATE. INSERT trigger causes INSERT into submission_log table; UPDATE trigger also causes INSERT into submission_log table.

Submission_log 表. 提交表中每个事件一行.包括提交的所有字段以及其自己的主键(提交的主键在这里是外键)，并包括一个指示符字段，用于表示是提交的插入还是更新>.

Submission_log table. One row per event against the submission table. Includes all the fields of submission plus its own primary key (submission's primary key is a foreign key here), and includes an indicator field for whether it represents an insert or update on submission.

以上内容的目的不是提供解决方案，甚至不是解决方案的框架，而是让您根据要在解决方案中建模的逻辑实体及其相互之间的关系进行思考.如果您对逻辑模型有一个清晰的了解，那么确定所需的表，它们的作用以及如何使用它们以及如何将它们相互关联将更加容易.

The purpose of the above is not to supply a solution, or even the framework of a solution, but rather to get you to think in terms of the logical entities you want to model in your solution, and their relationships to each other. When you have a clear picture of the logical model, it will be much easier to determine what tables are required, what their roles are, and how they will be used and how they will relate to each other.

这篇关于mysql基于另一个表中的列创建表并自动更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！