Explode (MySQL)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

Regelmatig moet ik records splitsen, omdat er meerdere waardes in één veld zitten. In PHP heb je hier explode voor, en in Perl heb je split. In MySQL moet je het zelf doen. Welnu!

Implementaties

explode_01 (jan. 2016)

CREATE DEFINER=`root`@`localhost` PROCEDURE `explode_01`
(
    in tblName tinytext,
    in fldName tinytext,
    in regex tinytext,
    in scheidingsteken tinytext
)
BEGIN

# Split a string according to a separator symbol, and store either part in separate records
# in this same table
#
# * Orignally developed: Jan. 2016
# * When invoked, this routine splits only once. So, when there is a long string with maybe
#  tens or hundreds of splits to be made, this function would need to be invoked numerous
#  times, preferably iteratively. That might not be very efficient
# * Tried this sproc again in March 2018, but it didn't work + seems quite crude → Find something else
#
set @ddl1="drop table if exists tbl_tmp1";
set @ddl2="drop table if exists tbl_tmp2";
set @ddl3=concat("create table tbl_tmp1 select * from ",tblName," where ", fldName," regexp '",regex,"'");
set @ddl4=concat("create table tbl_tmp2 select * from ",tblName," where ",fldName," regexp '",regex,"'");
set @ddl5=concat("delete from ",tblName," where ",fldName," regexp '",regex,"'");
set @ddl6=concat("update tbl_tmp1 set ",fldName,"=substr(",fldName,",1,position('", scheidingsteken,"' in ", fldName,")-1)");
set @ddl7=concat(
    "update tbl_tmp2 set ",fldName,"=substr(",fldName,",position('",scheidingsteken,
    "' in ",fldName,")+length('",scheidingsteken,"'),length(",fldName,"))");
set @ddl8=concat("insert into ",tblName," select * from tbl_tmp1"); 
set @ddl9=concat("insert into ",tblName," select * from tbl_tmp2");

prepare stmt1 from @ddl1; execute stmt1; deallocate prepare stmt1;
prepare stmt2 from @ddl2; execute stmt2; deallocate prepare stmt2;
prepare stmt3 from @ddl3; execute stmt3; deallocate prepare stmt3;
prepare stmt4 from @ddl4; execute stmt4; deallocate prepare stmt4;
prepare stmt5 from @ddl5; execute stmt5; deallocate prepare stmt5;
prepare stmt6 from @ddl6; execute stmt6; deallocate prepare stmt6;
prepare stmt7 from @ddl7; execute stmt7; deallocate prepare stmt7;
prepare stmt8 from @ddl8; execute stmt8; deallocate prepare stmt8;
prepare stmt9 from @ddl9; execute stmt9; deallocate prepare stmt9;

END

explode_02 (Maart 2018)

CREATE DEFINER=`root`@`localhost` PROCEDURE `explode_02`(
	tablename VARCHAR(20),
    id_column VARCHAR(20), 
    value_column VARCHAR(20), 
    delim CHAR(1)
)
BEGIN

# Duplicate a record with one field split over both records according to a given separator
#
# * Source: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows
# * March 2018
# * Tried it with up to 100.000 fields in the field-to-be-split. That didn't work, for it uses
#   VARCHAR fields, which go to max. 65.5365 bytes. Changing to TEXT gave errors
#   → Find another solution
#
DECLARE id INT DEFAULT 0;
DECLARE value VARCHAR(255);
DECLARE occurrences INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM
	tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value varchar(65500)) ENGINE=Memory SELECT ',
	id_column,' id, ', value_column,' value FROM ',tablename);
PREPARE stmt FROM @expr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DROP TEMPORARY TABLE IF EXISTS tmp_table2;
CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;

OPEN cur;
  read_loop: LOOP
	FETCH cur INTO id, value;
	IF done THEN
	  LEAVE read_loop;
	END IF;

	SET occurrences = (SELECT CHAR_LENGTH(value) -
					   CHAR_LENGTH(REPLACE(value, delim, '')) + 1);
	SET i=1;
	WHILE i <= occurrences DO
	  SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(
		  SUBSTRING_INDEX(value, delim, i), delim, -1)));
	  INSERT INTO tmp_table2 VALUES (id, splitted_value);
	  SET i = i + 1;
	END WHILE;
  END LOOP;

  SELECT * FROM tmp_table2;
