Stored procedures czasem sie przydają

Kwiecień 6th, 2009 by Arek Kuryłowicz | Posted in Developer, MySQL |

Podczas konwersji bazy danych serwisu Poema napotkałem pewien problem. Otóż dane dotyczące przypisania użytkownika do grup dostępu nie były zapisywane w osobnej tabeli jako indywidualne rekordy, lecz w rekordzie użytkownika, w pojedynczym polu o nazwie GID,  jako identyfikatory grup oddzielanych przecinkami, np. '1,3,19,3' lub pojedyncze wartości '8'. Z oczywistych względów dane takie są mało użyteczne. Zatem trzeba je przenieść do osobnej tabeli i powkładać w indywidualne rekordy. Na pierwszy rzut oka MySQL sobie nie poradzi. No to skrypt PHP, ale... leniwy jestem - nie chce mi się pisać skryptu, może zatem jednak MySQL. Rozwiązaniem jest napisanie własnej funkcji. Wygląda ona mniej więcej tak:

delimiter //

CREATE FUNCTION create_records_in_poema_uid_group(p_id INT)
RETURNS INT
BEGIN
  DECLARE gid_set VARCHAR(255) DEFAULT '';
  DECLARE gid INT;
  DECLARE pos INT;
 
  SET @done = 0;
  SET @num = 0;
 
  -- Pobieramy wartość pola do zmiennej gid_set, pole może wyglądać
  -- '1,5,12,16' lub być pojedyncza wartością '10'
  -- dodatkowy warunek w WHERE zapobiega duplikatom
     SELECT u.GID
       INTO gid_set
       FROM poema_users u
  LEFT JOIN poema_uid_group g ON (g.intUid = u.ID)
      WHERE u.ID = p_id
        AND g.intUidGroupId IS NULL;
 
  IF gid_set <> '' THEN
    REPEAT
 
      -- Ustalamy pozycję znaku przecinka
      SELECT LOCATE(',', gid_set) INTO pos;
 
      IF pos > 0 THEN
        -- jest przecinek, odetnij pierwszą wartość, i usuń ją ze zmiennej gid_set
        SELECT CAST(SUBSTR(gid_set, 1, pos) AS UNSIGNED) INTO gid;
        SELECT SUBSTR(gid_set, pos + 1) INTO gid_set;
      ELSE
        -- nie znaleziono przecinka, gid_set zawiera wyłącznie cyfrę,
        -- zzutuj ją do zmiennej gid jako
        -- INT i ustaw flagę zakończenia pętli
        SELECT CAST(gid_set AS UNSIGNED) INTO gid;
        SET @done = 1;
      END IF;
 
      -- Można dodać rekord do bazy
      INSERT INTO poema_uid_group (intUid, intGid, intGrantorUid, dtmGranted)
      VALUES (p_id, gid, 1, NOW());
      SET @num = @num + 1;
 
    UNTIL @done END REPEAT;
  END IF;
 
  -- Zwróć ilość dodanych rekordów
  RETURN @num;
END;
//

delimiter ;

Ok, funkcja dodana, teraz wystarczy wywołać ją dla każdego ID użytkownika:

SELECT create_records_in_poema_uid_group(ID) FROM poema_users;

Przy odrobinie inwencji będziesz w stanie wykorzystać funkcję do rozwiązania podobnego problemu w Twojej bazie danych.