INT 上的 MySQL 和正则表达式

Question

此查询应返回 4 行，但返回 1，ID 为 62983。有什么原因吗？

SELECT *
    FROM sims_user_role_maps surm
    JOIN sims_role sr ON surm.role_id = sr.id
    WHERE 
        surm.user_id = 118730
        AND sr.organization_id REGEXP 62978|62981|62982|62983

SP 调用

call schools_remove_for_user('f155ec0e-b9ce-11e1-a1fa-001cc4565d26', '62966|62969|62983') 

SP

CREATE PROCEDURE `schools_remove_for_user`(
    user_id NVARCHAR(255),
    school_ids LONGTEXT
)
BEGIN

DECLARE sims_user_id INT DEFAULT NULL;

SELECT u.sims_id 
INTO sims_user_id 
FROM user u 
WHERE u.id = user_id;

SET SQL_SAFE_UPDATES = 0;

DELETE FROM sims_user_role_maps
WHERE 
        user_id = sims_user_id
    AND role_id IN (
                SELECT role_id 
                FROM(
                    SELECT surm.role_id 
                    FROM sims_user_role_maps surm
                    JOIN sims_role sr ON surm.role_id = sr.id
                    WHERE 
                            surm.user_id = sims_user_id
                        AND sr.organization_id REGEXP school_ids) 
                A);

END

Answer 1

您没有将正则表达式放在引号内，因此 | 的工作方式为 bitwise OR .

正确的语法是AND sr.organization_id REGEXP '62978|62981|62982|62983'。您还可以考虑 sr.organization_id IN (x, y, z) 如果您只是尝试匹配特定整数，这会更好。