MySQL

Loris Tissino

12 novembre 2012

Introduzione

Quelle che seguono sono solo brevi note su cose che si possono fare e potrebbero non essere del tutto ovvie.

Ripristino password di root

Per ripristinare la password di root è necessario fermare il demone, riavviarlo con l'opzione --skip-grant-tables, effettuare la query di update della password, fermarlo e riavviarlo.

sudo service mysql stop
sudo mysqld --skip-grant-tables &
sleep 5
echo "UPDATE mysql.user SET Password=PASSWORD('SeKRetPwd') WHERE User='root';" | mysql -u root
echo "FLUSH PRIVILEGES;" | mysql -u root
sudo killall mysqld
sudo service mysql start

Visualizzazione riferimenti esterni con phpMyAdmin

Se si usa phpMyAdmin, può essere utile definire per le tabelle un "campo da mostrare" (in Struttura / Vedi relazioni), in modo da facilitare l'inserimento dei dati.

Riferimenti con phpMyAdmin

Questa informazione è interna a phpMyAdmin e viene memorizzata nella tabella pma_table_info.

Stored procedures - Funzioni (creazione)

È possibile creare una funzione con un codice simile al seguente:

delimiter $$

create function quality(n INT)
returns varchar(30)
begin
  if isnull(n) then
    return "not set";
  elseif n<6 then
    return "bad quality";
  elseif n<8 then
    return "good quality";
  else
    return "best quality";
  end if;
end$$

delimiter ;

Stored procedures - Funzioni (uso)

L'uso di una funzione personalizzata è equivalente a quello che si farebbe di una funzione predefinita:

select quality(mark) as markdescription from marks where ... ;

Stored procedures - Parametri in ingresso (creazione)

Si possono naturalmente definire dei parametri, come nell'esempio che segue -- per il quale si assume che esistano una tabella friends (id, name, city_id) e una tabella cities (id, name):

DELIMITER $$

CREATE PROCEDURE FriendsByCity(IN city_id INTEGER)
BEGIN
  SELECT friends.name, cities.name FROM friends
  LEFT JOIN cities on friends.city_id = cities.id
  WHERE cities.id = city_id;
END$$

DELIMITER ;

Stored procedures - Parametri in ingresso (uso)

Una stored procedure si richiama con CALL:

CALL FriendsByCity(1);

Stored procedures - Parametri in uscita (creazione)

Se la stored procedure deve restituire dei valori, è necessario dichiarare gli appositi parametri, preceduti dall'indicazione OUT:

DELIMITER $$

CREATE PROCEDURE CountFriendsByCity(IN city_id INTEGER, OUT number INT)
BEGIN
  SELECT COUNT(*) INTO number FROM friends
  LEFT JOIN cities on friends.city_id = cities.id
  WHERE cities.id = city_id;
END$$

DELIMITER ;

Stored procedures - Parametri in uscita (uso)

Quando una stored procedure calcola qualche valore, è necessario richiamare prima la procedura per l'esecuzione, e poi fare una selezione per ottenere i valori calcolati:

CALL CountFriendsByCity(1, @number);
SELECT @number AS friends_count;

Stored procedures - Creazione di API per l'accesso al DB (1/2)

Un buon uso delle stored procedures è quello di consentire la costruzione di interfacce per l'accesso al database che mascherino la struttura interna dello stesso.

Un esempio potrebbe essere quello di far sì che si possa inserire nel database degli amici, con la loro città di residenza, tramite chiamate simili a questa:

CALL AddFriendWithCity('Giulio', 'Udine');
CALL AddFriendWithCity('Alice', 'Treviso');

Deve essere la procedura a verificare se la città è già presente o meno, recuperandone l'id oppure procedendo a un nuovo inserimento, prima di inserire l'amico.

Stored procedures - Creazione di API per l'accesso al DB (2/2)

Per ottenere quanto descritto, si può scrivere una stored procedure di questo tipo:

DELIMITER $$

CREATE PROCEDURE AddFriendWithCity(IN friend_name VARCHAR(50), IN city_name VARCHAR(50))
BEGIN
  DECLARE city_id INT;
  SELECT id INTO city_id FROM cities WHERE name = city_name;

  IF city_id IS NULL THEN
     INSERT INTO cities(name) VALUES (city_name);
     SELECT LAST_INSERT_ID() INTO city_id;
  END IF;

  INSERT INTO friends(name, city_id) VALUES(friend_name, city_id);

END$$

DELIMITER ;

Stored procedures - Cicli e cursori

Può essere necessario prevedere che la procedura effettui dei cicli sulle tuple estratte con una selezione.

Il seguente esempio mostra come questo possa essere ottenuto (il fine della procedura è di inserire in una nuova tabella l'id, il nome e la città degli amici -- si potrebbe ottenere la stessa cosa con puro SQL, ma qui si vuole illustrare il funzionamento dei cicli).

DELIMITER $$

CREATE PROCEDURE TakeSnapshotForFriends()
BEGIN
  DECLARE city_name VARCHAR(100);
  DECLARE friend_name VARCHAR(100);
  DECLARE friend_id INT;
  DECLARE city_id INT;

  DECLARE job_done INT DEFAULT 0;

  DECLARE cur_friends CURSOR FOR
    SELECT friends.id AS id, friends.name AS friend_name, cities.name AS city_name FROM friends INNER JOIN cities on friends.city_id = cities.id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET job_done = 1;

  OPEN cur_friends;

  WHILE job_done = 0 DO
    FETCH cur_friends INTO friend_id, friend_name, city_name;
    INSERT INTO friends_snapshot(friend_id, friend_name, city_name) VALUES (friend_id, friend_name, city_name);
  END WHILE;

  CLOSE cur_friends;

END$$

DELIMITER ;