如何将我的列合并为一个以在MySQL中创建URI？

I have four columns that I would like to combine into one URL: region_slug, category_slug, listing_slug, and listing_id. Previously, I have been using PHP to do this but this can be repetitive, especially if I ever decide to change the format of the URI. I would like a single alias to return a URI like '{region_slug}/{category_slug}/{listing_slug}/{listing_id}. So I would basically like to do something like <?php echo $result['listing_uri']; ?> How could I do this with my current query:

我有四个列要组合成一个URL：region_slug，category_slug，listing_slug和listing_id。以前，我一直在使用PHP来执行此操作，但这可能是重复的，特别是如果我决定更改URI的格式。我想要一个别名来返回类似'{region_slug} / {category_slug} / {listing_slug} / {listing_id}的URI。所以我基本上喜欢做<？php echo $ result ['listing_uri']; ？>我如何使用当前查询执行此操作：

        SELECT
            l.listing_id, l.slug AS listing_slug, r.region_id, r.slug AS region_slug, c.category_id, c.slug AS category_slug
        FROM listings AS l 
        LEFT JOIN categories AS c ON l.category_id = c.category_id
        LEFT JOIN regions AS r ON l.region_id = r.region_id

1 个解决方案

#1

Use CONCAT_WS, this function allows your to concatenate strings with a separator:

使用CONCAT_WS，此函数允许您使用分隔符连接字符串：

SELECT
    l.listing_id AS listing_id,
    l.slug AS listing_slug, 
    r.region_id, r.slug AS region_slug, 
    c.category_id, c.slug AS category_slug,
    CONCAT_WS('/', region_slug, category_slug, listing_slug, listing_id) AS listing_uri
FROM 
    listings AS l 
LEFT JOIN 
    categories AS c ON l.category_id = c.category_id
LEFT JOIN 
    regions AS r ON l.region_id = r.region_id