三大数据库的split函数

前言

在执行sql查询传参的时候, 为了防止sql注入, 一般都会使用参数化查询. 而例外的是in, 因为它是多参数, 所以一般都是在后台代码中拼好用逗号分割的字符串以后, 再直接使用字符串传入sql解释器. 这样一来, sql注入便是有可能成功的了, 而且防止sql注入的代码需要在后台完成. 为了解决这个问题, 我们只需要在sql中提供自定义函数拆分字符串即可.

sqlserver

sqlserver应该算是最简单的了, 函数可以返回临时表, 查询起来也够简单.

CREATE FUNCTION [dbo].[f_Split]
    (
        @str NVARCHAR(MAX) ,
        @split_char NVARCHAR(10)
    )
RETURNS @table TABLE ( value NVARCHAR(MAX) )
AS
    BEGIN
        IF @str IS NULL
            OR @split_char IS NULL
            OR LEN(@str) = 0
            OR LEN(@split_char) = 0
            RETURN
        DECLARE @index INT
        SET @index = CHARINDEX(@split_char, @str)
        WHILE @index > 0
            BEGIN
                INSERT    @table
                VALUES    ( LEFT(@str, @index - 1) )
                SET @str = RIGHT(@str, LEN(@str) - @index)
                SET @index = CHARINDEX(@split_char, @str)
            END
        INSERT    @table
        VALUES    ( @str )
        RETURN
    END

-- 使用
SELECT * FROM dbo.f_Split('1,2,3,4',',')

SELECT * FROM dbo.temp t0
WHERE t0.ID IN (
    SELECT value FROM dbo.f_Split('1,2,3,4',',')
)

oracle

oracle也简单, 在使用的时候用table函数调用即可.

CREATE OR REPLACE TYPE f_split_type IS TABLE OF VARCHAR2 (4000)
CREATE OR REPLACE FUNCTION f_split(str VARCHAR2,
                                    split_char VARCHAR2 := ',')
RETURN f_split_type
    PIPELINED IS
    v_i           INTEGER;
    v_str_value   VARCHAR2(500);
    v_str         VARCHAR2(4000) := str;

BEGIN
    LOOP
        v_i := INSTR(v_str, split_char);
        EXIT WHEN v_i = 0;
        v_str_value := SUBSTR(v_str, 1, v_i - 1);
        v_str := SUBSTR(v_str, v_i + 1);
        PIPE ROW(v_str_value);
    END LOOP;
    PIPE ROW(v_str);
    RETURN;

END f_split;

-- 使用
SELECT t.COLUMN_VALUE FROM TABLE(f_split('1,2,3,4,5','2')) t

SELECT * FROM temp t0
WHERE t0.ID IN (
    SELECT COLUMN_VALUE FROM TABLE(f_Split('1,2,3,4',','))
)

mysql

mysql是比较麻烦的了, 不提供临时表可用, 而且函数不能返回结果表. 只能用一种简单的办法, 创建一个单独用作保存结果的表, 然后联合查询. 这里设置的清除数据时间是一小时, 即自动清除一小时之前的临时数据.

-- 临时表
CREATE TABLE `split_temp_table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `mainId` varchar(50) DEFAULT NULL,
    `value` varchar(100) DEFAULT NULL,
    `createTime` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

-- 函数
CREATE FUNCTION `f_split` (str VARCHAR (2000),split_char VARCHAR (10)) RETURNS VARCHAR (50) CHARSET utf8
BEGIN
    DECLARE uid VARCHAR (50) ;
    DECLARE i INT;
    DECLARE now_time DATETIME;

    SET now_time = NOW();

    DELETE FROM `split_temp_table` WHERE createTime < SUBDATE(now_time,INTERVAL 1 HOUR);

    IF (str IS NULL OR LENGTH(str) = 0 OR split_char IS NULL OR LENGTH(split_char) = 0)
    THEN RETURN NULL;
    END IF;

    SET uid = UUID() ;
    SET i = LOCATE(split_char,str);
    WHILE i > 0 DO
            INSERT INTO split_temp_table(mainId, `value`, createTime) VALUES(uid, LEFT(str,i - 1), now_time);
            SET str = RIGHT(str,LENGTH(str) - i);
            SET i = LOCATE(split_char,str);
    END WHILE;
    INSERT INTO split_temp_table(mainId, `value`, createTime) VALUES(uid, str, now_time);
    RETURN uid ;
END $$

-- 使用
SELECT f_split('1,2,3',',') INTO @uid;

SELECT * FROM temp t0
WHERE t0.ID IN (
    SELECT t1.`value` FROM split_temp_table t1
    WHERE t1.`mainId` = @uid
)

这三个函数完成以后, 在查询的时候就可以直接使用参数化传入带逗号分割的字符串了, 不需要考虑sql注入和sql拼接了.