Mysql：在select中设置变量

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:

我想只选择aliases.contact字段中具有subscriber.username的匹配行。这是我的第一次尝试,但它没有返回任何东西:

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

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?

这甚至是可能的,还是应该将逻辑移到应用程序中?

1 个解决方案

#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:

在我的博客中看到这篇文章:

Article-aware title filtering: internationalization: how to JOIN efficiently on a LIKE condition

文章识别标题过滤:国际化:如何在LIKE条件下有效加入

#1

SELECT  aliases.username AS phone_number,
        subscriber.username AS user_name
FROM    aliases, subscriber
WHERE   aliases.contact REGEXP CONCAT('^sip:', subscriber.user_name, '[.*]')