萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 實現 Oracle INSTR 函數完整兼容版:支持位置、出現次數、反向搜索

Oracle INSTR 語法回顧

INSTR(string, substring [, position [, occurrence]])
-- position > 0: 從左向右,起始位置
-- position < 0: 從右向左,起始位置(倒數)
-- occurrence: 第 N 次出現,默認 1

MySQL 內建 INSTR 缺陷

MySQL INSTR(str, substr) 僅支持前兩個參數,**不支持指定起始位置、出現次數、反向搜索**,遷移 Oracle 應用時須自行實現。

自定義函數 func_instr_oracle 完整實現

DELIMITER $$

DROP FUNCTION IF EXISTS `func_instr_oracle`$$

CREATE FUNCTION `func_instr_oracle`(
    f_str      VARCHAR(1000),  -- 源字符串
    f_substr   VARCHAR(100),   -- 子串
    f_str_pos  INT,            -- 起始位置(正:左→右;負:右→左)
    f_count    INT UNSIGNED    -- 第 N 次出現
) RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 0;        -- 位置迭代器
    DECLARE j INT DEFAULT 0;        -- 匹配計數
    DECLARE v_substr_len INT UNSIGNED DEFAULT 0;
    DECLARE v_str_len    INT UNSIGNED DEFAULT 0;

    SET v_str_len    = LENGTH(f_str);
    SET v_substr_len = LENGTH(f_substr);

    IF f_str_pos > 0 THEN                 -- 正向搜索
        SET i = f_str_pos;
        SET j = 0;
        WHILE i <= v_str_len DO
            IF INSTR(LEFT(SUBSTR(f_str, i), v_substr_len), f_substr) > 0 THEN
                SET j = j + 1;
                IF j = f_count THEN RETURN i; END IF;
            END IF;
            SET i = i + 1;
        END WHILE;

    ELSEIF f_str_pos < 0 THEN             -- 反向搜索
        SET i = v_str_len + f_str_pos + 1; -- 負數轉正向索引
        SET j = 0;
        WHILE i <= v_str_len AND i > 0 DO
            IF INSTR(RIGHT(SUBSTR(f_str, 1, i), v_substr_len), f_substr) > 0 THEN
                SET j = j + 1;
                IF j = f_count THEN RETURN i - v_substr_len + 1; END IF;
            END IF;
            SET i = i - 1;
        END WHILE;

    ELSE                                  -- position = 0 返回 0
        RETURN 0;
    END IF;

    RETURN 0;  -- 未找到
END$$

DELIMITER ;

驗證測試用例

調用預期實際說明
func_instr_oracle('This is belong to you, but not to me.', 'to', 1, 1)1616正向第 1 次
func_instr_oracle('This is belong to you, but not to me.', 'to', 1, 2)3232正向第 2 次
func_instr_oracle('This is belong to you, but not to me.', 'belong', -1, 1)99反向第 1 次(從右往左)
func_instr_oracle('This is belong to you, but not to me.', 'belong', -1, 2)00反向第 2 次不存在

替代方案:INSTR2 函數(基於 SUBSTRING_INDEX)

CREATE FUNCTION `INSTR2`(
    v_string    VARCHAR(5000),
    v_delimiter VARCHAR(20),
    pos         INT,
    nth         INT
) RETURNS VARCHAR(5000)
BEGIN
    DECLARE icount INT DEFAULT 0;
    DECLARE len    INT DEFAULT 0;
    DECLARE len1   INT DEFAULT 0;
    DECLARE str1   VARCHAR(5000) DEFAULT '';

    SET len  = LENGTH(v_string);
    SET len1 = LENGTH(v_delimiter);
    SET icount = INSTR(v_string, v_delimiter);

    IF icount = 0 THEN RETURN 0; END IF;

    IF pos IS NULL THEN RETURN icount;
    ELSEIF pos < 0 THEN RETURN LOCATE(v_delimiter, v_string, len + pos - 1);
    ELSEIF pos = 0 THEN RETURN 0;
    ELSEIF pos = 1 THEN
        IF nth IS NULL THEN RETURN icount;
        ELSEIF nth >= 1 THEN
            SET icount = IF(LENGTH(SUBSTRING_INDEX(v_string, v_delimiter, nth)) = 0
                         OR LENGTH(SUBSTRING_INDEX(v_string, v_delimiter, nth)) = len,
                         0,
                         LENGTH(SUBSTRING_INDEX(v_string, v_delimiter, nth)) + 1);
        ELSE RETURN 0; END IF;
    ELSE
        IF nth IS NULL OR nth = 1 THEN RETURN LOCATE(v_delimiter, v_string, pos);
        ELSEIF nth > 1 THEN
            SET str1 = SUBSTRING(v_string, pos);
            SET icount = IF(LENGTH(SUBSTRING_INDEX(str1, v_delimiter, nth)) = 0
                         OR LENGTH(SUBSTRING_INDEX(str1, v_delimiter, nth)) = LENGTH(str1),
                         0,
                         LENGTH(SUBSTRING_INDEX(str1, v_delimiter, nth)));
            IF icount <> 0 THEN SET icount = icount + pos; END IF;
            RETURN icount;
        END IF;
    END IF;
    RETURN 0;
END;

注意:INSTR2 針對分隔符場景優化,通用性不如 func_instr_oracle。

性能優化建議

  • 大字符串/高頻調用時,考慮應用層處理或生成計算列持久化位置
  • 函數標記 DETERMINISTIC 允許優化器緩存結果
  • 避免在 WHERE 子句中對大表調用自定義函數(全表掃描),改用應用層預計算或全文索引
copyright © 萬盛學電腦網 all rights reserved