mysql - 通过索引获取列名并在mysql查询中使用

Question

我需要这样的查询:

SELECT * FROM table WHERE owner_field = 100

但是“owner_field”在几个表中是不一样的。我想使用索引来标识字段:

SELECT * FROM table WHERE (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME=table AND INDEX_NAME='owner') = 100

我做错了什么？

UPDATE:我想将此条件添加到 INSERT、UPDATE、DELETE 查询中以进行安全控制(用户只能对自己的数据执行任何操作)。

Mysql结构如下:

user_id     
post_id        post_author    = user_id
category_id    category_owner = user_id

Answer 1

这是糟糕的设计，而且很可能是不可能的。

为什么不使用 VIEW ？

CREATE VIEW table1_ownerfield AS SELECT ownerfield_1 FROM table1;
CREATE VIEW table2_ownerfield AS SELECT ownerfield_2 FROM table2;

然后将它们用作表格:

SELECT * FROM table1_ownerfield;
SELECT * FROM table2_ownerfield;

对于问题的第二部分，请查看使用 triggers .

编辑:

听起来您想要的 View 是这样的:

CREATE VIEW my_posts AS 
    SELECT * FROM posts WHERE post_owner = CURRENT_USER() 
    WITH CHECK OPTION;
CREATE VIEW my_categories AS 
    SELECT * FROM categories WHERE category_owner = CURRENT_USER() 
    WITH CHECK OPTION;

你可能想做这样的事情:

创建表:

 CREATE TABLE user_connections (
     user_connection_id INT AUTO_INCREMENT PRIMARY KEY,
     mysql_connection_id INT,
     user_id INT
 );

然后当用户登录时，使用函数 CONNECTION_ID() 将 mysql 连接 ID 填充到此表中.

然后您可以创建如下 View :

CREATE VIEW my_posts AS 
    SELECT * FROM posts p JOIN user_connections uc 
    WHERE uc.mysql_connection_id = CONNECTION_ID() 
    AND p.post_user_id = uc.user_id 
    WITH CHECK OPTION;
CREATE VIEW my_categories AS 
    SELECT * FROM categories c JOIN user_connections uc 
    WHERE uc.mysql_connection_id = CONNECTION_ID() 
    AND c.category_user_id = uc.user_id 
    WITH CHECK OPTION;