mysql-如何将自定义表与user和usermeta表双向关联文字出版社

oogrdqng  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(362)

我正在使用wordpress,phpmyadmin,mysql。对于sql和数据库,我也是一个新手,所以请接受我的建议。
我有一个自定义数据表(这是一个用户信息的主表),我想与wp\u用户和wp\u usermeta表双向关联-这意味着如果用户更新了他们的配置文件(用户或usermeta表),那么自定义数据表将自动更新,而无需我手动输入更新的信息。
同样,如果编辑自定义数据表,我希望users和usermeta中的相关信息自动更新。
我试着用一个粗略的图表来描述我脑子里的想法。数据处理示意图
目前的系统很糟糕,很难维护。另一个问题是,有许多sql报表使用当前的自定义数据表。我知道我正在寻找的解决方案很可能意味着创建一个全新的表。没关系。

User
–––––––––––––––––––
| ID  | username  |
|–––––+–––––––––––|
| 376 | John      |
| 377 | Paul      |
| 378 | Ringo     |
–––––––––––––––––––

       Usermeta
–––––––––––––––––––––––––––––––––––––––––––––
| meta_id | user_id | meta_key | meta_value |
|–––––––––+–––––––––+––––––––––+––––––––––––|
| 8       | 1       | job      | Keys       |
| 102     | 2       | job      | Vocals     |
| 203     | 3       | job      | Drums      |
–––––––––––––––––––––––––––––––––––––––––––––

       Custom_Table
––––––––––––––––––––––––––––––––––––––
| ID | username  | job     | user_ID |
|––––+–––––––––––+–––––––––+–––––––––|
| 1  | John      | Keys    | 376     |
| 2  | Paul      | Vocals  | 377     |
| 3  | Ringo     | Drums   | 378     |
––––––––––––––––––––––––––––––––––––––

基本上,这就是我的查询(除了我有更多的usermeta行):

CREATE TABLE custom_table
    SELECT users.username,
    MAX(CASE WHEN meta_key = 'job' THEN meta_value ELSE NULL END) AS job,
    users.ID
    FROM wp_users as users, wp_usermeta as umeta
    WHERE umeta.user_id = users.ID

这将创建我想要的表,但是如果任何表被更新,它不会在其他任何地方“同步”。这可能吗?会不会是某种触发器(我还不熟悉触发器)?

kdfy810k

kdfy810k1#

只需使用视图而不是表。这使您能够始终了解实际数据的最新情况,而无需任何维护成本(而且,无论如何,也无需存储成本)。

create view custom_view as 
select 
    u.username, 
    um.meta_value as job
    u.id as user_id
from wp_users as users u
left join wp_usermeta as umeta um on um.user_id = u.ID and um.meta_key = 'job'

请注意原始查询:
它实际上是无效的sql(它缺少 group by 条款)
如果它有 group by 子句,它将过滤掉没有作业的用户(这可能不是您想要的)
不要用老派,含蓄的加入;始终使用标准联接(与 on 关键字中没有逗号 from 条款)
由于每个用户只对一个元密钥感兴趣,因此可以将查询重写为一个简单的 join ,没有聚合。

b4qexyjb

b4qexyjb2#

您可以使用可更新的 VIEW 要解决此问题:

CREATE VIEW custom AS
  SELECT User.ID, User.username,
    CASE WHEN meta_key = 'job' THEN meta_value ELSE NULL END AS job
  FROM User INNER JOIN Usermeta ON Usermeta.user_id = User.ID

dbfiddle.uk上的演示
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。还有一些其他的构造使得视图不可更新。
如果需要其中一个构造,则只能创建 VIEW 并且必须更新源表本身。视图始终与源表同步。

相关问题