Mysql: setting a variable within the select

Mysql: setting a variable within the select

陌若浮生 发布于 2021-11-29 字数 712 浏览 837 回复 1 原文

Given my two db tables aliases and subscriber have entries like this:

aliases.username    = '5551234567'  
aliases.contact     = 'sip:a_sip_username@sip.domain.com'  
subscriber.username = 'a_sip_username'  

I'd like to select only the matching rows that have subscriber.username within the aliases.contact field. This was my first attempt but it doesn't return anything:


SELECT
aliases.username as phone_number,
(@B:=subscriber.username) as user_name
FROM aliases,subscriber
WHERE aliases.contact regexp "^sip:@B[.*]"

Is this even possible or should I move the logic to the application?

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

剩余の解释 2022-06-07 1 楼
SELECT  aliases.username AS phone_number,
        subscriber.username AS user_name
FROM    aliases, subscriber
WHERE   aliases.contact REGEXP CONCAT('^sip:', subscriber.user_name, '[.*]')

Note that the following query will be more efficient:

SELECT  aliases.username AS phone_number,
        subscriber.username AS user_name
FROM    aliases, subscriber
WHERE   aliases.contact LIKE CONCAT('sip:', subscriber.user_name, '%')

, and this one, though seems complex, is even more efficient:

CREATE FUNCTION fn_get_next_subscriber(initial VARCHAR(200)) RETURNS VARCHAR(200)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _username VARCHAR(200);
        DECLARE EXIT HANDLER FOR NOT FOUND RETURN UNHEX('FFFF');
        SELECT  username
        INTO    _username
        FROM    subscribers
        WHERE   username>= initial
                AND username NOT LIKE CONCAT(initial, '%')
        ORDER BY
                username
        LIMIT 1;
        RETURN _username;
END

SELECT  a.username AS phone_number,
        s.username AS user_name
FROM    (
        SELECT  si.*, CONCAT('sip:', username) AS fromcontact
        FROM    subscriber si
        ) s, aliases a
WHERE   a.contact >= fromcontact
        AND a.contact < fn_get_next_subscriber(fromcontact)

This will use an index on aliases (contact) and avoid full table scan.

See this article in my blog: