php - Sql从字符串中选择数据选择id

Question

类别表

+----+-----------------------+
| id | category_name         |
+----+-----------------------+
|  1 | Buy Book              |
|  2 | Buy other thinks      |
+----+-----------------------+

购买 table

+----+-----------------------+----------+-------------+----------+--------+-------+
| id | identity              | name     | description | per_rate | bought | costs |
+----+-----------------------+----------+-------------+----------+--------+-------+
|  1 | PROJECT[1]CATEGORY[1] | BOOK     | PHP BOOK    | 10       | 50     | 5000  |
|  2 | PROJECT[1]CATEGORY[1] | BOOK     | PHP BOOK    | 10       | 40     | 4000  |
|  3 | PROJECT[2]CATEGORY[1] | BOOK     | JS BOOK     | 2        | 50     | 100   |
+----+-----------------------+----------+-------------+----------+--------+-------+

当我选择此表时，我想从其他表中选择类别名称。

身份:项目[project_id]类别[category_id]

所以有任何方法可以从其他表中选择类别 ID 并选择类别名称

我想要这张 table

+----+---------------+-----------------------+----------+-------------+----------+--------+-------+
| id | category_name | identity              | name     | description | per_rate | bought | costs |
+----+---------------+-----------------------+----------+-------------+----------+--------+-------+
|  1 |  Buy Book     | PROJECT[1]CATEGORY[1] | BOOK     | PHP BOOK    | 10       | 50     | 5000  |
|  2 |  Buy Book     | PROJECT[1]CATEGORY[1] | BOOK     | PHP BOOK    | 10       | 40     | 4000  |
|  3 |  Buy Book     | PROJECT[2]CATEGORY[1] | BOOK     | JS BOOK     | 2        | 50     | 100   |
+----+---------------+-----------------------+----------+-------------+----------+--------+-------+

Answer 1

你的数据结构非常糟糕。 project 和 category 应位于各自的列中，数字正确存储为数字，并且外键关系正确。在 MySQL 中，执行此操作可能需要触发器，但这是值得的。

有时，我们会因为别人的错误决定而陷入困境。您可以使用like做您想做的事情:

select b.*, c.category_name
from buy b join
     category c
     on b.identity like concat('%CATEGORY[', c.id, ']');

但是，您可能应该努力修复损坏的数据结构。