MySQL split comma-separated string into rows

split-string-into-rows.sql

-- split-string-into-rows.sql
-- Duane Hutchins
-- https://www.github.com/duanehutchins
-- Split a string into a mysql resultset of rows
-- This is designed to work with a comma-separated string (csv, SET, array)

-- Function SET_EXTRACT
-- Essentially does the reverse of MySQL's built-in function FIND_IN_SET(str,strlist) = index INT
-- Splits a comma-separated string (AKA "SET"), $strlist, and returns the element (aka substring) matching the provided index, $i.
-- If index $i is zero or positive, the elements are counted from the left, starting at zero.
-- If index $i is negative, the elements are instead counted from the right, starting at -1.
-- If either parameter is NULL or if $i is outside the element count, NULL will be returned
-- Usage Example: SELECT SET_EXTRACT(2,'foo,bar,foobar'); // "foobar"
CREATE FUNCTION SET_EXTRACT($i SMALLINT UNSIGNED, $strlist BLOB) RETURNS VARBINARY(255)
    DETERMINISTIC NO SQL
    RETURN NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(0b0, ',', $strlist, ",", 0b0), ",", $i+1.5*(SIGN($i+0.5)+1)-1), ',', -SIGN($i+0.5)),0b0)

-- Function SET_COUNT
-- Returns the number of elements in a set
-- (Actually returns the one plus the number of commas in the string)
CREATE FUNCTION SET_COUNT($strlist BLOB) RETURNS SMALLINT UNSIGNED 
    DETERMINISTIC NO SQL
    RETURN 1+CHAR_LENGTH($strlist)-CHAR_LENGTH(REPLACE($strlist,',',''));

-- Table number_set
-- A column of integers counting from 0 to 255
-- This is a handy tool to pivot a table (or mysql result) row of columns into a column of rows
CREATE TABLE IF NOT EXISTS `number_set` (
    `n` TINYINT(3) UNSIGNED NOT NULL PRIMARY KEY,
    UNIQUE KEY `n` (`n`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=BINARY MAX_ROWS=256 MIN_ROWS=256;

-- Insert numbers 0-255 into the number_set table
-- The data in MEMORY tables is lost on server restart, so I recommend adding this to the mysql --init-file
-- https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_init-file
INSERT INTO number_set (n)
    SELECT n1.n+(n2.n<<2)+(n3.n<<4)+(n4.n<<6) AS n FROM
    (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n1,
    (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n2,
    (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n3,
    (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n4;

-- Function split_string_into_rows()
-- Only used as a work-around to pass @split_string_into_rows to the split_string_into_rows VIEW
-- Returns @split_string_into_rows if the argument is NULL
-- Sets @split_string_into_rows if the argument is not NULL
CREATE FUNCTION split_string_into_rows($split_string_into_rows BLOB) RETURNS BLOB 
    DETERMINISTIC NO SQL
    RETURN IF($split_string_into_rows IS NULL, IFNULL(@split_string_into_rows,""), "1"|@split_string_into_rows:=$split_string_into_rows);

-- View split_string_into_rows
-- Splits a comma-delimited string (aka csv aka comma-separated string) into rows
-- Result set contains the index (`i`) and element (`e`)
-- Resultset sorted by index, starting at zero
-- The comma-separated string is passed via @split_string_into_rows
-- Usage Examples: 
--    Two queries: 
--      SET @split_string_into_rows = 'foo,bar,foobar'; SELECT e FROM split_string_into_rows;
--    As a single query: 
--      SELECT e FROM split_string_into_rows WHERE split_string_into_rows('foo,bar,foobar');
--    With a JOIN to another table: 
--      SELECT u.name FROM users JOIN split_string_into_rows s ON u.birth_month = s.e WHERE split_string_into_rows('March,April,May');
--      _ or even better _
--      SELECT STRAIGHT_JOIN u.name FROM split_string_into_rows s, users u WHERE u.birth_month = s.e AND split_string_into_rows('March,April,May');
-- Field indexes are still used when doing a join against a string split!
-- This preforms much faster than FIND_IN_SET() because the indexes are preserved.

-- Limited to 256 results
CREATE OR REPLACE ALGORITHM = MERGE VIEW split_string_into_rows(i,e) AS
    SELECT HIGH_PRIORITY SQL_SMALL_RESULT split_string_into_rows.n AS i, SET_EXTRACT(split_string_into_rows.n,split_string_into_rows(NULL)) AS e
    FROM number_set split_string_into_rows
    WHERE split_string_into_rows.n < SET_COUNT(split_string_into_rows(NULL));
  
-- Limited to 65536 results. This limit is increased with additional joins to number_set
CREATE OR REPLACE ALGORITHM = MERGE VIEW split_string_into_rows(i,e) AS
    SELECT HIGH_PRIORITY STRAIGHT_JOIN split_string_into_rows_1.n+(split_string_into_rows_2.n<<8) AS i, SET_EXTRACT(split_string_into_rows_1.n+(split_string_into_rows_2.n<<8),split_string_into_rows(NULL)) AS e
    FROM number_set split_string_into_rows_1, number_set split_string_into_rows_2
    WHERE split_string_into_rows_1.n < SET_COUNT(split_string_into_rows(NULL))
        AND split_string_into_rows_2.n <= (SET_COUNT(split_string_into_rows(NULL))-1)>>8
        AND split_string_into_rows_1.n+(split_string_into_rows_2.n<<8) < SET_COUNT(split_string_into_rows(NULL));