mysql - 如何将SQL查询转换成MYSQL查询？

Question

我已经在 tsql 中开发了一个拆分字符串的函数，但是 mysql 没有一些内置函数。我需要在 MYSQL 中运行，因为我是 mysql 的新手。函数应该接受 2 个参数 1.待拆分的字符串 2. 分隔符('，'或其他)请回复我。

Answer 1

我在互联网上找到了解决方案，您可以解决这个问题。

DELIMITER //
DROP FUNCTION IF EXISTS `splitAndTranslate` //
CREATE FUNCTION splitAndTranslate(str TEXT, delim VARCHAR(124))
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 0;    -- total number of delimiters
    DECLARE ctr INT DEFAULT 0;  -- counter for the loop
    DECLARE str_len INT;        -- string length,self explanatory
    DECLARE out_str text DEFAULT '';    -- return string holder
    DECLARE temp_str text DEFAULT '';   -- temporary string holder
    DECLARE temp_val VARCHAR(255) DEFAULT '';   -- temporary string holder for query

    -- get length
    SET str_len=LENGTH(str);    

    SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, '')))/LENGTH(delim) + 1;    
        -- get total number delimeters and add 1
        -- add 1 since total separated values are 1 more than the number of delimiters

    -- start of while loop
    WHILE(ctr<i) DO
        -- add 1 to the counter, which will also be used to get the value of the string
        SET ctr=ctr+1; 

        -- get value separated by delimiter using ctr as the index
        SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr - 1)) + 1), delim, '');

        -- query real value and insert into temporary value holder, temp_str contains the exploded ID           
        SELECT <real_value_column> INTO temp_val FROM <my_table> WHERE <table_id>=temp_str;

        -- concat real value into output string separated by delimiter
            SET out_str=CONCAT(out_str, temp_val, ',');
    END WHILE;
    -- end of while loop

    -- trim delimiter from end of string
    SET out_str=TRIM(TRAILING delim FROM out_str);
    RETURN(out_str);    -- return 

END//

引用 http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter-separated-string-ids/