In this post I will show you how to use a string array in mysql.

Mysql does not have the type Array in its procedural language. The technique developed here allows you to use the mysql array through a function, and this function can also return a string as Array.

The idea is simple: you must have a separator in the string, which is responsible for separating the data. Thus, the function will act similar to php function explode (), but it will never return an Array type. However, you can work with data inside the function, including being able to call other procedures or functions within the same. You can store data array and sql it, and work with.

Vamos ao código/The Code:

delimiter |

create function str_in_array( split_index varchar(10), arr_str varchar(200), compares varchar(20) )
  returns boolean
  begin
  declare resp boolean default 0;
  declare arr_data varchar(20);

  -- While the string is not empty
  while( length( arr_str ) > 0  ) do

  -- if the split index is in the string
  if( locate( split_index, arr_str ) ) then

      -- get the last data in the string
    set arr_data = ( select substring_index(arr_str, split_index, -1) );

    -- remove the last data in the string
    set arr_str = ( select
      replace(arr_str,
        concat(split_index,
          substring_index(arr_str, split_index, -1)
        )
      ,'')
    );
  --  if the split index is not in the string
  else
    -- get the unique data in the string
    set arr_data = arr_str;
    -- empties the string
    set arr_str = '';
  end if;

  /* HERE YOU DO SOMETHING WITH THE DATA */

  -- in this trivial example, it returns if a string is in the array
  if arr_data = compares then
    set resp = 1;
  end if;

  /* END OF YOUR CODE */
 end while;

return resp;
end
|

delimiter ;

Another example
In this example, we will use a string as array, that will pass some dates, and the function will compare with another date, and checks if there is one date in the array greate than one given.

delimiter |
create function incrementarDado( split_index varchar(10), small_date DATETIME, arr_dates varchar(200) )
  returns boolean
 begin
  declare resp boolean default 0;
  declare current datetime;

  -- while the array is not empty and it dont find a biggest date in the array
  while( length( arr_dates ) > 0 and resp = 0 ) do

    -- if it finds the split_index in the string arr_dates
    if( locate( split_index, arr_dates ) ) then

      -- Set the current date with the value of the last element in the array
      set current = ( select substring_index(arr_dates, split_index, -1) );

      -- removes the last value
      set arr_dates = ( select
                    replace(arr_dates,
                      concat(split_index,
                        substring_index(arr_dates, split_index, -1)
                      )
                    ,'')
                );
    else
      -- if it dont locate de split index

      -- set the current as the unique value in the array
      set current = arr_dates;

      -- empties the array
      set arr_dates = '';
    end if;

    -- Checks if current date is biggest then small_date
    set resp = ( select if( cast( current as DATETIME ) > small_date, 1, 0 ) );

  end while;

    -- returns if it found a date in the array biggest then small_date
   return resp;
end
|

delimiter ;

You can call the function in this way:

select incrementarDado( '|', now(), '2012-02-04|2011-10-08|2013-12-11' );

It’s very usefull!

Here is another posts that have people seeking this functional procedure:

http://www.easywayserver.com/forum/viewtopic.php?t=8
http://forums.devarticles.com/mysql-development-50/arrays-in-mysql-85353.html
http://stackoverflow.com/questions/4650238/split-a-mysql-string-from-group-concat-into-an-array-like-expression-list
http://stackoverflow.com/questions/3405724/how-to-store-data-in-array-in-mysql-function-or-procedure