是否可以从第二个表条目动态引用 mysql 表条目值? [英] Is it possible to reference a mysql table entry value from a second table entry dynamically?

问题描述

我找不到关于动态引用一个 MySQL 表条目到另一个的任何信息.这可能是不可能的.

I can't find anything about dynamically referencing one MySQL table entry to another. It may not be possible.

基本上，我想知道在 MySQL 中是否可以执行将某个 Excel 单元格的值引用到另一个单元格的等效操作.例如，如果在 Excel 中我将 Sheet 1 Cell A1 设置为某个值，例如MyVal".然后，如果我将 Sheet 2 Cell A1 设置为=Sheet1!A1"并将 Sheet 3 Cell A1 设置为=Sheet2!A1"，则 Sheet 3 Cell A1 的值为MyVal".如果我回到 Sheet 1 Cell A1 并将值更改为MyNewVal"，则 Sheet 2 Cell A1 和 Sheet 3 Cell A1 上的值会自动更新为MyNewVal".

Essentially, I'd like to know if in MySQL you can do the equivalent to referencing the value of a certain Excel cell to another. For example, if in Excel I set Sheet 1 Cell A1 to some value like "MyVal". Then if I set Sheet 2 Cell A1 to "=Sheet1!A1" and Sheet 3 Cell A1 to "=Sheet2!A1" the value of Sheet 3 Cell A1 is "MyVal". If I go back to Sheet 1 Cell A1 and change the value to "MyNewVal" then the value is automatically updated on Sheet 2 Cell A1 and Sheet 3 Cell A1 to "MyNewVal".

我的问题是...在 MySQL 中，我可以将第一个表中某个条目的值设置为动态链接到第二个表中不同条目的值，这样当我查询第一个表时(使用现有的 PHP 代码)我得到了第二个表中的值? 我想如果可能的话，那么第一个表中条目的值可能看起来像一个查询第二个表的正确价值.

My question is... in MySQL can I set the value of a certain entry in the first table to be dynamically linked to the value of a different entry in a second table such that when I query the first table (using existing PHP code) I get the value that's in the second table? I imagine that if it's possible then perhaps the value of the entry in the first table would look like a query that queries the second table for the correct value.

我了解如何在 PHP 中编写 UPDATE 查询以显式使值相同，但我不想更改现有的 php 代码.我想以相对/动态的方式链接它们.简短的原因是我不想更改 PHP 代码，因为在我维护的几个站点上使用了相同的代码，并且我希望保持现有的 php 代码相同以进行更清晰的维护/升级.

I understand how to write an UPDATE query in PHP to explicitly make the values the same but I don't want to change the existing php code. I want to link them in a relative/dynamic way. The short reason is that I don't want to change the PHP code since the same code is used on several of the sites I maintain and I want to keep the existing php code the same for cleaner maintenance/upgrading.

但是，由于各个站点上的数据库已经不同，因此以某种方式动态链接数据库本身不同表中的适当条目会非常干净.

However, since the databases on the various sites are already different, it would be very clean to somehow dynamically link the appropriate entries in the different tables in the database itself.

任何帮助将不胜感激.如果这是可能的，如果你能指出我正确的方向，我很乐意做这项研究.

Any help would be very appreciated. If this is possible, if you could just point me in the right direction, I'd be happy to do the research.

推荐答案

有 2.5 种方法可以做到这一点(基本上是两种，但感觉好像只有三种):

There are 2.5 ways to do this (basically two, but it feels like there's three):

从最简单到最难...

选项 1:

如果你需要tableA来反映tableB的值，根本不要把值存储在tableA中，直接使用tableB的值.使用连接:

If you need tableA to reflect tableB's value, don't store the value in tableA at all, just use tableB's value. Use either a join:

select a.*, b.col1
from tableA a
join tableB b on <some join condition>

或子选择

select *, (select col1 from tableB where <some condition>) col1
from tableA

选项 2:

如果您对选项 1 感到满意，请将其转换为一个视图，其行为类似于一个表(除了对更新连接视图的限制):

If you're happy with option 1, convert it to a view, which behaves like a table (except are restrictions on updating views that are joins):

create view myview as 
select ... (one of the above selects)

选项 3:

创建一个数据库触发器，当 tableB 的值更改时触发，并将值复制到 tableA 中的相应行/列

Create a database trigger that fires when tableB's value is changed and copies the value over to the appropriate row/column in tableA

create trigger tableB_update
after update on tableB
for each row
update tableA set
tablea_col = new.col1
where id = new.tableA_id;

请注意，new 和 old 是为新行和旧行赋予的特殊名称，因此您可以引用正在更新的表中的值.

Note that new and old are special names given to the new and old rows so you can reference the values in the table being updated.

选择最适合您需求的选项.

Choose the option that best suits your needs.

这篇关于是否可以从第二个表条目动态引用 mysql 表条目值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！