MySql常用函数
1、字符串拼接
- CONCAT(str1,str2,…)
- CONCAT_WS(separator,str1,str2,…)
- GROUP_CONCAT(expr)
CONCAT、CONCAT_WS其实差不多,就是CONCAT_WS能自己设置分隔符,而且这两个字段是拼接同一行的数据。
测试用例:
1.1 CONCAT
SELECT
pb.product_code,
pd.device_num,
CONCAT('https://XXXXX/download/index.html?d=',pb.product_code,'&sn=',pd.device_num) as qrcode_url
FROM
product_device pd
JOIN product_base pb ON pb.id = pd.product_id
输出结果:
2.2 CONCAT_WS
SELECT
pb.product_code,
pd.device_num,
CONCAT_WS(':','https://xxxxxxx/download/index.html?d=',pb.product_code,'&sn=',pd.device_num) as qrcode_url
FROM
product_device pd
JOIN product_base pb ON pb.id = pd.product_id
结果:
2.3 GROUP_CONCAT
将所有结果都拼接到一起;
SELECT
pb.product_code,
pd.device_num,
GROUP_CONCAT('https://xxx/download/index.html?d=',pb.product_code,'&sn=',pd.device_num) as qrcode_url
FROM
product_device pd
JOIN product_base pb ON pb.id = pd.product_id
结果: