MySQL – Splitting fields

[adsense id=”0514458240″ width=”468″ height=”60″]

It should be noted that anyone who needs this type of solution should look closely at their data model and have this corrected.

A question was raised where a field in the table contains a comma separated list of entries. These entries should be accessible. Through a set of queries. Let us call these comma separated values attributes. The data model should consist of a separate table with a unique identifier and the various attributes in question. But for argument sake let us imagine that this is not available and that we need to generate a temporary table consisting of these attribute values.

mysql> create table data (id int auto_increment primary key, attributes varchar(255));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into data(attributes) values ('One,Two,Three,Four'),('a,b,c,d,e'),('Dog,Cat,Horse');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from data;
+----+--------------------+
| id | attributes         |
+----+--------------------+
|  1 | One,Two,Three,Four | 
|  2 | a,b,c,d,e          | 
|  3 | Dog,Cat,Horse      | 
+----+--------------------+
3 rows in set (0.00 sec)

Let us now create a stored procedure which takes each of these records and adds them to a temporary table that we have created.

drop procedure if exists sp_data;
delimiter $$
create procedure sp_data()
begin
	declare done int default 0;
	declare lattributes varchar(255);
	declare lid integer;
	declare idx integer;
	declare cdata cursor for select id, attributes from data;
	declare continue handler for not found set done := 1;
	create temporary table if not exists tdata(id int, attribute varchar(100));
	open cdata;
	read_loop: loop
		fetch cdata into lid, lattributes;
		if done then
			leave read_loop;
		end if;
		attr_loop: loop
			set idx := locate(',', lattributes);
			if idx > 0 then
				set @lattribute := substring(lattributes, 1, idx-1);
			else
				set @lattribute := lattributes;
			end if;
			INSERT INTO tdata(id, attribute) VALUES (lid, @lattribute);
			if idx = 0 then
				leave attr_loop;
			end if;
			set lattributes := trim(substring(lattributes, idx+1));
		end loop;
	end loop;
end;
$$
delimiter ;

As we have a temporary table the results are only visible for the duration of the session. Afterwhich these are removed by the MySQL server.

Calling this function on this table we get:

mysql> select * from tdata;
ERROR 1146 (42S02): Table 'test.tdata' doesn't exist
mysql> call sp_data;
Query OK, 1 row affected (0.00 sec)

mysql> select * from data;
+----+--------------------+
| id | attributes         |
+----+--------------------+
|  1 | One,Two,Three,Four | 
|  2 | a,b,c,d,e          | 
|  3 | Dog,Cat,Horse      | 
+----+--------------------+
3 rows in set (0.00 sec)

mysql> select * from tdata;
+------+-----------+
| id   | attribute |
+------+-----------+
|    1 | One       | 
|    1 | Two       | 
|    1 | Three     | 
|    1 | Four      | 
|    2 | a         | 
|    2 | b         | 
|    2 | c         | 
|    2 | d         | 
|    2 | e         | 
|    3 | Dog       | 
|    3 | Cat       | 
|    3 | Horse     | 
+------+-----------+
12 rows in set (0.00 sec)