连接两个表但连接键位于查询字符串中

I want to join these two table but the join key of the second table is in a query string,

我想加入这两个表,但第二个表的连接键是在查询字符串中,

page table,

page_id     url
1           a
2           c
3           d

system table,

system_id       query
1               page_id=1&content=on&image=on
2               type=post&page_id=2&content=on

as you can see that page_id is part of the query string in system table.

正如您所看到的,page_id是系统表中查询字符串的一部分。

so how can I join them like the standard joining table method below?

那么如何加入它们就像下面的标准连接表方法一样?

SELECT*
FROM page AS p

LEFT JOIN system AS s
ON p.page_id = s.page_id

EDIT:

I def can change the system table into something like this,

我可以将系统表改成这样的东西,

system_id       page_id    query
1               1           page_id=1&content=on&image=on
2               2           type=post&page_id=2&content=on
3               NULL        type=page

But the reason why I don't want to do this is that the page_id is no need for many certain records. I don't want make a column with too many null.

但我不想这样做的原因是page_id不需要很多特定的记录。我不希望创建一个包含太多null的列。

3 个解决方案

#1

I guess you wanted something like this (MSSQL!):

我想你想要这样的东西(MSSQL!):

---

DECLARE @query VARCHAR(50)
DECLARE @Lenght INT  
DECLARE @PageID INT 

SET @query = '4kkhknmnkpage_id=231&content=on&image=on'
SET @Lenght = PATINDEX('%&%', substring(@query,PATINDEX('%page_id=%', @query),50)) - 9
SET @PageID = CAST(SUBSTRING(@query,PATINDEX('%page_id=%', @query) + 8,@Lenght) AS INT)

SELECT @PageID -- you can do as you please now :)

---

OR:

SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTRING(query,PATINDEX('%page_id=%', query) + 8,(PATINDEX('%&%', substring(query,PATINDEX('%page_id=%', query),50)) - 9)) AS INT) AS page_id
                FROM system) AS s
ON p.page_id = s.page_id 

-- Do as you please again :) 

---

I guess what you really wanted was something like this (MYSQL!):

我猜你真正想要的是这样的东西(MYSQL!):

---

SET @query := '4kkhknmnkpage_id=231&content=on&image=on';
SET @Lenght := POSITION('&' IN (SUBSTR(@query,POSITION('page_id=' IN @query),50))) - 9;
SET @PageID := CAST(SUBSTR(@query,POSITION('page_id=' IN @query) + 8,@Lenght) AS  SIGNED );

SELECT @PageID

---

OR

---

SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTR(query,POSITION('page_id=' IN query) + 8,(POSITION('&' IN (SUBSTR(query,POSITION('page_id=' IN query),50))) - 9)) AS  SIGNED) AS pageID
           FROM system) AS s
ON p.page_id = s.pageID 

#2

I would definitely create columns for page_id,content, image and type (and get them indexed). Then the database would be much lighter and would work much faster.

我肯定会为page_id,content,image和type创建列(并将它们编入索引)。然后数据库会更轻,工作速度更快。

#3

Joining two tables without the common field and data type, is fundamentally wrong IMO.

I will suggest that you extract the page_id and insert it in the database and use a normal join to accomplish what you are searching for.

我建议您解压缩page_id并将其插入数据库并使用普通连接来完成您要搜索的内容。

SO making the columns like

所以使列像

+------------+-----------+---------+
| system_id  |  page_id  |  query  |
------------------------------------

Here is a snippet with which you are extract the page_id.

这是一个片段,您可以使用它来提取page_id。

$query = 'page_id=1&content=on&image=on';
$queryParts = explode('&', $query);

$params = array();
foreach ($queryParts as $param) {
    $item = explode('=', $param);
    $params[$item[0]] = $item[1];
} 
$page_id = $parems['page_id'];

Then you can go on with the insert and use simple join statement to solve your problem in a proper way.

然后,您可以继续使用插入并使用简单的连接语句以正确的方式解决您的问题。

---

Update:

Since you are able to change the schema to a feasible one. You dont need to worry about some rows having empty rows on this.

因为您可以将架构更改为可行架构。您不必担心某些行上有空行。