CLOSE cur;
DROP TEMPORARY TABLE tmp_table1;
  
END

explode_03 (maart 2018)

Details:

  • Keyword select kun je niet weglaten
  • Er zijn haakjes om het argument, maar niet om de vergelijking
  • Ik kan de tussentabel tmp1 niet weglaten: Er lijkt geen manier te bestaan om in één actie een complete tabel te vervangen door nieuwe inhoud, gebaseerd op diezelfde tabel.
CREATE DEFINER=`root`@`localhost` PROCEDURE `explode_03`()
BEGIN

# Data is available in table "tmp"
#
while (select count(*) from tmp where tmp.applications like "%,%")>0 do

drop table if exists tmp1;
create table tmp1
	(select * from tmp where applications not like "%,%")
	union
	(select sku,substring_index(applications,",",1) from tmp where applications like "%,%")
	union
	(select sku,right(applications,length(applications)-length(substring_index(applications,",",1))-1) from tmp where applications like "%,%");

drop table tmp; 
create table tmp select * from tmp1;

# select * from tmp;

end while;    

END

Achterliggende principes & functionaliteiten

Tyische casus

Een tabel zoals 

sku applications
--- -------------------------------------------------------------------
001 hond,kat,herder,siamees,bruine herder,zwarte herder,rui,herder teef

wil ik omfietsen naar 

sku applications
--- -------------------------------------------------------------------
001 hond
001 kat
001 herder
001 siamees
001 bruine herder
001 zwarte herder
001 rui
001 herder teef

en soms naar 

sku applications
--- ----------------------------------------------
001 hond kat herder siamees bruine zwarte rui teef

Test-code: 

drop table if exists tmp;
create table tmp (sku tinytext, applications text);
insert into tmp values ("001","hond,kat,herder,siamees,bruine herder,zwarte herder,rui,herder teef");
insert into tmp values ("002","hond,cavia,hazewind,rui,teef,mannetje,vrouwtje");
select * from tmp;

Merk op dat het applications-veld van het type TEXT is: Het gaat om data die te groot is voor VARCHAR().

Het zijn twee afzonderlijke functionaliteiten

  • De eerste functie splitst het non-genormaliseerde veld
  • De tweede functie dupliceert het record en verdeelt het gesplitste veld over de twee records.

Echter: SQL is een set-gebaseerde taal, en de uitdaging is om daar gebruik van te maken. Om het te scheiden in deze twee afzonderlijke functionaliteiten, lijkt daar moeilijk bij aan te sluiten.

Altijd een tabel met minimaal twee velden

Zoals uit de typische casus hierboven blijkt: Er zijn altijd minimaal twee kolommen. Eentje met een sleutel, en eentje met de data die bewerkt moet worden.

Implicieter, zonder tijdelijke tabellen

  • Gebruik een update-query inclusief replace-functie om de tabel in één commando uit te breiden met afgesplitste records
  • Gebruik een afzonderlijke query om de oorspronkelijke record te ontdoen van de afgesplitste records.

Toegegeven, nu heb je geen tijdelijke tabellen, maar de prijs die je daarvoor betaalt:

  • Ingewikkelde code, waarschijnlijk met een subquery hier of daar
  • De tweede stap is tricky: Omdat de oorspronkelijke set is veranderd, is er een risico dat de selectie niet klopt
  • Het oorspronkelijke selectiecriterium heb je twee keer nodig, mogelijk met lichte aanpassingen. Ik wil dat graag beperken tot één keer, om dat gemakkelijk dynamisch te kunnen maken.

