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!

Mijn Huidige Imperfecte Oplossing

Dit is mijn huidige oplossing: 

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

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;

Ik geloof dat-ie steeds een deel afsplitst van het betreffende veld, en dat als apart record toevoegt.

Functionaliteiten nader bekeken

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.

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

Zie ook

Bronnen

Cursors en andere frivoliteiten

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