mysql - MySQL 中的模仿表( View 或存储函数)

Question

目前我有两个表中定义的用户结构:User (id, name) 和 UserParams (user_id, param, value)因此，当前数据如下所示:

User:
id, name
1, "John"
2, "Bob"

UserParams:
user_id, param, value
1, "Address", "Kilmainham str"
1, "Phone", "+12345678"

我想重构数据库，以便用户将拥有明确的地址、电话等列:

New User structure:
id, name, address, phone
1, "John", "Kilmainham str", "+12345678"
2, "Bob", "Orc's cave", "just speak up"

坏事是，数据库有很多存储过程，很难在单个条纹中进行重构。因此，我想要一个 View 或存储函数来模仿旧的“UserParams”行为。

SELECT * FROM UserParams WHERE user_id=1;
1, "Address", "Kilmainham str"
1, "Phone", "+12345678"

有什么办法可以做到这一点吗？谢谢。

Answer 1

CREATE VIEW UserParams  (user_id, param, value)
AS SELECT  id,  "Address"  , address 
FROM New_User
UNION 
SELECT id, "Phone", phone
FROM New_User