database - 'bouncing' 表的单个 MySQL 查询选择

Question

因此，为了简单起见，我有一个包含两个字段的主表 - 第一个是属性，第二个是属性值。如果第二个字段设置为引用另一个表中的值，则会在括号中表示。

示例:

                 MASTER_TABLE:Attr_ID |  Attr_Val--------+-----------   1    | 23(table1)   --> 23rd value from `table1`   2    | ...   1    | 42           --> the number 42   1    | 72(table2)   --> 72nd value from `table2`   3    | ...   1    | txt          --> string "txt"   2    | ...   4    | ...                 TABLE 1: Val_Id |  Value--------+-----------   1    | some_content   2    | ...   .    | ...   .    | ...   .    | ...  23    | some_content   .    | ...

Is it possible to perform a single query in SQL (without parsing the results inside the application and requerying the db) that would iterate trough master_table and for the given <attr_id> get only the attributes that reference other tables (e.g. 23(table1), 72(table2), ...), then parse the tables names from the parenthesis (e.g. table1, table2, ...) and perform a query to get the (23rd, 72nd, ...) value (e.g. some_content) from that referenced table?

Here is something I've done, and it parses the Attr_Val for the table name, but I don't know how to assign it to a string and then do a query with that string.

PREPARE pstmt FROM 
    "SELECT * FROM information_schema.tables 
     WHERE TABLESCHEMA = '<my_db_name>' AND TABLE_NAME=?"; 

 SET @str_tablename = 
   (SELECT table.tablename FROM
       (SELECT @string:=(SELECT <string_column> FROM <table> WHERE ID=<attr_id>) as String,
            @loc1:=length(@string)-locate("(", reverse(@string))+2 AS from,
            @loc2:=length(@string)-locate(")", reverse(@string))+1-@loc1 AS to,
            substr(@string,@loc1, @loc2) AS tablename
       ) table
   );  <--this returns 1 rows which is OK

EXECUTE pstmt USING @str_tablename; <--this then returns 0 rows

有什么想法吗？

Answer 1

如果成功的话，我喜欢这种方法的纯粹性。但我认为你正在制造一颗维修炸弹。有了这样的治疗方法，谁还需要生病呢？

没有人曾说过某个网站“伙计，他们的数据确实是纯净的!”他们称赞对数据所做的事情。我不建议你把双手绑在背后。我保证您的竞争对手不会。