MySQL:如何修剪表列中的前缀关键字？

Question

我使用关键字 m 表示来自 member 的帖子，而 u 来自 user，

所以帖子 owner id 会有这样的东西，m-1, m-2, u-1 等

m-1 是成员(member)id 1

m-2 是成员(member)id 2

u-1 是用户id 1

这是表格帖子，

needle_id   needle_title    needle_content  needle_published    owner_id    pg_id   
    1       a               xxx status      1                   m-1         39      
    2       b               xxx status      1                   m-2         39  
    3       c               xxx status      1                   m-3         39  
    4       d               xxx status      1                   u-1         39  
    5       e               xxx status      1                   m-1         39

和表成员，

mem_id  mem_screenname      mem_email           mem_key     cat_id
1       Member one          a@sample.com        xxx         1
2       Member two          b@sample.com        xxx         1
3       Member three        c@sample.com        xxx         1

这是我的查询，

SELECT
n.needle_id as needleID,
n.needle_title as needleTitle,
n.needle_content as needleContent,
n.needle_published as needleApproved,
n.mem_id as needleOwnerID,
n.needle_created as needleCreated,

m.mem_screenname as needleOwnerScreenname,
m.mem_email as needleOwnerEmail,
m.mem_key as needleOwnerKey,
m.cat_name as needleOwnerType

FROM root_string_needles AS n

    LEFT JOIN 
    (
        SELECT 
        mm.mem_id,
        mm.mem_screenname,
        mm.mem_email,
        mm.mem_key,
        ccmm.cat_name

        FROM root_members_cfm  AS mm

        LEFT JOIN root_category_members AS ccmm
        ON ccmm.cat_id = mm.cat_id
    ) AS m 
    ON m.mem_id = n.owner_id 

WHERE n.pg_id = '39'

AND m.mem_screenname IS NOT NULL
AND n.needle_published != '0'

ORDER BY n.needle_created DESC

我被困在 ON m.mem_id = n.owner_id 因为我使用 prefix 作为 owner id 而他们不会匹配。

那么如何修剪owner id列中的前缀，使mem id列可以匹配owner id？

Answer 1

@Mikael 的答案是正确的。要实际修剪数据，您可以使用 MySQL mid 函数:

MID(owner_id,3,2)

您也可以找到破折号(-)，然后提取破折号后的所有数据。