Hoe doen anderen dit?

  • Paulraj gebruikt in Stack Overflow vraag 1673238 SUBSTRING_INDEX om substrings te herkennen en te tellen voor gebruik in een niet-iteratieve oplossing. Plus REPLACE en CONCAT
  • Shylent suggereert in dezelfde post om de substrings in een aparte tabel onder te brengen. Op zich niet gek bedacht, maar niet wat ik zoek
  • Luv gebruikt in Stack Overflow vraag 17942508 een aparte set-gebaseerde explode-functie en een bijbehorende iteratieve functie om de nieuwe tabel te vullen. Net als Paulraj gebruikt hij statements met SUBSTRING en SUBSTRING_INDEX: REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1), delim, );
  • fthiella gebruikt hier een dubbele SUBSTRING_INDEX-functie en een join met CHAR_LENGTH als argument en een subquery met een rij cijfers. Die wordt hieronder nader bekeken.

Oplossing fthiella

Indrukwekkend voorbeeld van niet-iteratief splitsen van een string: 

SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
FROM
  (SELECT 1 n UNION ALL SELECT 2
   UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
  ON CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
  id, n

Getallenreeks

Direct na FROM is een subquery (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers. Deze genereert een set genaamd numbers met kolom n: 

+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

Het aantal van 4 is het maximale aantal substrings dat verwacht wordt.

Aantal substrings per record

CHAR_LENGTH(tablename.name)-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))

Retourneert het aantal substituties voor een gegeven waarde van name. Het commando replace vervangt namelijk alle instanties van de substring in het eerste argument: 

select replace("wie weet waar willem wever woont","w","");
ie eet aar illem ever oont

select char_length("wie weet waar willem wever woont")-char_length(replace("wie weet waar willem wever woont","w",""));
6

Join & impliciete loop

De join tussen numbers en tablename werkt als een impliciete loop. Per waarde van numbers worden alle records van tablename geselecteerd die één of meer keer dat aantal substrings bevat (plu1/min 1 of zoiets). Op die manier krijg je een 1-op-veel-relatie

Uitgebreider voorbeeld

Tabellen & waardes: 

CREATE TABLE tablename (
  id INT,
  name VARCHAR(20));

INSERT INTO tablename VALUES
(1, 'a,b,c'),
(2, 'd');

CREATE TABLE numbers (
  n INT PRIMARY KEY);

INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6);

Select-query: 

SELECT
  numbers.n,
  tablename.id,
  tablename.name orgname,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name,
  CHAR_LENGTH(tablename.name),
  CHAR_LENGTH(REPLACE(tablename.name, ',', '')),
  CHAR_LENGTH(tablename.name)-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))
FROM
  numbers INNER JOIN tablename
  ON CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
  n

Output: 

n 	id 	name 	name 	CHAR_LENGTH(tablename.name) 	CHAR_LENGTH(REPLACE(tablename.name, ',', '')) 	CHAR_LENGTH(tablename.name) CHAR_LENGTH(REPLACE(tablename.name, ',', ''))
1 	2 	d 	d 	1 	1 	0
1 	1 	a,b,c 	a 	5 	3 	2
2 	1 	a,b,c 	b 	5 	3 	2
3 	1 	a,b,c 	c 	5 	3 	2

Casus maart 2018: Honderden splits

Dit betreft een tabel met data zoals: 

sku applications
--- -------------------------------------------------------------------
001 hond,kat,herder,siamees,bruine herder,zwarte herder,rui,herder teef

waarbij de kolom apllications tot 100.000 tekens bevat en vermoedelijk honderden splits. De reden om dit te splitsen: Dit veld is input voor een Amazon keyword-veld. Amazon kent geen scheidingsvelden, maar combineert zelf. Daarom is het handig om dubbele termen uit te filteren. Hele casus:

  • Splits kolom applications
  • Filter dubbele entries uit
  • Samenvoegen in een veld.

Wat ik eigenlijk wil

Oude situatie: 

sku applications
--- -------------------------------------------------------------------
001 hond,kat,herder,siamees,bruine herder,zwarte herder,rui,herder teef

Nieuwe situatie: 

sku applications
--- ----------------------------------------------
001 hond kat herder siamees bruine zwarte rui teef

Zie ook

Bronnen

Cursors en andere frivoliteiten

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Explode_(MySQL)&oldid=11881"