MySQL/Manipulation de données

MySQL

1. Introduction
2. PhpMyAdmin
3. Parcourir les bases de données
4. Spécifier les noms
5. LDD, LMD et LCD
6. Variables utilisateurs
7. Alias
8. Types de données
9. Manipulation de base
   • mysqldump
10. Manipulation de table
11. Manipulation de données
12. Requêtes
13. NULL
14. Opérateurs
15. Fonctions
16. Procédures stockées
17. Regex
18. Bases de données spatiales
19. Importer et exporter
20. Réplication
21. Optimisation
22. API
23. Sécurité
24. Mots réservés
25. Débogage

INSERT

La syntaxe est la suivante :

 INSERT INTO TableName (Column1, Column2, Column3)
 VALUES (value1, value2, value3)

Ce qui effectue les opérations suivantes : insert value1 into Column1, value2 into Column2, and value3 into Column3.

Insérer un enregistrement (les valeurs sont insérées dans l'ordre où les colonnes apparaissent dans la base) :

INSERT INTO TableName
VALUES (value1, value2, value3)

-- Deux lignes :
INSERT INTO TableName
VALUES (value1, value2, value3), (value4, value5, value6)

INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00);
INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');

-- Copier ceux d'une autre table :
INSERT INTO table1(field1, field2)
SELECT field1, field2
FROM table2

-- Copier une ligne :
INSERT INTO World_Events SELECT * FROM National_Events

Il vaut mieux sélectionner chaque champ à copier sinon la contrainte d'unicité sur la clé primaire peut faire échouer l'insertion.

Astuces de performances :

• Pour insérer plusieurs lignes depuis un fichier, utiliser LOAD DATA INFILE de préférence (bulk insert).
• Si un gros volume d'insertion est trop lent sur des tables indexées non vides, augmenter la valeur de bulk_insert_buffer_size.
• Avant des insertions en masse, retirer les clés.
• Verrouiller une table (LOCK) accélère les INSERT.

UPDATE

 UPDATE table SET field1 = newvalue1, field2 = newvalue2 WHERE criteria ORDER BY field LIMIT n

Exemples :

 UPDATE owner SET ownerfirstname = 'John'
   WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');
 
 UPDATE antiques SET price = 500.00 WHERE item = 'Chair';
 
 UPDATE order SET discount=discount * 1.05
 
 UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID
   SET tbl1.col1 = tbl1.col1 + 1
   WHERE tbl2.status='Active'
 
 UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz')
 
 UPDATE products_categories AS pc
   INNER JOIN products AS p ON pc.prod_id = p.id
   SET pc.prod_sequential_id = p.sequential_id
 
 UPDATE table_name SET col_name =
   REPLACE(col_name, 'host.domain.com', 'host2.domain.com')

 UPDATE posts SET deleted=True
   ORDER BY date LIMIT 1

Avec ORDER BY il est possible de classer les enregistrements avant l'insertion, voire même sur un nombre donné de lignes (avec LIMIT).

Astuces de performances :

• La vitesse des UPDATE dépend du nombre d'index mis à jour.
• En cas d'UPDATE d'une table MyISAM au format dynamique, les colonnes larges causes des lectures de mémoire superflues. Il faut régulièrement lancer OPTIMIZE TABLE pour les réduire à la taille de leur contenu.
• Lancer plein d'UPDATE en même temps sur une table verrouillée est plus rapide qu'individuellement.
• Ajouter FOR UPDATE à la fin d'un SELECT sur une table InnoDB, prépare les locks nécessaires pour ne pas que ces données changent avant un UPDATE les utilisant.

La fonction replace() est sensible à la casse même avec les collations insensibles.

REPLACE

REPLACE fonctionne depuis MySQL 5.5, en remplaçant un enregistrement par un autre, exactement comme DELETE + INSERT, sauf que si l'ancien enregistrement a la même valeur que le nouveau en tant que PRIMARY KEY ou UNIQUE index, l'ancien est supprimé avant l'insertion du nouveau.

Ne pas confondre avec replace().

IGNORE

Pour éviter qu'une insertion soit interdite par une contrainte d'intégrité (ex : clé primaire en double), le mot IGNORE (dans "INSERT IGNORE" ou "REPLACE IGNORE") affiche juste des avertissements si une insertion est ignorée.

Avant MySQL 4.0.1, INSERT ... SELECT opérait implicitement en mode IGNORE : en ignorant les enregistrements qui causeraient des erreurs de valeur de clé dupliquée.

 Une alternative est de passer par le trigger ON DUPLICATE KEY UPDATE id=id.

DELETE et TRUNCATE

 DELETE [QUICK] FROM `table1`
 TRUNCATE [TABLE] `table1`

Quelques précisions :

• Utiliser DELETE sans clause WHERE, supprime tous les enregistrements.
• Si une table contient beaucoup d'index, on peut agrandir le cache pour accélérer les DELETE (variable key_buffer_size).
• Pour les tables indexées MyISAM, parfois DELETE est plus rapide en spécifiant le mot QUICK (DELETE QUICK FROM ...). Cela permet de réutiliser les valeurs des index effacées.
• TRUNCATE efface également les lignes rapidement, en faisant DROP et CREATE (sur certains moteurs de stockage seulement).
• TRUNCATE ne garantit pas la transaction ou le verrouillage.
• DELETE informe de combien de lignes ont été supprimées, mais pas TRUNCATE.
• Après une suppression massive (au moins 30 % des lignes), il convient de lancer OPTIMIZE TABLE juste après pour accélérer la suite.
• Sur des tables InnoDB avec contraintes FOREIGN KEY, TRUNCATE se comporte comme DELETE.

 DELETE FROM `antiques`
 WHERE item = 'Ottoman'
 ORDER BY `id`
 LIMIT 1

Il est possible de classer les lignes avant leur suppression, tout en en choisissant le nombre.

Pour supprimer des enregistrements de plusieurs tables (multi-table delete ou cross table delete) :

DELETE t1, t2
FROM t1
LEFT JOIN t2
WHERE t1.id=t2.id AND t1.value > 1;

-- Synonyme :
DELETE FROM t1, t2
USING t1
  LEFT JOIN t2
    ON t1.id = t2.id
WHERE t1.value > 1;

Toutefois dans les version inférieures à la 4 (et étonnamment constaté comme fausse alerte par PhpMyAdmin sur des versions ultérieures), les jointures sont interdites dans les suppressions, et on doit alors utiliser le WHERE :

DELETE t1, t2
FROM table1 t1, table2 t2
WHERE t1.id=t2.id AND t1.value > 1;

Comme la clause LIMIT ne fonctionne pas sur les suppressions multiples, il faut contourner en supprimant les enregistrements retournés par un SELECT avec LIMIT.