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

Question

我想将四列合并到一个 URL 中:region_slug、category_slug、listing_slug 和 listing_id。以前，我一直使用 PHP 来执行此操作，但这可能是重复的，尤其是当我决定更改 URI 的格式时。我想要一个单一的别名来返回一个 URI，比如'{region_slug}/{category_slug}/{listing_slug}/{listing_id}。所以我基本上想做类似 <?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

Answer 1

使用 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