# MySQL/Version imprimable

* Si vous imprimez cette page, choisissez « Aperçu avant impression » dans votre navigateur, ou cliquez sur le lien Version imprimable dans la boîte à outils, vous verrez cette page sans ce message, ni éléments de navigation sur la gauche ou en haut.
* Cliquez sur Rafraîchir cette page pour obtenir la dernière version du wikilivre.
* Pour plus d'informations sur les version imprimables, y compris la manière d'obtenir une version PDF, vous pouvez lire l'article Versions imprimables.
Une version à jour et éditable de ce livre est disponible sur Wikilivres, une bibliothèque de livres pédagogiques, à l'URL : https://fr.wikibooks.org/wiki/MySQL

# Introduction
## Pourquoi MySQL ?

* Gratuiciel en licence GPL version 2.
* Facile d'utilisation : intuitif et ergonomique.
* Vitesse performante.
* Supporte la plupart des commandes SQL ANSI.
* Support technique complet, avec des tutoriels en ligne, des forums, mailing list (lists.mysql.com),et des contrats payants possibles.
* Portabilité : importation et exportation faciles vers des fichiers Excel et autres bases de données.
* Échelonnable : pratique aussi bien pour des petites bases, que pour celles contenant des milliards d'enregistrements avec plusieurs téraoctets de données et des centaines de milliers de tables.
* Contrôle des permission des utilisateurs précis.

### La licence MySQL

MySQL est disponible sous double licence :
* Licence publique générale GNU version 2 : copyleft, permettant d'utiliser MySQL à des fins commerciales ou pas, tant que l'application est à la même licence. Il y existe par ailleurs une exception Free/Libre Open Source Software (FLOSS) qui autorise des programmes non GPL mais gratuits à se connecter au serveur MySQL (comme par exemple des programmes en licence PHP).
* Une soi-disante "commerciale" (bien que GNU GPL puisse être aussi utilisée en commercial mais pas propriétaire), licence payante, conférant le droit d'intégrer MySQL avec une application non FLOSS, redistribuable en dehors de son organisation. Mais ces bibliothèque ne peuvent pas se connecter aux nouvelles versions de MySQL.

## MySQL et ses forks

MySQL étant un freeware, il a donc engendré des fork officieux.

### MariaDB

En 2008, Sun Microsystems acheta MySQL, puis fût acheté par Oracle en 2010. Après l'acquisition, le processus de développement changea. L'équipe commença à sortir de nouvelles versions de MySQL moins fréquemment, avec du nouveau code moins testé, par une communauté moins active
En 2009 Monty Widenius, fondateur de MySQL, quitta l'entreprise pour en créer une autre, appelée The Monty Program. Son fork fût appelé MariaDB, il permet de :
* importer le nouveau code qui sera ajouté à la branche MySQL, en le rendant plus stable ;
* nettoyer le code MySQL ;
* ajouter des contributions de la communauté (plugins et fonctionnalités) ;
* développer le moteur de stockage Aria, anciennement Maria ;
* augmenter les performances ;
* ajouter d'autres fonctionnalités au serveur.
Sa licence est GNU GPLv2, héritée de MySQL.
La plateforme primaire de MariaDB est GNU/Linux, mais il tourne aussi sur Windows. Les moteurs de stockage suivants ont été ajoutés :
* Aria (utilisé pour les tables internes)
* PBXT
* XtraDB
* FederatedX
* SphinxSE
* OQGRAPH
Installation sur Ubuntu : sudo apt-get install mariadb-server && apt-get install mariadb-client

### Drizzle

En 2008 Brian Aker, architecte en chef de MySQL, quitta le projet pour démarrer un nouveau fork appelé Drizzle. Initialement financé par Oracle, Drizzle l'est maintenant par Rackspace. Ses caractéristiques sont :
* seule une petite partie du code MySQL a été conservée : les fonctionnalités essentielles ;
* modularité : beaucoup de choses peuvent être implémentées sous forme de plugins ;
* optimisé multiprocesseur et multicore 64 bits ;
* seuls les systèmes GNU/Linux et UNIX sont supportés.
Il n'existe pas de version publique de ce fork. Il est en licence GNU GPLv2 (héritée de MySQL), mais une licence BSD peut être appliquée.

### OurDelta

OurDelta est un autre fork, maintenu par Open Query. La première branche (5.0), est basée sur MySQL 5.0. La 5.1 est issue de MariaDB. OurDelta contient des patchs développés par la communauté ou des tiers. Il fournit des packages pour certaines distributions GNU/Linux : Debian, Ubuntu, Red Hat/CentOS. Il n'est pas disponible sur d'autres plateformes mais son code source est disponible gratuitement.

### Percona Server

Percona Server set un fork maintenu par Percona. Il propose le moteur de stockage ExtraDB, fork d'InnoDB, et des patchs d'amélioration des performances.

## Installation d'Apache / MySQL pour Windows
### Tout-en-un

Des logiciels tout-en-un (serveur Web, base de donnée MySQL, et PHP) permettent de s'affranchir d'une installation fastidieuse et rédhibitoire pour le débutant :
* EasyPHP : n'a pas vocation à être installé pour de la production, mais pour le développement. Il stocke les bases de données dans C:\Program Files (x86)\EasyPHP\binaries\mysql\data.
* WAMP : est du même type qu'EasyPHP : ce logiciel installe facilement un serveur Web Apache, une base de données MySQL et PHP 4 et 5. Il a l'avantage de permettre de passer facilement de PHP 4 à PHP 5, sans avoir à refaire une installation ou une compilation. Tout comme EasyPHP, c'est un environnement de développement, et non un environnement de production. Attention : la résolution des noms d'hôtes se réalise séparément. Les installations WAMP servent à tester en local sur votre PC. Dans la plupart des cas, il suffit d'utiliser le fichier Hosts local, comme on le ferait sur une machine Linux, afin de lier des noms aux adresses IP. Dans Windows XP, Vista et 7, ce fichier se trouve dans le répertoire systemroot\System32\Drivers\Etc. Il peut se faire que le service ait déjà été configuré. Lorsque vous vous en doutez, contactez votre administrateur réseau. Remarque : vous trouverez une liste des possibilités de résolution de noms avec MS Windows sur Microsoft.com.
* XAMPP : est du même type qu'EasyPHP ou WAMP, le deuxième P étant pour Perl. Son usage est recommandé avec PHPEclipse, et il fournit aussi un serveur Apache Tomcat par défaut.
* The Uniform Server : en anglais seulement avec Apache2, Perl5, PHP5, MySQL5, phpMyAdmin.
Sur Windows 10 pro, le serveur IIS est installé par défaut, et oblige Apache à changer de port (888 au lieu de 80) lors de l'installation. Pour résoudre cela il suffit de décocher Internet Information Services dans Programmes et fonctionnalités, Activer ou désactiver des fonctionnalités Windows.
De même, le port MySQL est susceptible de passer de 3306 à 3388.
Sur Windows 10, EasyPHP development server (alias Devserver, la version rouge) ne fonctionne pas (il manque MSVCR110.dll), mais EasyPHP hosting server (alias Webserver, la bleue) tourne normalement. Or, elle se lance automatiquement à chaque démarrage, ce qui le ralentit significativement. Pour éviter cela, exécuter services.msc, puis passer les trois services ci-dessous en démarrage manuel. Ensuite pour les lancer à souhait (en tant qu'administrateur), créer un script MySQL.cmd contenant les lignes suivantes :

#### Message d'erreur relatif à SSL

Pour l'instant, WAMP ne supporte pas encore le Secure Socket Layer (SSL). L'installation se finit par un message qui vous informe de ce fait. Afin de pouvoir travailler sans problèmes, éditez le fichier c:\windows\php.ini. Cherchez dans ce fichier la ligne qui commence avec extension=php_openssl.dll. Commentez cette ligne en la faisant précéder d'un point-virgule :
;extensions=php_openssl.dll
Si tout se passe bien, vous pouvez ouvrir la page de test dans votre navigateur.

### Installation manuelle

* Apache est disponible sur le site Web de Apache Software Foundation apache.org.
* PHP est téléchargeable sur le site officiel de php. Choisissez le fichier au format ZIP.
* Enfin, vous trouverez MySQL sur mysql.com.

#### Installer Apache

Pour installer Apache, double-cliquez sur le fichier exécutable, et suivez les instructions d'installation automatique.
Si vous installez Apache sur un ordinateur de développement, renseignez le champ "nom de domaine" avec la valeur localhost.
Si vous installez un serveur de production et que vous disposez d'un nom de domaine, vous devriez disposer des informations nécessaires concernant votre nom de domaine, fournies par le registrar.
Une fois l'installation terminée, il faut encore indiquer à Apache qu'il doit fonctionner conjointement avec PHP, car il ne sait pas les traiter par défaut. Pour cela, il faut modifier les informations de configuration d'Apache, contenues dans le fichier httpd.conf, qui se trouve dans le dossier d'installation d'Apache, dans le sous-dossier conf.

#### Installer PHP

Une fois l'archive téléchargée, décompressez-la à la racine de votre disque dur et renommez le dossier en 'PHP'. Dans le dossier PHP, vous trouverez deux fichiers: php.ini-dist et php.ini-recommended. Copiez php.ini-recommended dans votre dossier C:\Windows ou C:\winnt (le nom du dossier dépend de la version de votre système. renommez-le en php.ini.
Ce fichier est le fichier de configuration qui contrôle les options dont vous disposerez. Par exemple :
| PHP.ini | PHP | Rôle |
| error_reporting E_ALL | error_reporting(E_ALL); | Affiche tous les avertissements et erreurs directement sur le site. C'est utile pour la préproduction car cela évite de rechercher d'éventuels messages dans les logs, mais peut perturber la mise en page pour des avertissements bénins. |
| error_reporting 0 | error_reporting(0); | N'affiche aucun message sur le site relatif à son exécution |
| max_execution_time = 300 | set_time_limit(300); | Définit le "timeout", c'est-à-dire le temps maximum en secondes autorisé pour exécuter un script PHP. |
| post_max_size = 80M | ini_set('post_max_size', '80M'); | Définit la taille maximum d'un fichier que l'on peut envoyer au serveur en HTTP. |

#### MySQL

Télécharger et installer le .msi sur http://dev.mysql.com/downloads/gui-tools/5.0.html.
Pour arrêter, démarrer, démarrer automatiquement le serveur MySQL vous devez aller dans la gestion des services (Démarrer/Exécuter/services.msc).

## Installation d'Apache / MySQL pour Linux
### LAMP

Logiciel tout-en-un pour Linux (Apache + MySQL + PHP), comme WAMP pour Windows.

### Installation manuelle
#### Apache sur Debian / Ubuntu

Le service peut ne pas être lancé par défaut, mais même s'il l'est on peut quand-même essayer de l'activer avec :
On peut ensuite tester le serveur, pour voir si une page s'affiche ou s'il refuse la connexion :
Cette adresse est le rebouclage, elle peut aussi être rentrée directement dans tout navigateur web.
Si Apache était déjà installé vérifier le fichier pour indiquer le démarrage automatique d'Apache 2 /etc/default/apache2 :

##### Installer PHP

On distingue principalement deux versions de PHP : celle dont le binaire est appelé par le serveur Web, et php-fpm qui possède son propre service daemon (aussi appelé par le serveur Web) testable ainsi :
FPM signifie FastCGI Process Manager, puisque le processus PHP-fpm écoute les requêtes CGI. Cela peut se traduire soit par des requêtes TCP/IP, soit par un socket Unix (.sock dans le vhost).
PHP peut-être installé avec toutes les déclinaisons de la distribution Debian (stable, testing, unstable). Il suffit pour cela d'insérer vos lignes préférées dans le fichier /etc/apt/sources.list :
Ce qui suit suppose que le serveur Web a bien été installé ; exécuter les commandes suivantes :
Une fois ces commandes exécutées, redémarrer le serveur Web. Dans le cas d'Apache cela s'effectue avec la commande suivante :
Si tout s'est bien passé, vous disposez maintenant d'un serveur Web qui a la capacité d'exécuter des scripts PHP dans votre navigateur.
Testons :
Pour débugger :

###### Mise à jour

Pour PHP 7 ou 8 sur Ubuntu :
Sur Debian :
Puis :
Une fois les serveurs Web installés, ils se lancent automatiquement à chaque démarrage de la machine, ce qui est souhaitable pour un serveur, mais pas toujours pour un PC. Pour éviter cela, il suffit d'y désactiver les daemons :

###### Bibliothèques

Voici une liste de bibliothèques fréquemment utilisées dans les applications :
D'autres s'installent avec pecl au lieu de apt.
Pour les activer après installation, on peut éditer le php.ini ou lancer : phpenmod nom_du_module_php. Ex : sudo phpenmod gd.
Pour les désactiver : phpdismod nom_du_module_php
Pour détecter l'emplacement du php.ini de la version de PHP par défaut : php --ini.

##### Désinstaller PHP

Pour éviter de désinstaller tous les paquets PHP un par un (par exemple après une bascule de PHP7.0 vers PHP7.1), il existe "ppa-purge" :

#### Apache sur Gentoo

Premièrement il faut installer Apache :
Ensuite, il faut installer PHP :
Puis il faut qu'apache utilise PHP dans sa configuration.
* Code : Configuration de apache
* # nano -w /etc/conf.d/apache2
* APACHE2_OPTS="-D PHP5"

#### MySQL seul

MySQL est disponible sur http://dev.mysql.com/downloads/gui-tools/5.0.html au format :
* .msi (Windows)
* .dmg (Mac)
* .rpm (Linux)
* .tar
En l'absence de gestionnaire de paquets, utiliser le .tar ainsi :

#### APT
##### Variante

La dénomination des paquets mentionnés peut varier légèrement selon la version. Dans un terminal, entrez :
et confirmez.
(Remarque : il semblerait qu'en installant le paquet "mysql-server-5.0", au lieu du paquet mentionné plus haut, certaines personnes rencontrent des problèmes. Il est donc préférable d'installer ce paquet, ou d'installer la dernière version 4 stable avec : $ sudo apt-get install mysql-server-4.1. Consultez le forum pour plus d'informations : )
Lancez ensuite la commande :
Appuyez sur Entrée lorsqu'il vous demande le mot de passe root MySQL : pour le moment il n'y en a pas.
Le script vous demande alors si vous voulez mettre un mot de passe pour l'utilisateur root. Répondez Y, et entrez (2 fois le nouveau mot de passe du root MySQL). Il vous pose ensuite une série de questions. Si vous ne savez pas quoi répondre, acceptez les choix par défaut en appuyant simplement sur Enter.
Votre serveur MySQL est prêt. Par défaut il se lance à chaque démarrage du système, si vous ne le souhaitez pas, il vous suffit de lancer :
et de répondre "Non" à la question du démarrage systématique de MySQL.

#### Sur Gentoo
### Modules

De nombreux modules complémentaires peuvent être installés sur Apache.
Pour les lister, on utilise apachectl (parfois apache2ctl) :
ou
Pour activer un module :
Un fichier est alors créé dans /etc/apache2/mods-enabled/.
Exemple pour la réécriture d'URL :
Pour le désactiver :
La configuration du module reste toutefois disponible dans /etc/apache2/mods-available/.

### Sites

Pour lister les sites du serveur :
Pour activer un site :
Le fichier du site est alors visible dans /etc/apache2/sites-enabled/.
Pour le désactiver :
Le site est dans /etc/apache2/sites-available/.

## Problème d'encodage d'Apache2

Si vous rencontrez un problème d'encodage des caractères de vos pages, par exemple les caractères accentués apparaissant sous la forme "�" (<?>), c'est probablement parce qu'Apache2 déclare dans les en-têtes HTTP qui accompagnent les pages visionnées un encodage par défaut en Unicode (UTF-8) :
Tandis que les pages visionnées utilisent un autre encodage des caractères, comme par exemple Latin1 (ISO-8859-1). Même si vos documents indiquent le jeu de caractères utilisé, le paramètre donné par le serveur dans les en-têtes HTTP est prioritaire !
Pour corriger ce problème, il faudra éditer /etc/apache2/apache2.conf :

#### Encodage par défaut en Latin1 (ISO-8859-1)

Cherchez la ligne suivante :
Décommentez-la en enlevant le # :
Pour ceux qui ont la locale iso-8859-15 (sinon vous pouvez faire "sudo dpkg-reconfigure locales" pour l'ajouter) et qui désirent l'utiliser par défaut, ajoutez un 5 en fin de ligne :
ainsi que la ligne suivante dans le paragraphe en-dessous :
Il ne vous reste plus qu'à mettre "fr" en première position dans la ligne LanguagePriority (juste au-dessus), et à demander à apache de relire sa configuration :

#### Aucun encodage par défaut

Il est également possible de s'affranchir de tout encodage par défaut, de la manière suivante :
Cherchez la directive AddDefaultCharset :
Remplacez l'attribut par la valeur Off :
Là encore, on demandera à Apache de relire sa configuration :
Maintenant, les en-têtes HTTP ne contiendront plus d'indication d'encodage des caractères. Attention : il faudra alors que chaque page indique l'encodage utilisé, car s'en remettre à la détection automatique par les navigateurs peut s'avérer assez aléatoire !

## Installation sur Docker

Dans docker-compose.yml, il convient de partager un volume pour les importations de dumps :

## Hello world

Pour entrer des commandes SQL on peut soit :
* Lancer le logiciel en shell.
** Linux : sudo mysql -u root, ou directement sur une base spécifique : mysql -h localhost -u root MaBase. Si l'accès est refusé, ajoute "-p" pour taper le mot de passe.
** Windows : "C:\Program Files (x86)\EasyPHP\binaries\mysql\bin\mysql.exe" -h localhost -u root MaBase
* Ouvrir une fenêtre SQL dans PhpMyAdmin (ex : http://localhost/modules/phpmyadmin/#PMAURL-1:server_sql.php?server=1).
Ainsi, on peut exécuter plusieurs commandes SQL en une seule commande shell :

## Références

* ↑ http://www.mysql.com/why-mysql/benchmarks/
* ↑ http://archive.mariadb.org/mariadb-5.5.28a/
* ↑ https://www.tecmint.com/connect-nginx-to-php-fpm/

# PhpMyAdmin
## Introduction

PhpMyAdmin est un paquet qui permet, grâce à une interface web, d'éditer/créer/supprimer des bases MySQL, des tables et leur contenu.
Prérequis : Soit Docker, soit :
* Serveur Apache ou Nginx installé.
* PHP installé (en module d'Apache ou en processus FPM).

## Installation de PhpMyAdmin
### Docker Compose

On peut utiliser traefik simplifier l'installation. En effet, il assure le routage pour éviter d'avoir à ajouter l'adresse de PhpMyAdmin dans le fichier hosts de l'OS et les vhosts su serveur Web :

### Windows

Sous Windows, il est fourni avec WAMP ou EasyPHP, mais peut aussi être installé indépendamment depuis http://www.phpmyadmin.net/home_page/downloads.php.

### Linux

Sous Linux, il est fournit avec LAMP. Depuis un tout-en-un, il suffit de créer un chemin accessible depuis le serveur Web :
Si ça ne fonctionne pas, suivre le paragraphe suivant.

#### Installer Apache et PHP avec PhpMyAdmin

Grâce aux dépendances des paquets, cette opération peut se faire en une seule fois : Remarque : Vérifiez que la case "Traiter les paquets recommandés comme des dépendances" soit cochée dans Synaptic, configuration, préférences.
Cela installera automatiquement apache2 + php + modules d'apache pour PHP et MySQL + PhpMyAdmin. Pour accéder à PhpMyAdmin, il faut se rendre à la page http://localhost/PhpMyAdmin.
Note : En cas de problème d'authentification (erreur 2002 notamment) installer le paquet mysql-server peut résoudre ce dernier.
Après l'installation, il vaut mieux modifier les droits d'accès de root, et ajouter un mot de passe pour un peu plus de sécurité. Pour cela, il faut se rendre à la page privilèges de PhpMyAdmin.
Remarque pour Ubuntu 5.04 (Hoary Hedgehog) : Afin que cette commande fonctionne il est nécessaire d'avoir effectué les modifications suivantes : dans /etc/apt/ éditer le fichier sources.list supprimer les # des lignes suivantes :
(cette ligne est dans certain cas '# deb http://archive.ubuntu.com/ubuntu/ hoary universe main restricted multiverse')
Pour la version d'Ubuntu 5.10 (Breezy), vous pouvez effectuer ces changements avec le gestionnaire de paquets synaptic (apt) : Système ---> Administration ---> Gestionnaire de paquets Synaptic
Lancer le chargement des nouvelles sources :
Puis lancer l'installation de PhpMyAdmin comme décrit ci-dessus.

## Test

Une fois les paquets téléchargés et installés, taper http://localhost/phpmyadmin/ dans la barre d'adresse d'un navigateur (1) et faire "Entrée". Une page s'affichera, invitant à choisir sa langue d'affichage (2), entrer son nom utilisateur (3, sinon il définit phpmyadmin par défaut) et son mot de passe (4). Valider avec le bouton "Exécuter"
S'il y a un message d'erreur persistant à la place, il faut décommenter et définir les identifiants dans le fichier phpmyadmin\config.inc.php.
Vous obtiendrez cet écran où toutes les bases sont paramétrables.

## Configuration
### PHP

La configuration par défaut ne permet que d'importer des bases de données de maximum 2 Mo. Au-delà l'erreur suivante survient :
* Aucune données n'a été reçu en vue de l'importation. Aucun nom de fichier n'a été fourni, ou encore la taille du fichier a dépassé la limite permise par votre configuration de PHP.
Pour étendre ce quota, modifier quatre lignes dans php.ini :
Puis relancer Apache.
De même, la durée de session par défaut étant de 1440 s, il convient de les étendre dans php.ini (paramètre session.gc_maxlifetime) avant de les étendre dans config.inc.php, ou par l'interface graphique.

### MySQL

Sur la page d'accueil, sans sélectionner de base, il y a un onglet Paramètres, puis dedans un Fonctionnalités pour gérer l'interface PHPMyAdmin.
De plus, l'onglet Variable permet de lire et configurer les paramètres de MySQL.

## Créer des utilisateurs MySQL

Lancez votre navigateur préféré sur l'adresse http://localhost/phpmyadmin/
* Connectez-vous en tant que 'root' avec le mot de passe du root MySQL que vous avez défini tout à l'heure
* Cliquez sur "Privilèges", puis sur "Ajouter un utilisateur"
* Entrez les informations de l'utilisateur
* Dans la table "Privilèges globaux", définissez les droits de l'utilisateur. Si vous ne savez pas quoi mettre, cochez toutes les cases des colons "Données" et "Structures".
* Validez en cliquant sur "Exécuter".
Vous pouvez maintenant vous déconnecter en cliquant sur "Quitter" et vous connecter avec le login et le mot de passe du nouvel utilisateur.

## Conception

Il est possible de modéliser les tables et d'en convertir le code, grâce au concepteur accessible dans les onglets depuis une table :

## Optimisation

Ce SGBD permet l'optimisation de requête, tel que décrit dans MySQL/Optimisation.
Par exemple, dans les évènements il est possible de lancer un OPTIMIZE TABLE toutes les nuits, d'obtenir une interface graphique avec version imprimable pour visionner le tableau d'un EXPLAIN, ou encore pour ajouter un trigger :

# Parcourir les bases de données
## mysql

mysql est une base de données système contenant des variables propres au serveur, telles que les comptes utilisateurs (dans la table "user"), le fuseau horaire (dans la table "time_zone") ou les logs.
Il est possible d'y stocker l'historique des requêtes entrées sur le serveur en activant :
Elles apparaissent ainsi dans la table general_log, ce qui peut être pratique pour déboguer une application en boite noire. Par contre il faut vite les désactiver après la mesure car elles peuvent devenir très volumineuses.

## INFORMATION_SCHEMA

information_schema est une base de données virtuelle apparue dans MySQL 5, qui contient des métadonnées sur le serveur et ses bases. Elle se remplit automatiquement et n'est pas modifiable (ni la structure, ni les données), on peut donc juste la lire.
Beaucoup de ses informations sont récupérables aussi avec la commande SHOW, plus rapide. Toutefois information_schema est plus flexible.
La table de INFORMATION_SCHEMA sur les bases est SCHEMATA. Le programme mysqlshow (en ligne de commande DOS/Unix) peut aussi être utilisé à la place.
Cela ne peut fonctionner que si le serveur est démarré, et sans l'option --skip-all-databases.
En l'absence des privilèges SHOW DATABASES, seule les bases sur lesquelles le compte a des permissions seront visibles.

## Exemples d'utilisation
### Lister les tables les plus volumineuses

Pour trier les tables par taille :
Pour avoir la taille d'une table, ajouter :

## Lister les bases

Les commandes SQL suivantes fournissent les informations relatives aux bases de données situées sur le serveur courant.
Toutes :
le mot clé SCHEMA peut être utilisé en lieu et place de DATABASES. MySQL ne supporte pas les SCHEMA des standards SQL, donc il est synonyme de DATABASES. il a été ajouté pour la compatibilité avec d'autres SGBD.

### Ajouter un filtre sur les noms des bases

L'opérateur LIKE fonctionne selon le langage de manipulation de données standard. Donc il est faisable de lister toutes les bases commençant par 'wiki' ainsi :

### Filtres complexes

En utilisant la clause WHERE :
Elle autorise les expressions rationnelles, les opérateur de comparaison '=', '<' et '>', et les fonctions sur les chaînes de caractères.

## Lister les tables et les vues

Les tables `TABLES` et `VIEWS` de la base INFORMATION_SCHEMA fournissent des informations sur les tables et les vues de toutes les bases du serveur.
Les commandes SQL suivantes donnant relativement peu d'information sur les vues, il faudra recourir à la table `VIEWS` pour les métadonnées.
mysqlshow peut aussi être utilisé à la place.

### Show all tables

Les deux formes sont équivalentes.

### Appliquer un filtre

La syntaxe est la même que pour les bases :
De plus, par défaut SHOW TABLES ne retourne que la colonne du nom des tables. Le mot FULL permet d'en ajouter une deuxième appelée `Table_type` :
Elle peut contenir trois valeurs différentes : 'BASE TABLE' pour les tables, 'VIEW' pour les vues, et 'SYSTEM VIEW' pour les tables spéciales du serveur (généralement celles de la base INFORMATION_SCHEMA).
Donc pour lister les vues :

### Filtrer les tables ouvertes

La liste des tables non temporaires (sans les vues) ouvertes dans le cache :

### Supprimer toutes les tables

Il faut exécuter le résultat de cette requête :

### Supprimer toutes les procédures stockées
## Lister les champs

Les commandes suivantes correspondent aux informations de la table COLUMNS de INFORMATION_SCHEMA.
mysqlshow le permet également.

### DESCRIBE

Le résultat contient six colonnes :
| Field | Type | Null | Key | Default | Extra |
DESC est un alias de DESCRIBE.
'filtre' peut être un nom de colonne. S'il est spécifié, seule cette colonne sera affichée. Si 'filtre' contient '%' ou '_', il sera évalué comme une condition LIKE. Par exemple, pour obtenir tous les champs commençant par 'wiki' :

### EXPLAIN

Synonyme de DESC :

### SHOW FIELDS

Autre synonyme de DESC :
Remarque : le mot FULL rajoute une colonne "Privileges" et une "Comment" :
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
La colonne des commentaires peut servir à expliquer la signification du nom d'un champ, en apparaissant sous ce dernier lors des sélection dans PhpMyAdmin, et peut même être interprétée par certains logiciels. Par exemple, l'ORM Doctrine comprend le commentaire "DC2Type:array" pour choisir comment désérialiser les tableaux JSON stockés en LONGTEXT.

### SHOW COLUMNS

Autre synonyme de DESC :
En fait FIELDS et COLUMNS sont synonymes. EXPLAIN et DESC ne supportent pas toutes leurs clauses (filtre).
De plus, les syntaxes ci-dessous sot équivalentes :

## Lister les indexes

Les commande suivantes renseignent sur les indexes d'une table, ses clés. Elles sont aussi dans la table `COLUMNS` de INFORMATION_SCHEMA, et accessibles via mysqlshow -k.
Exemple de résultat :
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
| Table1 | 0 | PRIMARY | 1 | id | A | 19 | NULL | NULL | | BTREE | |
Le mot KEYS est synonyme de INDEX. Aucune autre clause n'est possible avec.
Avec phpMyAdmin il est facile de créer plusieurs fois le même index, ce qui ralentit ensuite toutes les requêtes.
Pour supprimer un index :
Un alias existe aussi : SHOW KEYS FROM `TABLE`;.

## Lister les clés étrangères

Pour le nom des clés étrangères d'une table :

## Références

* ↑ https://dev.mysql.com/doc/refman/5.7/en/system-schema.html

# Spécifier les noms

Afin de distinguer les variables des mots réservés, on place les identificateurs MySQL (noms des tables, champs, et bases) entre deux accents graves (`). Il s'agit du caractère ASCII 96, disponible sous Linux en pressant les deux touches ALT + '.
Généralement il est optionnel, mais il permet de meilleurs messages d'erreur, par exemple :
vs :
Montre qu'il manquait un s à group:
Cette syntaxe autorise l'utilisateur à employer des mots réservés dans leurs noms d'objets. On peut même utiliser des accents graves en les tapant deux fois, à la manière des caractères d'échappement :
Par contre cette syntaxe n'est pas portable, car le standard SQL recommande le guillemet (").

# Syntaxe
## Principe

On peut diviser le vocabulaire SQL en trois groupes :
* Le LDD est composé de CREATE, ALTER et DROP. Il permet d'ajouter, modifier et supprimer les structures logiques qui contiennent les données, ou autorisent les utilisateurs à y accéder ou à les maintenir (bases, MaTables, vues, clés...). le LDD concerne les métadonnées.
* Le LMD est constitué de INSERT, UPDATE et DELETE. Pour ajouter, modifier et supprimer les données stockées dans les bases.
* Le LCD représente GRANT et REVOKE. Il s'agit de la sécurité de la base, des permissions des utilisateurs.
On peut aussi distinguer deux autres catégories :
* Le DQL (Data Query Language : langage de requête de données), comme SELECT, SHOW et HELP. Ils sont rattachés au LMD dans le modèle traditionnel.
* Le LCT (en anglais DTL ou Data Transaction Language : langage de transaction de données) avec START TRANSACTION, SAVEPOINT, COMMIT et ROLLBACK [TO SAVEPOINT]. Affiliable au LCD dans le modèle à trois catégories.

## Visualisation

* L'étoile (alias wildcard : "*") désigne toutes les colonnes d'une table, pour éviter de les sélectionner une par une.
* Quand un nom de colonne est un mot réservé par MySQL, pour ne pas qu'il soit interprété, il faut l'entourer d'accents graves (ex : SELECT `key`).

## Clés primaires et étrangères
### Lecture

Les clés (primaires et étrangères) d'une table sont incluses dans sa description :
Mais on peut aussi ne sélectionner qu'elles :
Sinon :

### Création

Pour ajouter une clé étrangère à une table existante :

#### CONSTRAINT

Facultativement, la clé étrangère peut aussi être nommée : on la baptise après le mot CONSTRAINT.
Cette syntaxe permet aussi d'ajouter des évènements en cas de suppression ou mise à jour du champ lié par une clé étrangère, à préciser parmi : RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Ex :

#### CHECK

A ne pas confondre avec CONSTRAINT, il gère les contraintes indépendantes des autres tables. Exemples :

### Suppression

Pour désactiver les contraintes le temps d'une session :
Pour le faire globalement :
Pour faire supprimer définitivement une contrainte :
Mais parfois pour éviter les vérifications des contraintes, il suffit de lancer la commande suivante le temps d'un UPDATE avec un WHERE :
Lors du clustering comme avec Galera, il faut avoir une clé primaire par table.

## Créer/supprimer une vue

Il est impossible d'ajouter un index à une vue.

## Permissions

Pour les lister :
Pour les définir:

## Manipuler des variables

Les définitions sont effectuées à l'aide des mots clés "select" (suivi de ":=") ou "set" (avec "=") :
Pour les afficher ensuite :
Certaines variables globales représentent la configuration du système, et peuvent être changées provisoirement le temps d'une session, ou de façon permanente :
En cas de conversion de dates au format Text en Datetime, cela les efface toutes à 0000-00-00 00:00:00

### Variables globales

Pour avoir la version de MySQL :

## Références

* ↑ https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
* ↑ http://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html

# Variables utilisateurs
## Variables locales

Les types des variables MySQL sont forcément scalaires. Pour une "variable tableau", il faut donc créer une table temporaire ou concaténer chaque ligne.
Les variables locales ne peuvent pas être lues en dehors de leur fonction ou procédure stockée.
Elles sont déclarées après DECLARE avec leur nom, leur type, et éventuellement leur valeur par défaut :

## Variables de session

Les variables obéissent à certaines règles :
* Leurs noms commencent par "@" (ex : @total).
* Elles sont déclarées avec le mot SET, ou bien SELECT accompagné de l'opérateur d'assignation :=.
* Une variable définie dans la liste de champ ne peut pas être utilisée comme une condition.
* Les variables de session durent le temps du thread.
Pour copier dans valeurs d'une sélection dans une ou plusieurs variables :
Elles peuvent être utiles quand on doit agréger plusieurs valeurs sans jointures entre leurs tables. Ex :

## Variables globales

Une variable globale est visible pour tous les utilisateurs, elle est précédée de "@@".
Elles peuvent modifier les fichiers de configuration définitivement pendant la session. Donc en les changeant, il est nécessaire de préciser le critère définitif ou éphémère, en distinguant set global et set session.
Exemple :

### sql_mode

Un script peut avoir un comportant différent sur deux bases tournant sur la même version de MySQL. Par exemple il est possible d'imposer de préciser dans le GROUP BY toutes les variables sélectionnées à regrouper avec ONLY_FULL_GROUP_BY.
Ce paramétrage est visible avec sql_mode :
Pour le modifier au démarrage dans Docker compose :

## Références

* ↑ https://dev.mysql.com/doc/refman/5.7/en/set-variable.html
* ↑ http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference
* ↑ http://dev.mysql.com/doc/refman/5.7/en/declare-local-variable.html
* ↑ https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

# Alias

Une expression ou une colonne peut être baptisée avec AS. Cet alias est utilisé comme nom de colonne et peut donc être nommé dans les clauses des requêtes. Exemple :
Ces alias fonctionnent avec ORDER BY, GROUP BY et HAVING, mais pas WHERE.
Cela peut aussi servir à raccourcir les noms des tables employées comme préfixes.
De plus les alias peuvent jouer un rôle crucial pour les auto-jointures. Par exemple ci-dessous, la table personne est référencée par p et c :

# Types de données
## Types de données principaux

Voici les valeurs acceptées pour chaque type de variable en octets, au-delà desquelles un stockage provoque un débordement :
Il faut préférer DECIMAL(10,2) à FLOAT car ce dernier peut se révéler imprécis. Ex : 39.99 x 1 = 39.9900016784668.
Pour gagner en performances et en espace disque, il est très important de restreindre au maximum les types. Par exemple pour une liste de pays, utiliser SMALLINT au lieu de INT s'avère payant.

## VARCHAR

VARCHAR est l'abréviation de CHARACTER VARYING (caractère variant en anglais). 'n' représente la taille maximum de colonne (jusqu'à 65 535 caractères). Par exemple, une colonne de type VARCHAR(10) peut contenir 10 caractères maximum. La taille du stockage correspondant en fait à la taille du texte contenu (L), plus un ou deux octets (un si la taille est inférieure à 255).
Par exemple pour la chaîne "abcd", L = 4 et le stockage = 5.
CHAR(n) est similaire à VARCHAR(n) sauf qu'il occupe une taille fixe, il ne tient pas compte de son contenu. Un champ CHAR prend donc plus de place de stockage, est 20 % plus performant pour les recherches s'il est index.

## TEXT et BLOB

Les types TEXT et BLOB (binary large object) ont une taille maximum de 65 535 caractères. L'espace requis est la taille réelle des données stockées, plus un ou deux octets (un si < 255). Comme elles ne sont pas stockées dans le fichier de données, toutes les opérations (INSERT / UPDATE / DELETE / SELECT) les concernant sont plus lentes, mais cela a l'avantage de rendre celles qui ne les touchent pas plus rapides.
Toutefois le BLOB possède plusieurs déclinaisons :
* TINYBLOB : 255 o.
* MEDIUMBLOB : 16 Mo.
* LONGBLOB : 4,29 Go.

## INTEGER

Spécifier une valeur "n" entre parenthèses n'a aucun effet. De toute façon, la taille maximum des données stockées est de 429 fois 10$^7$.
Pour les nombres uniquement positifs (comme les index qui représentent les numéros de ligne), utiliser UNSIGNED, sinon SIGNED.
En rentrant un nombre supérieur à la limite (ex : 1234567890123456789), le logiciel dira qu'une ligne a été affectée mais en fait il ne modifie pas le champ. Pour pallier cela, modifier le type en BIGINT.
Le nombre entre parenthèses après les types entiers indique sur combien de chiffres l'entier stocké est prévu pour être affiché. Toutefois s'il est plus long que cela, cela n'empêchera pas son stockage.

## DECIMAL

Syntaxe : DECIMAL(n,m).
Ex : DECIMAL(4,2) signifie des nombres jusqu'à 99,99 (quatre chiffres dont deux réservés aux décimales).

## DATE

Il existe trois types pour stocker des dates : DATETIME, DATE, et TIMESTAMP.
MySQL récupère et affiche les dates au format "AAAA-MM-JJ" (plus pratique pour les classer de gauche à droite).
DATETIME est utilisé quand les valeurs doivent contenir l'heure en plus du jour.
La différence entre DATETIME et TIMESTAMP est que la taille des formatsTIMESTAMP est limitée aux années 1970-2037.
Le type TIME peut stocker les heures du jour (HH:MM:SS) sans date. Il peut aussi représenter une période de temps (ex : -02:00:00 pour deux heures avant). Limité entre '-838:59:59' et '838:59:59'.
YEAR peut stocker des années.
Pour manipuler des dates, il faut préciser un jour et pas seulement une heure, car pourrait interpréter "HH:MM:SS" comme une valeur "YY:MM:DD".
Les exemples suivant montrent la plage de date précise pour les temps Unix, démarrant à l'époque Unix jusqu'à 2038 ($2^{31}-1$).
Les fonctions de manipulation de date seront exposées dans un chapitre ultérieur.

## SET et ENUM

SET est un type dont les valeurs sont prédéfinies dans une liste lors de la création de la table.
ENUM est similaire mais restreint à un seul membre, alors que SET autorise le stockage de n'importe lesquelles de ses valeurs ensemble.
Exemple :

## Vérification

Pour connaitre le type d'un champ :
Pour vérifier que le contenu d'un champ est un toujours un nombre entier :

## Références

* ↑ https://dev.mysql.com/doc/refman/5.5/en/integer-types.html
* ↑ https://dba.stackexchange.com/questions/424/performance-implications-of-mysql-varchar-sizes/1915#1915
* ↑ Christian Soutou, Apprendre SQL avec MySQL : Avec 40 exercices corrigés, Éditions Eyrolles, 7 juillet 2011 (lire en ligne)
* ↑ https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html
* ↑ http://dev.mysql.com/doc/refman/5.0/fr/set.html

# Manipulation de base
## Création

mysqladmin create permet de le faire en ligne de commande.
NB : dans MySQL, CREATE SCHEMA est un parfait synonyme de CREATE DATABASE, contrairement à d'autres SGBD comme Oracle ou SQL Server.

## Suppression

mysqladmin drop permet de le faire en ligne de commande. Le paramètre -f force celle-ci sans poser de question.

## Renommage

Dans les versions 5.1.x il existait une commande RENAME DATABASE db1 TO db2;, mais elle a été retirée suite à des pertes de données.
Il reste toutefois la ligne de commande pour le faire en plusieurs étapes :
Une autre option avec les droits root, est de renommer le répertoire de la base :
Après renommage, il convient de migrer les permissions :

## Copie
### Avec mysqldump

    Pour plus de détails voir : MySQL/mysqldump.

### Avec des outils de modélisation

Ces logiciels permettent de représenter les tables sous formes de diagrammes.

#### phpMyAdmin

    Pour plus de détails voir : MySQL/PhpMyAdmin.

#### MySQL Workbench

MySQL Workbench permet également la migration depuis d'autres bases de données, telles que Microsoft SQL Server.
Par rapport à phpMyAdmin, il a l’inconvénient de devoir être installé, mais a l'avantage de pouvoir modifier des tables en changeant de champ au clavier, comme dans un tableur.

#### DBDesigner

DBDesigner est en licence GNU GPL, mais ne peut pas être considéré comme un freeware car il requiert un compilateur Kylix non gratuit.
Il rencontre une erreur de connexion à MySQL sur la version 4 : unable to load libmysqlclient.so. Pour la résoudre :
* Installer les "Shared compatibility libraries" MySQL pour version 5.0).
Sous Linux :
* Remplacer le fichier libmysqlclient.so de DBDesigner par le nouveau :
* Trouver et installer kylixlibs3-unwind-3.0-rh.4.i386.rpm
* Trouver un vieux xorg (ex : xorg-x11-libs-6.8.2-37.FC4.49.2.1.i386.rpm depuis FC4) et l'extraire :
* Récupérer libXft.so.1.1 dans ce package et l'installer :
Maintenant DBDesigner4 peut se connecter à MySQL5.

#### Kexi

Il existe aussi Kexi de Calligra Suite, téléchargeable sur http://userbase.kde.org/Calligra/Download/fr.

### OpenOffice Base et ODBC

Configuration typique :
* Soit une base MySQL appelée mysqlhost.
* OpenOffice.org sur la machine cliente (Debian GNU/Linux dans l'exemple).
* Connexion via ODBC.
Sur le client, installer mysql-client :
Sous Fedora/CentOS :
Avant d'installer ODBC, test la connexion distante localement :
Il faut créer la base mysqldatabase et l'utilisateur user1 sur mysqlhost.
Toujours sur la machine cliente :
Pour Fedora/CentOS :
Les fichiers /etc/odbc.ini et /etc/odbcinst.ini sont créés.
odbcinst.ini déclare le pilote ODBC disponible. Exemple pour Debian :
Pour CentOS :
Maintenant odbcinst est utilisable :
Pour d'autres options : man odbcinst
Il faut créer au moins un DSN (Data Source Name ou Data Set Name), parce que chaque connexion ODBC avec OOo est initialisée avec.
Pour créer un DSN, il existe différente possibilités :
* Modifier /etc/odbc.ini (concerne tous les utilisateurs)
* Modifier ~/.odbc.ini (concerne un seul utilisateur)
* Utilise les applications graphiques comme ODBCConfig (Debian : unixodbc-bin, Fedora : unixODBC-kde).
Finalement, ces applications graphiques modifient /etc/odbc.ini ou ~/.odbc.ini.
Par exemple, un fichier /etc/odbc.ini (le nom du DSN est entre crochets) :
Dans ce cas, le DSN est appelé MySQL-test.
Ensuite pour tester, utiliser la commande isql :
Depuis OOo :
À ce stade, le programme est connecté à la base mysqldatabase en tant que user1. Il reste donc le mot de passe à rentrer.
Ensuite, Java est requis dans les Wizards uniquement (lors de création directe JRE est inutile) :
* Wizard pour créer un formulaire.
* Wizard pour créer des rapports.
* Wizard pour créer des requêtes.
* Wizard pour créer tables.
Les distributions GNU/Linux fournissent généralement OpenOffice avec IcedTea (openjdk-6-jre/java-1.6.0-openjdk) ou GCJ (java-gcj-compat/java-1.4.2-gcj-compat) donc les fonctionnalités basées sur du Java fonctionnent.

## Restauration

* Sous Linux, le mot de passe est demandé après entrée de la commande :
* Sous Windows, par défaut le compte root n'a pas de mot de passe et MySQL n'est pas dans les variables d'environnement donc on utilise son chemin absolu :
Contrairement aux importations de PhpMyAdmin il n'y a pas de limite. Par exemple on peut charger une base de 2 Go en cinq minutes.
À partir d'un fichier compressé :

## Références

* ↑ https://dev.mysql.com/doc/refman/5.1/en/rename-database.html
* ↑ http://www.thegeekstuff.com/2014/03/mssql-to-mysql/

# mysqldump

mysqldump peut sauvegarder des bases de données MySQL. Son fonctionnement est particulièrement intéressant car elle génère les commandes SQL permettant de re-créer la base de données sur un autre serveur, ou d'exporter les données vers du CSV et du XML.

#### Backup

Pour exporter la base de donnée « myBase », on utilise la commande suivante :
Ceci fera l'export dans un fichier « myBase_backup.sql ».
Exemple de dump d'une base :
Pour dumper toutes les bases :
Pour dumper certains enregistrements de certaines tables :

##### Sans les données

Pour ne récupérer que le schéma :

##### Aux formats Oracle, PostgreSQL, Microsoft SQL Server...

L'option --compatible permet de spécifier à mysqldump le format à utiliser pour être compatible avec les bases de données existantes. Exemple :
Cette option peut prendre les valeurs suivantes : ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options

##### Tâche planifiée

Pour définir le backup automatique d'une base tous les soirs à minuit, sous Linux :
Le fichier décompresser est au format SQL et peut donc être exécuter pour recréer les bases, tables, et insérer les enregistrements.

#### Restauration

Pour importer une base de données sauvegardée via mysqldump, on utilise la commande cliente mysql et une redirection en entrée :
En cas de gros volume, il est plus rapide de copier les .bin des bases, au lieu d'utiliser mysqldump.

##### Préparation

Pour réinjecter le dump dans d'autres bases, il vaut mieux nettoyer la base de destination.

##### Vers une base distante

En utilisant mysqldump et ssh, on peut dupliquer une base de données sur une machine distante :

# Manipulation de table
## CREATE TABLE

La syntaxe de création des tables d'une base est ainsi :
Les bonnes pratiques du nommage des tables sont les mêmes que pour les colonnes, et consistent à évier les mots réservés, les abréviations, et n'utiliser que des minuscules en snake case.

### Création à partir d'une autre table

Les enregistrements de la requête SELECT peuvent être enregistrés dans une nouvelle table. Les types des données seront les mêmes que dans l'ancienne table. Exemple :

### Auto-incrémentation

De plus, MySQL peut assurer l'auto-incrémentation des clés uniques grâce à l'option AUTO_INCREMENT. En cas de troncature de la table, le compteur peut être réinitialiser avec :

### Moteurs

Le moteur de stockage d'une table est défini par la clause ENGINE=. On en distingue deux principaux :
* MyISAM : light.
* InnoDB : gère les transactions (commit / rollback) et les clés étrangères.

#### Tables d'archive

MySQL propose un type de table d'archive, prenant moins de place (par compression) mais dont on ne peut pas supprimer les enregistrements une fois ajoutés :

### Tables temporaires

Il est possible de créer des variables de type table, qui seront effacées à la fin de leurs scripts. On les appelle "tables temporaires" :
Exemple avec paramètre nommé :
Si le nom de la colonne ne correspond pas au nom du champ sélectionné, la table temporaire se voit ajouter une colonne du nom de ce champ. Ex :
Toutes les tables temporaires sont supprimées à la fin de la connexion MySQL qui les a créée.

### Copier une table

Pour obtenir la même structure (noms et types des champs, index, mais aucun enregistrement) :
Pour dupliquer le contenu d'une table dans le résultat :
Pour avoir le code de création :

## ALTER TABLE

ALTER TABLE sert à ajouter, supprimer ou modifier la structure des tables (colonnes, index, propriétés).

### Modifier une colonne

Pour changer les caractéristiques :

#### Renommer une colonne

Pour renommer une colonne :

### Renommer une table

Pour renommer une table, il faut préalablement retirer ses privilèges avec ALTER et DROP, puis CREATE et INSERT pour ceux à attribuer à la nouvelle table.
La différence entre ALTER TABLE et RENAME est que seul le premier peut renommer les tables temporaires, mais il n'en permet qu'un par requête.

## CASCADE

Certains enregistrements d'une base de données relationnelle peuvent devenir inutiles si ceux qui leur sont joints viennent à disparaitre.
C'est par exemple le cas dans une table "adresse de facturation" où il n'y n'aurait plus de personne physique ou morale associée, c'est-à-dire qu'il existerait en mémoire une ligne avec un id utilisateur pointant vers une ligne de la table "utilisateur" qui n'existe plus.
Pour éviter d'avoir à maintenir ces reliquats, MySQL offre la possibilité de les supprimer automatiquement "en cascade", au moment où ceux qui leur sont joints sont effacés. Cela se définit par dessus la contrainte d'intégrité FOREIGN KEY.
Exemple :

## Unique

Une autre contrainte d'intégrité qui permet de forcer chaque valeur d'un champ à être différentes est UNIQUE :
Pour ajouter une contrainte unique du nom de la colonne concernée :
Pour ajouter une contrainte unique nommée :
La différence entre les deux est la requête utilisée pour l'ajouter.
Pour un groupe de champs unique (clé composite) :
L'index unique est déjà un index, donc inutile d'ajouter ADD INDEX sur le même champ de même type en plus.

## Exemple pour travaux pratiques

Soit l'exemple suivant qui sera utilisé pour les sélections ensuite (toute ressemblance avec un framework connu est purement non fortuite : si vous avez déjà votre propre wiki, il est possible de sauter cette phase pour passer directement au paragraphe SELECT).
NB : le type VARBINARY est équivalent à VARCHAR, mais il faut savoir qu'il stocke la chaine de caractères sous sa forme binaire, et donc prend moins de place.
Création d'une base

## Références

* ↑ http://stackoverflow.com/questions/6645818/how-to-automate-database-backup-using-phpmyadmin
* ↑ https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
* ↑ https://sql.sh/1396-nom-table-colonne
* ↑ http://www.mysqltutorial.org/mysql-temporary-table/
* ↑ http://dev.mysql.com/doc/refman/5.7/en/table-size-limit.html
* ↑ https://board.phpbuilder.com/d/10271323-phppgadmin-add-unique-index-vs-add-unique-key
* ↑ https://stackoverflow.com/questions/9393234/mysql-unique-field-needs-to-be-an-index

# Manipulation de données
## INSERT

La syntaxe est la suivante :
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) :
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

Exemples :
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.

## DELETE et TRUNCATE

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.
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) :
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 :
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.

## Références

* ↑ https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
* ↑ http://dev.mysql.com/doc/refman/5.5/en/replace.html
* ↑ https://dev.mysql.com/doc/refman/5.7/en/delete.html
* ↑ https://www.electrictoolbox.com/article/mysql/cross-table-delete/

# Requêtes
## SELECT

La syntaxe de sélection est la suivante (chaque clause fera l'objet d'un paragraphe explicatif ensuite) :

### Liste de champs

Il faut spécifier les données à récupérer avec SELECT :
L'étoile permet d'obtenir tous les champs d'une table :
Mais il est plutôt conseillé de nommer chaque champ (projection) pour accélérer la requête.

### Noms des tables

Pour récupérer les champs d'une table ou d'une vue, il faut la placer dans la clause FROM :
Autres exemples :

### WHERE

Cette clause permet de filtrer les enregistrements. Prenons pas exemple celui ou ceux dont le champ identifiant est égal à 42 :
Ou bien ceux qui ne sont pas nuls :
Il est impossible d'utiliser le résultat d'une fonction calculée dans le SELECT dans le WHERE, car ce résultat n'est trouvé qu'à la fin de l'exécution, donc WHERE ne peut pas s'en servir au moment prévu.
Pour ce faire il convient d'utiliser HAVING (voir-ensuite)

### GROUP BY

Quand plusieurs enregistrements sont identiques dans le résultat, qu'ils ont les mêmes valeurs dans leurs champs sélectionnés, ils peuvent être groupés en une seule ligne.
Par exemple, en regroupant les enregistrements de la table utilisateurs sur le champ de date d'inscription au wiki, on peut obtenir pour chacune le nombre d'édition maximum, minimum et leurs moyennes :
Idem mais classé par nom et prénom d'utilisateur :
Cette instruction permet donc de réaliser des transpositions lignes en colonnes. Par exemple pour afficher les utilisateurs connus comme ayant le même e-mail :
Si on place le MAX dans le CASE la transposition ne s'effectue pas.
Voir aussi GROUP_CONCAT pour transposer les colonnes en lignes.

#### WITH ROLLUP

Ajouter cette clause à un GROUP BY permet d'ajouter le résultat d'une super-agrégation reprenant l'ensemble des résultats.

### HAVING

HAVING déclare un filtre valable uniquement pour les enregistrements de la clause GROUP BY, ce qui le distingue de WHERE qui lui opère avant GROUP BY.
HAVING n'est pas optimisé et ne peut pas utiliser les index.
Voici un exemple d'erreur d'optimisation classique : l'ordonnancement des opérations ne filtre le gros des résultats (valeur admin) qu'à la fin de la requête (utilisant plus de mémoire, donc plus de temps qu'avec un WHERE) :
Par contre, cet exemple ne peut pas être optimisé car le HAVING utilise le résultat du MAX calculé après le GROUP BY :

### ORDER BY

Il est possible de classer les résultat, par ordre croissant ou décroissant, des nombres ou des lettres.
Par défaut, l'ordre est ASCENDING (croissant). Pour le décroissant il faut donc préciser DESCENDING :
Les valeurs NULL sont considérées comme inférieures aux autres.
Il est également possible de nommer la colonne à classer par son numéro :
Les expressions SQL sont autorisées :
La fonction RAND classe de façon aléatoire :
Quand un GROUP BY est spécifié, les résultats sont classés selon les champs qui y sont nommés, sauf avant un ORDER BY. Donc l'ordre décroissant peut aussi être précisé depuis le GROUP BY :
Pour éviter ce classement automatique du GROUP BY, utiliser ORDER BY NULL :

### LIMIT

Le nombre maximum d'enregistrements dans le résultat est facultatif, on l'indique avec le mot LIMIT :
Ce résultat retourne donc entre 0 et 10 lignes.
Généralement cela s'emploie après un ORDER BY pour avoir les maximums et minimums, mais voici un exemple pour en avoir trois au hasard :
Il est possible de définir une plage d’enregistrements, sachant que le premier est le numéro zéro :
On peut donc paginer les requêtes dont les résultats peuvent saturer le serveur :
La seconde commande est équivalente à celle-ci :
Une astuce consiste à déboguer la syntaxe de sa requête rapidement en lui demandant un résultat vide, et observer ainsi s'il y a des messages d'erreur sans attendre :

#### Conseils d'optimisation

* SQL_CALC_FOUND_ROWS peut accélérer les requêtes.
* LIMIT est particulièrement pratique dans des SELECT avec ORDER BY, DISTINCT et GROUP BY, car leurs calculs n'impliquent pas toutes les lignes.
* Si la requête est résolue par le serveur en copiant les résultats dans une table temporaire, LIMIT aide MySQL à calculer combien de mémoire est requise par la table.

### DISTINCT

Le mot DISTINCT peut être utilisé pour supprimer les doublons des lignes du résultat :
Cela permet par exemple de récupérer la liste de toutes les valeurs différentes d'un champ :
On peut également en sortir les différentes combinaisons de valeurs :
La fonction COUNT a un comportement différent du GROUP BY :
* SELECT COUNT(*) FROM maTable GROUP BY monChamp) : renvoie une ligne pour chaque valeur du champ, avec le nombre d'occurrences dans la table de chacune.
* SELECT COUNT(monChamp) FROM maTable GROUP BY monChamp) : idem mais sans compter les NULL (ils apparaissent avec un total à 0).
* SELECT COUNT(DISTINCT monChamp) FROM maTable : le nombre de valeurs différentes du champ (c'est-à-dire le nombre de lignes des requêtes précédentes).
* SELECT DISTINCT COUNT(*) FROM maTable : le nombre de lignes dans la table.
* SELECT DISTINCT COUNT(monChamp) FROM maTable : le nombre de lignes dans la table sans compter celles où monChamp est NULL.

### IN and NOT IN

Équivalent du signe =, qui ne nécessite pas d'être répété quand il concerne plusieurs valeurs :
Pour améliorer les performances du NOT IN (1, 2), il est généralement préférable d'utiliser NOT EXISTS (select 1, 2).

### EXISTS

Fonction disponible depuis MySQL 4.

### UNION et UNION All

Compatible MySQL 4 et plus. L'union de sélections nécessite qu'elles aient le même nombre de colonnes.
La requête suivante renvoie tous les enregistrements de deux tables :
UNION est équivalent à UNION DISTINCT, ce qui le distingue de UNION ALL qui ne filtre pas les doublons.

## JOIN

Les relations entre les tables permettent de joindre intelligemment leurs résultats. La jointure naturelle est la plus rapide sur la plupart des plateformes SQL.
L'exemple suivant compare les nombres en anglais et en hindi.
| | select * from english | | select * from hindi |
| Tag | Inenglish | Tag | Inhindi |
| 1 | One | 2 | Do |
| 2 | Two | 3 | Teen |
| 3 | Three | 4 | Char |

### INNER JOIN

| Tag | Inenglish | Inhindi |
| 2 | Two | Do |
| 3 | Three | Teen |
La jointure cartésienne décrit le cas où chaque ligne d'une table est jointe à toutes celles d'une autre.
3*3 = 9 lignes :
| Tag | Inenglish | Tag | Inhindi |
| 1 | One | 2 | Do |
| 2 | Two | 2 | Do |
| 3 | Three | 2 | Do |
| 1 | One | 3 | Teen |
| 2 | Two | 3 | Teen |
| 3 | Three | 3 | Teen |
| 1 | One | 4 | Char |
| 2 | Two | 4 | Char |
| 3 | Three | 4 | Char |

### NATURAL JOIN

La jointure naturelle équivaut à INNER JOIN sur toutes les colonnes communes des deux tables.

### USING

Le mot USING est compatible MySQL 4, mais change avec MySQL 5. La requête suivante est équivalente à celles INNER JOIN ci-dessus :

### OUTER JOIN

| Tag | Inenglish | Tag | Inhindi |
| 1 | One | | |
| 2 | Two | 2 | Do |
| 3 | Three | 3 | Teen |
| | | 4 | Char |

### RIGHT OUTER JOIN

* S'assurer que le type des clés de jointes est le même dans les deux tables.
* Les mots clés LEFT et RIGHT ne sont pas absolus, ils opèrent selon le contexte : en intervertissant les tables le résultat sera identique.
* La jointure par défaut est INNER JOIN (pas OUTER).

### FULL OUTER JOIN

MySQL et MariaDB n'ont pas de jointure FULL OUTER JOIN. Voici comment l'émuler :

### Jointures multiples

Il est possible de joindre plus de deux tables :
Exemple :

## Sous requêtes

Compatible MySQL 4.1 et plus.
* Les sous-requêtes SQL permettent aux résultats d'une requête d'être utilisés par une autre.
* Elles apparaissent toujours comme une partie de clause WHERE ou HAVING.
* Seul un champ peut être dans la sous-requête SELECT.
* Les ORDER BY ne sont donc pas autorisés (inutiles sur une seule colonne).
Par exemple, la "table" RepOffice = OfficeNbr from Offices, liste les bureaux où le quota de vente excède la somme des quotas des vendeurs individuels :
??? est la somme des quotas des vendeurs.
En combinant ces deux requêtes, les points d'interrogations disparaissent :
Par exemple, tous les clients avec des commandes ou limites de crédits > 50000 €. En utilisant le mot DISTINCT pour ne lister les clients qu'une seule fois :
* ON : filtre les lignes d'une seule table.
* WHERE : filtre les lignes de toutes les tables.
* HAVING : filtre les lignes de toutes les tables après regroupements.
Les sous-requêtes ne peuvent pas faire référence à un élément de la requête qui les contient. Si c'est nécessaire, il faut les transformer en jointures : ... JOIN (SELECT...) q ON q.id = ...
Exemple :

## References

* ↑ https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html
* ↑ http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
* ↑ http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
* ↑ https://stackoverflow.com/questions/173041/not-in-vs-not-exists
* ↑ https://dev.mysql.com/doc/refman/5.7/en/join.html
* ↑ https://mariadb.com/kb/en/with/
* Official MySQL documentation

# NULL
## Description

De nombreux langages de programmation ont deux valeurs logiques : True et False. SQL en possède une troisième pour les valeurs inconnues : NULL.
NULL étant une absence de valeur, il peut être assigné à des colonnes TEXT, INTEGER ou autres. Toutefois une colonne déclarée NOT NULL ne pourra pas en contenir.
NULL ne doit pas être entouré d'apostrophes ou de guillemets, ou bien il désignera une chaine de caractères contenant son nom.
L'exemple ci-dessous peut sélectionner des chanteurs avec prénom de taille zéro (""), par exemple pour Sting et Homer. Il vérifie si la date de naissance est nulle :
Les enregistrements X à NULL ne sont pas renvoyés par un WHERE X != 'Y'
COUNT ne tient pas compte des NULL :
Par ailleurs, SUM(NULL) renvoie NULL.
Les opérations normales (comparaisons, expressions...) renvoient NULL si au moins un des éléments comparés est NULL :
Deux valeurs inconnues ne sont donc pas égales (NULL=NULL renvoie NULL).

## Gérer NULL

La fonction COALESCE peut simplifier le travail avec NULL.
Par exemple, pour éviter de montrer les valeurs nulles en les traitant comme des zéros :
Dans un champ date, les traiter comme celle actuelle :
La fonction coalesce prévient des problèmes de calcul logarithmique d'une valeur nulle, et peut être optionnelle selon les circonstances.
IFNULL dans un SELECT fait de NULL n'importe quelle valeur désirée.
Si expr1 n'est pas nulle, IFNULL renvoie expr1, sinon expr2.
IFNULL renvoie une chaine ou un nombre, selon le contexte :
Attention aux résultats peu prévisibles, par exemple la requête suivante efface toutes les entrées :
Pour obtenir les NULL en dernier lors d'un ORDER BY :
Enfin, pour déterminer les champs d'une table qui ne peuvent pas être nuls :

# Opérateurs

MySQL propose plus que les standards des opérateurs SQL. Ils peuvent être utilisés pour rédiger des expressions contenant des constantes, variables, valeurs contenues dans des champs ou autres expressions.

## Opérateurs d'assignation

L'opérateur = peut assigner une valeur à une colonne :
Par contre pour assigner une valeur à une variable, l'opérateur est :=, car = est déjà utilisé pour la comparaison.
SELECT INTO peut aussi remplir les variables.

## Opérateurs de comparaison
### Égalité

Pour vérifier si deux valeurs sont égales, utiliser = :
Pour vérifier si deux valeurs sont différentes, c'est <> ou != :

### Comparaison IS NULL

Pour savoir si une valeur est nulle, utiliser IS :
Pour savoir si une valeur n'est pas nulle :
Il existe par ailleurs l'opérateur vaisseau spatial : <=>, qui considère NULL comme une valeur normale :

### Comparaison IS booléen

IS et IS NOT fonctionnent aussi avec TRUE, FALSE et UNKNOWN (qui est purement un synonyme de NULL).

### Plus grand et plus petit que

Avec des nombres :
Avec du texte dans l'ordre alphabétique :
Cet ordre alphabétique est défini par COLLATION (l'interclassement), pour un CHARACTER SET donné. Par exemple, une COLLATION peut être sensible à la casse ou pas (suffixe utf8_general_cs = case sensitive, utf8_general_ci = case insensitive).
Exemple :

### BETWEEN

l’opérateur BETWEEN ... AND ... permet de vérifier si une valeur appartient à une plage (bornes incluses) :

### IN

IN permet de s'assurer si une valeur est dans une liste :
Attention : si la liste contient des nombres et des chaines, il faut tout mettre entre apostrophe pour obtenir le résultat escompté.
Il n'y a aucune limite théorique au nombre de valeurs de la liste.

## Opérateurs logiques
### Booléens logiques

MySQL n'a pas vraiment de type BOOLEAN.
FALSE est un synonyme de 0. Les chaines vides sont considérées FALSE.
TRUE est un synonyme de 1. Tout ce qui n'est ni FALSE, ni NULL est considéré TRUE.
UNKNOWN est un synonyme de NULL. La date spéciale 0/0/0 est nulle.

### NOT

NOT est le seul opérateur qui n'a qu'une seule opérande. Il renvoie 0 si l'opérande est TRUE, 1 si elle est FALSE, et NULL si elle est NULL.
! est synonyme de NOT.

### AND

AND renvoie 1 si les deux opérandes sont TRUE, sinon 0 ; si au moins l'une des deux opérandes est nulle, il renvoie NULL.
&& est synonyme de AND.

### OR

OR renvoie TRUE si au moins une des opérandes est TRUE, sinon FALSE ; si les deux opérandes sont nulles, il renvoie NULL.
| est un synonyme de OR.

### XOR

XOR (ou exclusif) renvoie :
* 1 si une seule des deux opérandes est TRUE et l'autre FALSE.
* 0 si les deux sont TRUE ou FALSE.
* NULL si au moins l'une des deux est NULL.

## Opérateurs arithmétiques
### Divisions

Renvoie un nombre de type FLOAT :
Pour retourner la valeur entière du résultat d'une division sous forme de type INTEGER, utiliser DIV :
Le reste de la division (modulo) se trouve avec '%' ou MOD :

### Utiliser + pour convertir des données

Pour convertir un INTEGER en FLOAT :
Il est impossible de convertir une valeur FLOAT en ajoutant 0.0, mais on peut forcer le type en INTEGER :

### ROUND

Pour arrondit en définissant le nombre de chiffres après la virgule. Ex :

## Opérateurs de texte

Il n'y a pas d'opérateurs de concaténation en MySQL. Les opérateurs arithmétiques convertissent les valeurs en nombres et pour leurs opérations, donc la concaténation avec + est impossible.
La fonction CONCAT pallie à cela.

### LIKE

L'opérateur LIKE si la chaîne recherchée est inclue dans une colonne :
Généralement cette chaine est sensible à la casse, mais il y a deux exceptions, quand :
* une comparaison LIKE touche une colonne déclarée en BINARY ;
* l'expression contient une clause BINARY :
Les comparaisons LIKE acceptent deux caractères spéciaux :
* _ : n'importe quel caractère (un seul, ni zéro ni deux).
* % : n'importe quel séquence de caractères (par exemple zéro ou mille).
À noter que dans les expressions LIKE, \ est aussi le caractère d'échappement pour ', et son comportement ne peut pas être changé par la clause ESCAPE. Il peut aussi échapper d'autres caractères, mais pas lui-même.
Utilisations courantes de LIKE :
* Trouver tous les titres commençant par "hello" :
* Trouver tous les titres finissant par "world" :
* Trouver tous les titres contenant la chaine "gnu" :
Ces caractères spéciaux peuvent être contenus dans le pattern lui-même. Par exemple, pour rechercher les symboles _ ou % dans la base :
/ peut-être une alternative à \ si on le précise :
Quand on utilise l'opérateur =, les espaces des chaines sont ignorés, mais avec LIKE ils sont reconnus :
De même, contrairement à "=", "LIKE" compare uniquement les caractères, même si leurs règles d'interclassement les regroupent :
LIKE fonctionne aussi avec les nombres :
Pour tester si un pattern ne fonctionne pas alors qu'il devrait, utiliser NOT LIKE :

### SOUNDS LIKE

SOUNDS LIKE permet de vérifier si deux textes se prononcent pareils. Il utilise l'algorithme SOUNDEX, basé sur les règles de l'anglais, et peut s'avérer assez approximatif :
SOUNDS LIKE est une extension apparue depuis MySQL 4.1.

## Opérateur bit à bit

Il existe des opérateurs pour les opérations bit à bit.

## Conditions
### IF

La structure IF ... THEN ... ELSE ... END IF; ne fonctionne que dans les procédures stockées (contenant plusieurs requêtes). Pour gérer une condition en dehors d'elles, on peut utiliser : IF(condition, siVraie, siFausse);.
Exemple : SELECT IF(-1 < 0, 0, 1); renvoie 0.
Exemple avec plusieurs conditions (switch) :

### CASE

SELECT CASE WHEN condition THEN siVraie ELSE siFausse END;
Exemple : SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END; renvoie 0.
Exemple avec plusieurs conditions :
Dans une seule requête :

## Précédence
### Précédence des opérateurs

Du plus au moins prioritaire :
Modificateurs :
* PIPES_AS_CONCAT : si activé, | est prioritaire sur ^, mais - et ~ le reste sur |.
* HIGH_NOT_PRECEDENCE : si activé, NOT est au niveau de !.

### Utilisation des parenthèses

Tout comme en mathématiques, les parenthèses permettent d'évaluer des sous-expressions avant d'autres :
Cela peut aussi se faire pour rendre les requêtes plus lisibles aux humains :

## Références

* ↑ https://www.bennadel.com/blog/3918-performing-a-case-sensitive-search-on-a-case-insensitive-column-using-collate-in-mysql-5-6-49.htm
* ↑ https://docs.oracle.com/cd/E17952_01/mysql-5.0-en/string-comparison-functions.html
* ↑ http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html
* ↑ https://dev.mysql.com/doc/refman/5.7/en/if.html
* ↑ https://dev.mysql.com/doc/refman/5.7/en/case.html
* ↑ https://dev.mysql.com/doc/refman/5.7/en/case.html

# Fonctions
## Syntaxe

À l'instar des mots réservés SQL, les noms des fonctions ne sont pas sensibles à la casse :
Si le SQL_MODE IGNORE_SPACE SQL_MODE n'est pas défini, il est impossible de placer un espace entre le no de la fonction et la première parenthèse, sous peine de voir une erreur 1064. IGNORE_SPACE est généralement à 0, car cela accélère le parseur. Donc :
Toutefois, cette restriction ne s'applique qu'aux fonctions natives de MySQL (pas aux procédures stockées).

## Fonctions générales

Fonctions qui dépendent du type.

### BENCHMARK(nombre, expression)

Exécute l'expression n fois et retourne toujours zéro, le chiffre pertinent est donc le temps pris par cette opération de simulation. Utile pour trouver les goulots d'étranglement des expressions SQL :

### CAST(valeur AS type)

Renvoie la valeur convertie en chaine de caractères, comme les apostrophes.
Exemple avec encodage :

### CHARSET(chaine)

Renvoie le type de caractères de la chaine :

### COALESCE(valeur, ...)

Renvoie le premier paramètre non nul. S'ils sont tous nuls, renvoie NULL.

### COERCIBILITY(chaine)

Renvoie la coercibility d'une chaine (entre 0 et 5) :
| Coercibility | Signification | Exemple |
| 0 | Explicit collation | Value with COLLATE clause |
| 1 | No collation | Concatenation of strings with different collations |
| 2 | Implicit collation | Column value |
| 3 | System constant | USER return value |
| 4 | Coercible | Literal string |
| 5 | Ignorable | NULL or an expression derived from NULL |

### COLLATION(chaine)

Renvoie la collation d'une chaine :
Pour obtenir celle par défaut d'une base :

### CONCAT

Concaténation sans séparateur :

### CONCAT_WS

Concaténation avec séparateur de son choix :

### CONNECTION_ID

Renvoie l'identifiant du thread courant :

### CONVERT(valeur, type)

Tout comme CAST, retourne la valeur convertie dans le type mentionné :

### CONVERT(chaine USING charset)

Convertit la chaine string passée dans le CHARACTER SET spécifié :

### CURRENT_USER

Retourne les noms de l'utilisateur et de l'hôte courants :

### DATABASE

Retourne le nom de la base de données courante :

### FOUND_ROWS

Après un SELECT avec une LIMIT et le mot clé SQL_CALC_FOUND_ROWS, il est possible de lancer un autre SELECT avec FOUND_ROWS. En effet il renvoie le nombre de ligne de la clause précédente, sans la limite :

### GREATEST(valeur1, valeur2, ...)

Renvoie la plus grande valeur des paramètres :

### INTERVAL(valeur1, valeur2, valeur3, ...)

Renvoie l'emplacement du premier argument supérieur au premier, en partant du zéro dans la liste des entiers en paramètres :

### IF(valeur1, valeur2, valeur3)

If valeur1 est vraie, renvoie valeur2, sinon (fausse ou nulle) renvoie valeur3.

### IFNULL(valeur1, valeur2)

Si valeur1 est nulle, renvoie valeur2, sinon valeur1.

### ISNULL(valeur)

Si la valeur passée est nulle, renvoie 1, sinon 0.

### NULLIF(valeur1, valeur2)

Renvoie NULL si valeur1 = valeur2, sinon valeur1.

### LAST_INSERT_ID

Renvoie le dernier ID en AUTO_INCREMENT inséré dans la base, ce qui évite un SELECT lorsque l'on a besoin d'insérer deux enregistrements dont la deuxième avec une clé étrangère vers la première.

### LEAST(valeur1, valeur2, ...)

Renvoie la plus petite valeur dans la liste des paramètres passés :

### LENGTH(chaine)

Affiche la taille d'une chaine en octets.
Pour avoir le nombre de caractères, utiliser CHAR_LENGTH.

### SUBSTR(chaine, début, taille)

Découpe une chaine de caractère :
Son alias "substring" fonctionne aussi.

### SUBSTRING_INDEX

Découpe une chaine selon un séparateur. Le troisième paramètre désigne la énième occurrence de ce dernier :

### Fonctions JSON

MySQL (et MariaDB) offrent plusieurs fonctions de manipulation de champ varchar contenant du JSON.

#### JSON_VALID

Renvoie vrai si le champ est un JSON valide, faux sinon.

#### JSON_UNQUOTE

Retire les guillemets au début et à la fin d'une chaine (pour l'utiliser dans un JSON sans qu'ils soient interprétés). Ex :

#### JSON_KEYS

Renvoie toutes les clés du JSON en paramètre.

#### JSON_EXTRACT

Extrait un morceau du JSON par sa clé. Ex :
Cette fonction est combinable avec JSON_UNQUOTE pour les recherches dans des données JSON.. Ex :

#### JSON_SEARCH

Retourne les clés correspondant à la valeur recherchée. Son deuxième paramètre peut être "one" pour la première clé, ou "all" pour toutes. Ex :

#### JSON_VALUE

Retourne la valeur à partir de la clé en paramètre.

#### JSON_CONTAINS

Renvoie 1 si la JSON contient la valeur en paramètre, ou 0 sinon.

#### JSON_REMOVE

Retire la paire clé/valeur de la clé passée en paramètre.

## Date et heure

Il existe des dizaines de fonctions liées aux dates.
Pour trouver la date de l'an dernier :
Sélectionner toutes les pages du wiki non lues depuis plus un an :
Autres exemples de sélections :
convert('17/02/2016 15:49:03',datetime) ou convert('17-02-2016 15:49:03',datetime) donne null, donc une requête d'insertion le remplace par le même résultat que now. La syntaxe doit être convert('2016-02-17 15:49:03',datetime) ou convert('2016/02/17 15:49:03',datetime).

### DATE_ADD

Pour additionner deux dates. Par exemple pour calculer le jour d'une livraison prenant 48 h :
Pour la date d'hier :
Les unités à additionner ou soustraire les plus courantes sont :

### DATEDIFF

Pour soustraire une date à une autre. Par exemple pour calculer un âge :

### STR_TO_DATE

Un BETWEEN ou ">" de champ date avec une chaine de caractères ne marche pas. Il faut donc convertir la chaine en date :

## Fonctions d'agrégation
### COUNT(champ)

Si le paramètre est "*" au lieu d'un nom de colonne, COUNT renvoie les nombre de lignes total de la requête. Cela peut permettre de savoir combien de lignes possède une table, par exemple le nombre de pages d'un wiki :
Si le mot DISTINCT est employé, cela ignore les doublons :
Si le nom d'un champ est précisé, cela renvoie le nombre de valeurs non nulles :
Cela fonctionne aussi pour des expressions, des combinaisons de champs :
Pour afficher le décompte de plusieurs tables non jointes :

### MAX(champ)

MAX renvoie la valeur maximum d'une expression issue du résultat d'une requête, ou NULL s'il n'y en a pas :

#### Alternatives

Selon le contexte, la fonction MAX n'est pas toujours la meilleure option pour obtenir un maximum. Par exemple en cas de sous-requêtes ou sans agrégation possible :
* SELECT `user_editcount` FROM `wiki1_user` ORDER BY user_editcount DESC LIMIT 1;
* SELECT `user_editcount` FROM `wiki1_user` wu1 LEFT JOIN `wiki1_user` wu2 ON wu1.user_editcount > wu2.user_editcount WHERE wu2.user_editcount is null;

### MIN(champ)

MIN renvoie la valeur minimum d'une expression issue du résultat d'une requête, ou NULL s'il n'y en a pas :

### AVG(champ)

AVG renvoie la valeur moyenne d'une expression, ou NULL s'il n'y en a pas :
* Moyenne des valeurs d'un champ entier :
* Moyenne des valeurs d'un champ date :
* Moyenne d'un total obtenu par COUNT :

### SUM(champ)

SUM dresse la somme des valeurs d'une expression, ou NULL s'il n'y en a pas.
Si SUM(DISTINCT expression) est utilisé, les valeurs identiques ne sont ajoutées qu'une seule fois. Il a été ajouté après MySQL 5.1.
Cette fonction est impactée quand on ajoute des LEFT JOIN dans la même requête. Il faut alors les séparer dans des sous-requêtes.

### GROUP_CONCAT(champ)

GROUP_CONCAT concatène les valeurs de tous les enregistrements d'un groupe dans une seule chaine séparée par une virgule par défaut. En effet, le deuxième paramètre facultatif permet de définir un autre séparateur.

### Fonctions d'agrégation de bit

Syntaxe générale :
Ces fonctions bit à bit calculent expression pour chaque ligne du résultat et entre les expressions. La précision est de 64 bit.

#### OR

(retourne 0 s'il n'y a aucun résultat)

#### XOR

(retourne 0 s'il n'y a aucun résultat)

## Références

* ↑ http://dev.mysql.com/doc/refman/5.0/fr/information-functions.html
* ↑ https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html
* ↑ http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_coercibility
* ↑ https://mariadb.com/kb/en/json_search/
* ↑ https://www.startutorial.com/articles/view/how-to-search-json-data-in-mysql
* ↑ https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
* ↑ https://www.w3schools.com/sql/func_mysql_date_add.asp

# Procédures stockées

MySQL peut enregistrer des requêtes pour les rappeler comme les fonctions d'un programme. Elles peuvent intégrer des contrôles de flux, des boucles et des curseurs. Il en existe trois sortes :
* Fonctions et procédures stockées : programmes invocables depuis les commandes SQL ;
* Déclencheurs (ou triggers) : programmes qui se déclenchent avant ou après un évènement impliquant une table (DELETE, INSERT, UPDATE) ;
* Évènements : programmes exécutés à une certaine date, régulièrement.
Les futures versions de MySQL pourraient même stocker des procédures écrites dans d'autres langages que SQL.

## Délimiteur

MySQL utilise un caractère comme délimiteur pour séparer ses requêtes, par défaut ';'. Quand on crée des procédures stockées avec plusieurs requêtes, on en crée en fait une seule : CREATE de la procédure. Toutefois, si elles sont séparées par ';', il faut demander à MySQL de les ignorer pour estimer la fin du CREATE, puis remettre ";" à la fin.
Dans l'exemple suivant, '|' joue ce rôle :

## Procédures stockées

Il en existe deux types :
* FUNCTION si elles retournent un résultat. Elles sont appelées avec SELECT.
* PROCEDURE si elles ne retournent rien après leur traitement. Elles sont exécutées avec CALL.

### Gestion des PROCEDURE et FUNCTION
#### CREATE FUNCTION

Création de fonction. Exemple sans paramètre :
Pour la rendre plus lisible, on peut faire exactement la même chose sur plusieurs lignes, mais comme elle peut contenir plusieurs instructions, cela nécessite d'indiquer le début et la fin de la procédure avec BEGIN et END :
Avec paramètre :

#### CREATE PROCEDURE

Création de procédure stockée :
Pour affiner les permissions, la création ci-dessous est liée à un compte :
Toutefois, s'il est supprimé elle ne fonctionne plus. Pour éviter cela, on peut la rattacher aux comptes qui vont l'exécuter :

#### CALL

Invocation :

#### DROP PROCEDURE

Suppression :

#### Modification

On est obligé de supprimer et de recréer le module :

### Métadonnées des PROCEDURE et FUNCTION
#### SHOW CREATE FUNCTION / PROCEDURE

Pour obtenir la requête SQL qui avait créé la procédure :

#### INFORMATION_SCHEMA.ROUTINES

La base virtuelle INFORMATION_SCHEMA a une table `ROUTINES` avec les informations des procédures et fonctions.

#### INFORMATION_SCHEMA.PARAMETERS

Cette table contient toutes les valeurs des fonctions stockées.

## Déclencheurs
### Gestion des TRIGGER

Disponibles depuis MySQL 5.0.2, ils fonctionnent sur les tables persistantes, mais pas les temporaires.

#### CREATE TRIGGER

Cet exemple est une requête DELETE appelée `effacer_ancien`, qui se lance après qu'un nouvel enregistrement soit inséré dans la table. Si un INSERT ajoute plusieurs lignes à une table, le déclencheur est appelé plusieurs fois.
Les conditions de déclenchement des triggers doivent être des commandes LMD basiques :
* INSERT, dont LOAD DATA et REPLACE ;
* DELETE, incluant REPLACE, mais pas TRUNCATE ;
* UPDATE
Un cas particulier est INSERT ... ON DUPLICATE KEY UPDATE. Si INSERT est exécuté, BEFORE INSERT ou AFTER INSERT sont exécutés. Si UPDATE est exécuté à la place de INSERT, l'ordre des évènements est le suivant : BEFORE INSERT, BEFORE UPDATE, AFTER UPDATE.
Le déclencheur peut aussi s'appliquer à une table en particulier :
Les noms des triggers doivent être unique pour chaque base.
Contrairement au standard SQL, tous les déclencheurs sont exécutés FOR EACH ROW, et non pour chaque commande.
Une procédure stockée doit être spécifiée entre les mots BEGIN et END sauf s'il ne contient qu'une seule commande. Le SQL dynamique ne peut pas y être utilisé (PREPARE) ; Une autre procédure stockée peut être appelée à la place.
Il est posible d'accéder à l'ancienne valeur d'un champ (avant l'exécution de la procédure) et à la nouvelle valeur :

#### DROP TRIGGER

Pour supprimer un déclencheur :
Pour modifier un trigger, il faut le supprimer puis le recréer.

### Métadonnées des TRIGGER
#### SHOW CREATE TRIGGER

Disponible depuis MySQL 5.1. Affiche la commande pour recréer un déclencheur nommé :
* Trigger : Nom du déclencheur.
* sql_mode : valeur du SQL_MODE au moment de l'exécution.
* SQL Original Statement
* character_set_client
* collation_connection
* Database Collation

#### SHOW TRIGGERS

Pour obtenir la liste des triggers de la base courante :
Pour obtenir la liste des triggers d'une autre base :
D'autres filtres sont possibles :
Les colonnes du déclencheur sont :
* Trigger : nom
* Event : commande SQL qui le déclenche
* Table : table associée
* Statement : requête exécutée
* Timing : BEFORE ou AFTER
* Created : toujours NULL
* sql_mode : SQL_MODE définit lors de sa création
* Definer : créateur
* character_set_client : valeur de la variable `character_set_client` lors de la création
* collation_connection : valeur de la variable `collation_connection` lors de la création
* Database Collation : COLLATION utilisée par la base du trigger.

#### INFORMATION_SCHEMA.TRIGGERS

La base virtuelle INFORMATION_SCHEMA a une table `TRIGGERS` avec les colonnes suivantes :
* TRIGGER_CATALOG : catalogue contenant le trigger ;
* TRIGGER_SCHEMA : SCHEMA (DATABASE) contenant le trigger ;
* TRIGGER_NAME : nom du trigger ;
* EVENT_MANIPULATION : INSERT, UPDATE ou DELETE ;
* EVENT_OBJECT_CATALOG : pas encore implémenté ;
* EVENT_OBJECT_SCHEMA : schéma contenant la table associée au trigger ;
* EVENT_OBJECT_NAME : nom de la table associée au trigger ;
* ACTION_ORDER : pas encore implémenté ;
* ACTION_CONDITION : pas encore implémenté ;
* ACTION_STATEMENT : commande exécutée lors de l'activation du trigger ;
* ACTION_ORIENTATION : pas encore implémenté ;
* ACTION_TIMING : BEFORE ou AFTER ;
* ACTION_REFERENCE_OLD_TABLE : pas encore implémenté ;
* ACTION_REFERENCE_NEW_TABLE : pas encore implémenté ;
* ACTION_REFERENCE_OLD_ROW : pas encore implémenté ;
* ACTION_REFERENCE_NEW_ROW : pas encore implémenté ;
* CREATED : date et heure de création (pas encore implémenté) ;
* SQL_MODE : SQL_MODE valide pour l'exécution du trigger ;
* DEFINER : créateur du trigger, sous la forme 'utilisateur@hôte' ;
* CHARACTER_SET_CLIENT : valeur de la variable `character_set_client` lors de la création ;
* COLLATION_CONNECTION : valeur de la variable `collation_connection` lors de la création ;
* DATABASE_COLLATION : COLLATION utilisée par la base.

## Évènements

Les évènements sont aussi appelé en anglais Scheduled Events ou Temporal Triggers. Ils sont planifiés pour s'exécuter à un moment donné, date ou intervalle de temps. Ils sont similaire aux crontab UNIX.
Quand un évènement est lancé, il doit être complètement exécuté. S'il est réactivé avant la fin de son exécution, une nouvelle instance du même évènement est créée. Donc il est conseillé d'utiliser LOCK pour éviter qu'ils interfèrent entre eux.
Le planificateur des évènements est un thread en permanence en exécution, afin d'être en mesure de lancer les évènements à tout moment. Il peut toutefois être désactivé en lançant MySQL avec ces options :
Ou bien en ajoutant une ligne dans le fichier de configuration (my.cnf) :
Ou encore en cours d'utilisation :
Quand il est lancé, on peut vérifier son status avec SHOW PROCESSLIST. Son utilisateur est 'event_scheduler'. Quand il est en sommeil, `State` est à 'Waiting for next activation'.

### Gestion des EVENT

Les commandes sont CREATE EVENT, ALTER EVENT, DROP EVENT.

#### CREATE EVENT

Pour un évènement à exécuter le lendemain :
Son nom est obligatoire et doit être précisé après CREATE EVENT.
Pour créer une tâche à exécuter une seule fois, utiliser AT. Pour ne pas spécifier la date et l'heure de manière absolue, mais relativement après un intervalle, utiliser AT CURRENT_TIMESTAMP + INTERVAL ....
Une tâche récurrente s'obtient avec EVERY :
On peut aussi spécifier la date et l'heure du début et/ou de la fin. La tâche sera exécutée à intervalle régulier entre ces dates :
Les unités autorisées sont :
La clause DO spécifie la commande à exécuter.
Si la tâche est composée par plus d'une commande, utiliser BEGIN ... END :
Si un EVENT du même nom existe déjà, le serveur renvoie une erreur. On peut l'éviter avec IF NOT EXISTS :
Après expiration de l'évènement, MySQL le supprimer par défaut. Pour éviter cela afin de pouvoir le réutiliser son code ultérieurement, utiliser ON COMPLETION :
On peut aussi dire explicitement à MySQL de le supprimer :
En précisant une date de lancement antérieure, l'évènement expire immédiatement après sa création, c'est pourquoi le serveur prévient avec un warning 1588, normalement.
Pour préciser si un évènement est activé lors de sa création, les mots sont ENABLE, DISABLE, DISABLE ON SLAVES (ce dernier ne se réplique pas sur les bases de données esclaves). Par défaut, il est activé :
Pour le modifier : ALTER EVENT.
On peut aussi commenter l'évènement dans une limite de 64 caractères :
Par ailleurs, on peut modifier l'utilisateur de l'évènement pour obtenir d'autres permissions. Par exemple depuis celui voulu avec CURRENT_USER :
Spécifier un autre utilisateur nécessite les droits root :

#### ALTER EVENT

Renommage d'un évènement :
On peut aussi ne définir que la clause à modifier :

#### DROP EVENT

Avec les permissions sur l'évènement à supprimer :
S'il n'existe pas l'erreur 1517 survient. Pour l'éviter :

### Métadonnées des EVENT
#### SHOW CREATE EVENT

Cette commande retourne la commande CREATE EVENT utilisée pour créer le trigger, et sur les paramètres l'impactant.
Les colonnes du résultat sont :
* Event : nom
* sql_mode : mode SQL utilisé (ex : NO_ENGINE_SUBSTITUTION)
* time_zone : fuseau horaire du créateur (ex : SYSTEM)
* Create Event : code qui a généré l'évènement
* character_set_client (ex : utf8)
* collation_connection (ex : utf8_general_ci)
* Database Collation (ex : latin1_swedish_ci)

#### SHOW EVENTS

Pour afficher tous les évènements de la base courante :
Pour une base en particulier :
Autres filtres :
Types de résultat :
* Db : nom de la base ;
* Name : nom de l'évènement ;
* Definer : créateur (user@host) ;
* Time zone : fuseau horaire ;
* Type : 'ONE TIME' ou 'RECURRING' selon la récurrence ;
* Executed At : date de l'exécution, ou NULL pour les récursifs ;
* Interval Value : nombre d'intervalle entre les exécutions, ou NULL pour les non récursifs ;
* Interval Field : unités de mesure de l'intervalle (ex : 'SECOND'), ou NULL pour les non récursifs ;
* Starts : date de première exécution, ou NULL pour les non récursifs ;
* Ends : date de dernière exécution, ou NULL pour les non récursifs ;
* Status : ENABLED, DISABLED, ou SLAVESIDE_DISABLED ;
* Originator : identifiant du serveur créateur (0 pour le courant). Disponible depuis MySQL 5.1 ;
* character_set_client
* collation_connection
* Database Collation

#### INFORMATION_SCHEMA.EVENTS

La base virtuelle INFORMATION_SCHEMA contient une table `EVENTS` depuis MySQL 5.1. Voici ses colonnes :
* EVENT_CATALOG : toujours NULL (les CATALOG ne sont pas encore implémentés par MySQL) ;
* EVENT_SCHEMA : nom de la base ;
* EVENT_NAME : nom de l'évènement ;
* DEFINER : créateur (user@host) ;
* TIME_ZONE : fuseau horaire ;
* EVENT_BODY : langage utilisé ;
* EVENT_DEFINITION : routine à exécuter ;
* EVENT_TYPE : 'ONE TIME' ou 'RECURRING' selon la récurrence ;
* EXECUTE_AT : date de l'exécution, ou NULL pour les récursifs ;
* INTERVAL_VALUE : nombre d'intervalle entre les exécutions, ou NULL pour les non récursifs ;
* INTERVAL_FIELD : unités de mesure de l'intervalle (ex : 'SECOND'), ou NULL pour les non récursifs ;
* SQL_MODE mode SQL ;
* STARTS : date de première exécution, ou NULL pour les non récursifs ;
* ENDS : date de dernière exécution, ou NULL pour les non récursifs ;
* STATUS : ENABLED, DISABLED, ou SLAVESIDE_DISABLED ;
* ON_COMPLETION : 'NOT PRESERVE' ou 'PRESERVE' ;
* CREATED : date de création ;
* LAST_ALTERED : date de dernière modification ;
* LAST_EXECUTED : date de dernière exécution ;
* EVENT_COMMENT : commentaires ;
* ORIGINATOR : identifiant du serveur créateur (0 pour le courant). Disponible depuis MySQL 5.1 ;
* character_set_client
* collation_connection
* Database Collation

### Avantages

* Elles réduisent le trafic du réseau car une seule commande permet de leur en faire exécuter plusieurs. Les appeler est donc plus rapide.
* Ces modules peuvent être invoqués plusieurs fois depuis n'importe quel langage (PHP, Java...).
* Elles conservent une logique entre les bases : le DBA peut les modifier sans toucher aux programmes qui les appellent.
* Peut permettre aux utilisateurs qui n'ont pas accès à une table de récupérer ses données ou la modifier dans certaines circonstances.

## Extensions au standard SQL
### Flow control

Les mots clés sont : IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT. Ils ne sont utilisables que dans les procédures stockées.

### Curseurs

Les curseurs permettent de traiter chaque ligne différemment, mais cela ralentit considérablement les requêtes.
Ils doivent être déclaré puis ouvert avant le début de la boucle qui traite chaque enregistrement. Pour connaitre la fin de la table parcourue, il faut crée un handler après le curseur :

### Gestion des erreurs

La déclaration d'un "handler" permet de spécifier un traitement en cas d'erreur :
De plus, le type d'erreur peut être précisé :

### mysql_affected_rows

Cette fonction renvoie le nombre de lignes impactées par la requête précédente.

## Références

* ↑ http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html
* ↑ http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html
* ↑ https://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html
* http://www.convert-in.com/mssql-to-mysql-stored-procedures.htm

# Regex

En informatique, une expression régulière ou expression rationnelle ou expression normale ou motif, est une chaîne de caractères, qui décrit, selon une syntaxe précise, un ensemble de chaînes de caractères possibles. Les expressions régulières sont également appelées regex (de l'anglais regular expression). Elles sont issues des théories mathématiques des langages formels. Les expressions régulières sont aujourd’hui utilisées pour la lecture, le contrôle, la modification, et l'analyse de textes ainsi que la manipulation des langues formelles que sont les langages informatiques.
L'exemple d'expression régulière suivant permet de valider qu'une chaîne de caractère correspond à la syntaxe d'un nombre entier non signé, c'est à dire une suite non vide de chiffres :
En détails :
* Les crochets spécifient l'ensemble des caractères auquel doit appartenir le caractère courant de la chaîne. Dans cet exemple, l'ensemble est celui des chiffres de 0 à 9 inclus.
* Le caractère plus indique de répéter le motif précédent au moins une fois (suite non vide).
MySQL utilise l'opérateur REGEXP ou RLIKE pour la validation d'une chaîne de caractères :
Les expressions rationnelles peuvent être analysées et testées via un débogueur en ligne comme https://regex101.com/.
Expressions rationnelles courantes :
| Caractère | Type | Explication |
| . | Point | N'importe quel caractère |
| [...] | crochets | classe de caractères : tous les caractères énumérés dans la classe, avec possibilité de plages dont les bornes sont séparées par "-". Ex : [0-9a-z] pour tout l'alphanumérique en minuscule, ou [0-Z] pour tous les caractères de la table Unicode entre "0" et "Z", c'est-à-dire l'alphanumérique majuscule plus ":;<=>?@". |
| [^...] | crochets et circonflexe | classe complémentée : tous les caractères sauf ceux énumérés. |
| [...[...]] | union | Union des deux ensembles |
| [...&&[...]] | intersection | Intersection des deux ensembles |
| ^ | circonflexe | Marque le début de la chaîne ou de la ligne. |
| $ | dollar | Marque la fin de la chaîne ou de la ligne. |
| | | barre verticale | Alternative - ou reconnaît l'un ou l'autre |
| (...) | parenthèses | groupe de capture : utilisé pour limiter la portée d'un masque ou de l'alternative, grouper un motif répété ou capturer une séquence |
| \n | référence | Même séquence que celle capturée précédemment par le nème groupe de capture |
| \g{n} | référence | Même séquence que celle capturée précédemment par le nème groupe de capture |
| (?P<nom>pattern) | Sous-motif nommé | Nomme le résultat d'un groupe de capture par un nom. |
| \g{nom} | référence | Même séquence que celle capturée précédemment par le groupe de capture nommé nom. |
| \k<nom> | référence | Même séquence que celle capturée précédemment par le groupe de capture nommé nom. |
Par défaut, les caractères et groupes ne sont pas répétés. Les quantificateurs permettent de spécifier le nombre de répétitions et sont spécifiés immédiatement après le caractère ou groupe concerné.
Quantificateurs :
| Caractère | Type | Explication |
| * | astérisque | 0, 1 ou plusieurs occurrences |
| + | plus | 1 ou plusieurs occurrences |
| ? | interrogation | 0 ou 1 occurrence |
| {...} | accolades | nombre de répétitions : spécifie le nombre de répétitions du motif précédent (minimum et maximum). Avec la présence de la virgule, quand le minimum est absent la valeur par défaut est zéro, quand le maximum est absent la valeur pas défaut est l'infini. Sans virgule (un seul nombre) il s'agit du nombre exact (minimum et maximum ont la même valeur). Exemples : a{2} deux occurrences de "a", a{1,10} (sans espace) entre une et dix, a{,10} jusqu'à 10 fois (de 0 à 10), a{3,} au moins 3 fois (de 3 à l'infini). |
Par défaut les quantificateurs ne recherchent pas forcément la plus longue séquence de répétition possible. Il est possible de les suffixer avec un caractère pour modifier leur comportement.
Modificateurs de quantificateurs :
| Caractère | Type | Explication |
| ? | réticent | Le quantificateur qui précède recherchera la plus petite séquence possible. |
| + | possessif | Le quantificateur qui précède recherchera la plus grande séquence possible. |
Remarques :
* Les caractères de début et fin de chaîne (^ et $) ne fonctionnent pas dans où ils ont un autre rôle.
* Les opérateurs * et + sont toujours avides, pour qu'ils laissent la priorité il faut leur apposer un ? à leur suite.
Classes de caractères POSIX :
| Classe | Signification |
| [[:alpha:]] | n'importe quelle lettre |
| [[:digit:]] | n'importe quel chiffre |
| [[:xdigit:]] | caractères hexadécimaux |
| [[:alnum:]] | n'importe quelle lettre ou chiffre |
| [[:space:]] | n'importe quel espace blanc |
| [[:punct:]] | n'importe quel signe de ponctuation |
| [[:lower:]] | n'importe quelle lettre en minuscule |
| [[:upper:]] | n'importe quelle lettre capitale |
| [[:blank:]] | espace ou tabulation |
| [[:graph:]] | caractères affichables et imprimables |
| [[:cntrl:]] | caractères d'échappement |
| [[:print:]] | caractères imprimables exceptés ceux de contrôle |
Expressions rationnelles Unicode :
| Expression | Signification |
| \\ | Antislash |
| \C | Caractère spécial C non interprété : [ ] { } ( ) ? * . : \ & - ^ $ |
| \Q...\E | Séquence littérale non interprétée |
| \0xxx | Caractère Unicode (1 à 3 chiffres octaux) |
| \a | Alarme (ASCII 07) |
| \A | Début de chaîne |
| \b | Caractère de début ou fin de mot |
| \B | Caractère qui n'est pas début ou fin de mot |
| \cX | Caractère de contrôle ASCII (X étant une lettre) |
| \d | Chiffre |
| \D | Non chiffre |
| \e | Escape (ASCII 1B) |
| \f | Form-feed (ASCII 0C) |
| \G | Fin de la correspondance précédente |
| \h | Espace blanc horizontal [ \t\xA0\u1680\u180e\u2000-\u200a\u202f\u205f\u3000] |
| \H | Non espace blanc horizontal [^\h] |
| \n | Fin de ligne |
| \pL, \p{L}, \p{Letter} | Lettre (dans tout langage) |
| \r | Retour charriot |
| \R | Retour à la ligne, équivaut à \u000D\u000A|[\u000A\u000B\u000C\u000D\u0085\u2028\u2029] |
| \s | Caractères espace [ \t\n\x0B\f\r] |
| \S | Non caractères espace [^\s] |
| \t | Tabulation |
| \uxxxx | Caractère Unicode (4 chiffres hexadécimaux) |
| \v | Espace blanc vertical [\n\x0B\f\r\x85\u2028\u2029] |
| \V | Non espace blanc vertical [^\v] |
| \w | Caractère alphanumérique : lettre, chiffre ou underscore |
| \W | Caractère qui n'est pas lettre, chiffre ou underscore |
| \xxx | Caractère Unicode (2 chiffres hexadécimaux) |
| \x{xx...x} | Caractère Unicode (chiffres hexadécimaux) |
| \X | Caractère Unicode du groupe de graphèmes étendu |
| \z | Fin de chaîne |
Constructeurs spéciaux : Ces fonctions précèdent l'expression à laquelle elles s'appliquent, et le tout doit être placé entre parenthèses.
* ?: : groupe non capturant. Ignorer le groupe de capture lors de la numérotation des backreferences. Exemple : ((?:sous-chaine_non_renvoyée|autre).*).
    La présence d'un groupe capturant peut engendrer une allocation mémoire supplémentaire. Si une expression régulière particulièrement complexe provoque une erreur de mémoire, essayez de remplacer les groupes capturant non référencés et inutilisés par des groupes non-capturant en ajoutant ?: juste après la parenthèse ouvrante, et en décalant les numéros des groupes référencés.
* ?> : groupe non capturant indépendant.
* ?<= : positive lookbehind, vérifier (sans consommer) que ce qui précède correspond au motif spécifié. Exemple :
    Chercher une lettre u précédée d'une lettre q : (?<=q)u
* ?<! : negative lookbehind, vérifier (sans consommer) que ce qui précède ne correspond pas au motif spécifié.
* ?= : positive lookahead, vérifier (sans consommer) que ce qui suit correspond au motif spécifié.
* ?! : negative lookahead, vérifier (sans consommer) que ce qui suit ne correspond pas au motif spécifié. Exemples :
    Chercher une lettre q non suivie d'une lettre u : q(?!u)
    ((?!sous-chaine_exclue).)
    <(?!body).*> : pour avoir toutes les balises HTML sauf "body".
    début((?!mot_exclu).)*fin : pour rechercher tout ce qui ne contient pas un mot entre deux autres.
    (?!000|666) : pour exclure 000 et 666.
Options :
Les options d'interprétation sont en général spécifiées à part. Mais certaines API ne permettent pas de les spécifier. Il est possible d'insérer ces options dans l'expression régulière.
    (?optionsactivées-optionsdésactivées)
Exemples :
* Chercher un mot composé de voyelles sans tenir compte de la casse :
    (?i)[AEIOUY]+
* Chercher un mot composé de voyelles en tenant compte de la casse, ici en majuscules :
    (?-i)[AEIOUY]+
Les options s'appliquent à toute l'expression quelle que soit leur position dans l'expression.

## Introduction

En MySQL 5.1, les expressions régulières fonctionnent sur des textes en octets et peuvent donc donner des résultats inattendus avec des textes en Unicode.
MySQL propose plusieurs opérateurs et fonctions pour le regex. toutefois, c'est assez limité car en MySQL 8.0 il n'existe toujours pas de groupe de capture.

## REGEXP
### Syntaxe

RLIKE est synonyme de REGEXP.
Dans MySQL, le double antislash fait office de caractère d'échappement.

### Exemples

* Est-ce que la sélection est différente des lettres de A à Z :
* Sélectionner les pages de wiki qui matchent un pattern :

## REGEXP_LIKE

Même rôle que REGEXP mais on peut définir les options en argument. Ex :

## REGEXP_REPLACE

Fonctionne comme la fonction REPLACE mais avec du regex. Exemple pour retirer les caractères spéciaux d'une chaine :

## REGEXP_SUBSTR

Extrait une sous-chaine (substring). Ex :

## Références

* ↑ https://unicode-table.com/fr/
* ↑ https://docstore.mik.ua/orelly/webprog/pcook/ch13_05.htm
* ↑ https://www.regular-expressions.info/posixbrackets.html
* ↑ https://www.regular-expressions.info/unicode.html
* ↑ https://www.regextester.com/15
* ↑ Jan Goyvaerts, Steven Levithan, Regular Expressions Cookbook, O'Reilly Media, Inc., 22 mai 2009 (lire en ligne)
* ↑ Les options sont appelées modificateurs (modifiers en anglais), voir https://www.regular-expressions.info/modifiers.html
* ↑ Pour se familiariser avec Unicode, on peut lire À la découverte d'Unicode
* ↑ https://dev.mysql.com/doc/refman/8.0/en/regexp.html

# Bases de données spatiales
## Principe

Lors du typage des champs, certains représentent des objets graphiques, et sont donc considérés comme étant de catégorie "Spatial" (base de données spatiales). Par conséquent, ils se manipulent par des requêtes différentes que pour le texte.
On distingue huit types de champs :
* Geometry
* Point
* LineString
* Polygon
* MultiPoint
* MultiLineString
* MultiPolygon
* GeometryCollection
Et six relations entre eux :
* Contains
* Disjoint
* Equals
* Intersects
* Overlaps
* Within

## Références

* ↑ (anglais) http://dev.mysql.com/doc/refman/5.0/en/spatial-datatypes.html
* ↑ (anglais) http://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-mbr.html

# Importer et exporter
## Exporter

phpMyAdmin propose un bouton "Export" permettant l'extraction de toute une base dans un fichier .sql. Pour ne sortir que quelques tables, il faut les cocher puis choisir "Export" dans le menu déroulant en bas.
Pour exporter en SQL, le mot clé est INTO OUTFILE :
Le processus MySQL écrit lui-même le fichier, pas l'utilisateur. De plus, le fichier est stocké sur le serveur, pas le client.
Généralement le serveur a le droit d'écrire dans /tmp, donc même s'il n'est pas sécurisé ce répertoire est utilisé pour les exemples ci-dessous.
Par ailleurs, exporter est possible en ligne de commande :
Ou bien via mysqldump.

## Importer

Pour importer un fichier :
Le fichier doit se trouver sur le serveur MySQL.
Options additionnelles :
Pour spécifier la structure du document et la présence d'en-tête, on peut associer les colonnes de la base à des variables :
Dans cet exemple, seule la seconde et troisième colonne du fichier sont stockées dans le champ name et phone_number.

## Précisions sur le contenu

Pour importer un .sql créant un utilisateur et sa base de données, il faut savoir s'il existe déjà sur le serveur, car MySQL ne possède pas de DROP USER IF EXISTS. Par contre pour les bases ça fonctionne :
PS : si cette commande renvoie la commande drop database est désactivée avec PhpMyAdmin, modifier son config.default.php en passant $cfg['AllowUserDropDatabase'] à true, et vider le cache du navigateur.

# Réplication
## Principe

La réplication signifie que les données écrites sur le master MySQL sont envoyées à des slaves faisant office de copies.
Applications :
* Sauvegardes : backup automatique alternatif à mysqldump.
* Distribution : accès en lecture de la même base depuis plusieurs serveurs pour augmenter les performances.
* Failover : système de secours.
Il y a deux types de réplication :
* Asynchrone (master/slave).
* Semi-asynchrone (réplication asynchrone plus avec un slave avant de terminer la requête).
Configurations des réplications :
* standard : master->slave
* double maître : master<->master
En Master-Master les deux hôtes sont tour à tour master et slave : le serveur A se réplique sur le serveur B qui se réplique sur le serveur A. Il n'y a pas de vérification de consistance des données, même si auto_increment_increment/auto_increment_offset est configuré les deux serveurs ne doivent pas être utilisés pour des accès concurrents.

## Réplication asynchrone

C'est le cas le plus simple, un master écrit un fichier de log binaire, et les slaves peuvent lire ce dernier (potentiellement sélectivement) pour rejouer les commandes de la requête.
Étant asynchrone, le master et les slaves peuvent avoir différents états au même moment. Cette configuration peut résister aux coupures réseau.

### Configuration du master

Dans /etc/mysql/my.cnf, section [mysqld] :
* Définir un identifiant de serveur ; par exemple 1 :
* La réplication est basée sur les logs binaires, donc les activer :
Créer un nouvel utilisateur pour que le slave puisse se connecter :
Vérifier l'identifiant de serveur :

### Configuration de chaque slave

Dans /etc/mysql/my.cnf, section [mysqld] :
* Définir un identifiant de serveur différent du master et des autres slaves :
* Vérifier avec :
* Il est aussi possible de déclarer le nom de la machine slave dans le master (cf. SHOW SLAVE HOSTS) :
Déclarer le master :
Si la réplication sert de backup, spécifier le point de départ :
Démarrer la réplication :
Cela va créer un fichier master.info, typiquement dans /var/lib/mysql/master.info ; contenant la configuration et le statut.

### Vérifier la réplication
#### Sur le slave

Ou bien pour avoir un résultat formaté plus lisible :
Exemple :
Vérifier en particulier :
On peut supposer une nature réplication asynchrone :
Voir aussi :

#### Sur le master

Vérifier les connexions des slaves :

### Consistance

La réplication est une simple copie, similaire aux sorties mysqldump dans le client mysql.
par conséquent, pour maintenir cette consistance :
* Ne pas écrire sur le slave ;
* Démarrer la réplication avec des données initiales identiques sur le master et le slave ;
* Utiliser les mêmes versions de MySQL sur eux peut aider.

### Réparer

Par défaut, la réplication stoppe en cas d'erreur (provenant du réseau ou d'une requête).
Dans ce cas, regarder la trace dans le log (généralement /var/log/syslog) :
La meilleure façon et de relancer la réplication entièrement.
On peut aussi tenter de réparer, par exemple faire sauter à MySQL la commande 1 :
Attention en définissant ce nombre car il contient toutes les commandes, pas seulement les erreurs.
Une autre façon est d'utiliser les outils Maatkit :
* mk-slave-restart (pour relancer la réplication du slave si SQL_SLAVE_SKIP_COUNTER ne peut pas aider)
* mk-table-checksum (pour faire un checksum des tables sur le master et le slave)
* mk-table-sync (pour synchroniser le slave avec le master basé sur des statistiques générés par mk-table-checksum).

### Désinstaller

Pour supprimer une réplication :
* MySQL met le slave en pause et remplace la configuration avec les valeurs par défaut. master.info est effacé.
* Relancer MySQL pour effacer toute la configuration.
Attention : STOP SLAVE arrêt la réplication. Elle peut être relancée manuellement ensuite, ou bien automatiquement lors de la relance du serveur MySQL. Pour éviter ce lancement automatique :
Pour arrêter d'utiliser la réplication, vérifier que la configuration est bien vide :

## Hints SQL

Avant chaque requête, des indications concernant la réplication peuvent être placées en commentaire. Par exemple via le plugin Mysqlnd de PHP.
* MYSQLND_MS_MASTER_SWITCH : force la requête sur le master.
* MYSQLND_MS_SLAVE_SWITCH : force la requête sur l'esclave.
* MYSQLND_MS_LAST_USED_SWITCH: force la requête sur le dernier serveur utilisé.

## Tables fédérées

Sur MySQL 8 et sur MariaDB depuis la version 10.0, une alternative à la réplication et au clustering existe. Il s'agit du moteur de stockage Federated, qui permet de créer une table sur un serveur qui se synchronisent avec la même sur un autre.

### CREATE SERVER

Pour éviter de définir le mot de passe du serveur distant dans la requête SQL, on peut utiliser :
Ces serveurs sont stockés dans :
Pour en supprimer un :

## Partitionnement

Pour optimiser les performances, il est possible de découper les tables en plusieurs partitions selon une clé définie lors de la création de la table.
En MySQL, il existe :
* PARTITION BY RANGE
* PARTITION BY LIST
* PARTITION BY HASH
* PARTITION BY KEY

## Références

* ↑ https://dev.mysql.com/doc/connectors/en/apis-php-mysqlnd-ms.quickstart.sqlhints.html
* ↑ https://dev.mysql.com/doc/refman/8.0/en/federated-create-server.html
* ↑ https://mariadb.com/kb/en/federated-storage-engine/
* ↑ https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

# Optimisation
## Optimiser le serveur MySQL
### Avant de démarrer l'optimisation

Quand la base est anormalement lente, vérifier les points suivants :
* Trouver les goulots d'étranglements (processeur, RAM, I/O, requêtes)
* Chercher à complexifier les requêtes gourmandes. Généralement on effectue les opérations suivantes dans cet ordre pour éliminer un maximum de données inutiles des résultats rapidement : projection, sélection, jointure.
* Optimiser l'application en retirant les requêtes ou cache PHP des pages web.
* Optimiser les requêtes (ajouter des index, des tables temporaires ou changer de jointure).
* Optimiser la base du serveur (taille du cache, etc.).
* Optimiser le système (les différents systèmes de fichier, le swap (mémoire virtuelle) et les versions du noyau).
* Optimiser le hardware.
Des outils pour unixeries existent pour trouver les goulots d'étranglement :

#### vmstat

    monitore les utilisations du processeur, de la RAM et des I/O en les classant.

#### mytop

    trouve les requêtes lourdes.

#### mysqlreport

    checklist pas à pas (nécessite Perl et son module DBD-MySQL installés).

#### MySQL Workbench

    anciennement mysql admin(istrator), monitore et personnalise MySQL de façon convenable.
On peut ensuite classer les applications en trois groupes par leurs nécessités :
* I/O et lecture (blogs, news).
* I/O et écriture (traqueur de connexion web, collection de données de compte).
* CPU (CMS, logiciel de business).

### Variables de statut et serveur

MySQL peut être monitoré et personnalisé en surveillant les variables de statut, et définissant les variables de serveur qui peuvent être globales ou par session.
Les variables de statut peuvent être monitorées par SHOW [GLOBAL|SESSION] STATUS [LIKE '%foo%'] ou mysqladmin [extended-]status.
Les variables de serveur peuvent être définies dans /etc/mysql/my.cnf ou via SET [GLOBAL|SESSION] VARIABLE foo := bar, et affichées avec mysqladmin variables ou SHOW [GLOBAL|SESSION] VARIABLES [LIKE '%foo%'].
Généralement, les variables de statut commencent par une majuscule, et pas les variables de serveur.
Pour gérer les quotas de ces variables, il faut les multiplier par la valeur de max_connections pour avoir une estimation de la mémoire maximum utilisée. Cela permettra d'éviter des crashs lors des pics de connexions. Exemple :
    min_memory_needed = global_buffers + (thread_buffers * max_connections)

#### Expérience

Lançons le programme Perl suivant :

##### Rôle

Simule une entrée de données en quantité industrielle à intervalle régulier.

##### Utilisation

Pour vérifier le nombre d'éléments :
La taille doit être importante :
Incidemment, restaurer d'un dump est plus rapide car il y a moins d'insertions :
La commande SQL scanne tous les enregistrements pour obtenir une somme :
Par exemple, pour compter le matériel depuis le premier janvier 2008 :
MySQL a besoin de lire toute la base même pour un petit nombre d'enregistrement, car rien ne garantit qu'ils soient classés. Pour améliorer ceci, on peut faire de la date un index. MySQL va donc créer une nouvelle table cachée avec les dates classées dans l'ordre, et stocker leur position dans la table 'weightin' afin de pouvoir faire le lien avec. Comme l'index est ordonné, MySQL peut plus rapidement localiser un enregistrement (ex : par dichotomie) plutôt que lors d'une lecture séquentielle.
Ajout de l'index :
On remarque que le fichier .MYD a grossi :
C'est parce qu'il est utilisé pour stocker les index, par défaut toutes les clés primaires.
On constate aussi que l'ordre naturel des résultats change après ajout de l'index. Dans cet exemple, SELECT * FROM weightin renverra les valeurs de "date" les plus élevées à la fin, puis utilisera les ID comme clé de tri secondaire.

#### Autre exemple

Tentons d'optimiser la requête :
Il suffit de faire de 'line' un index, afin qu'il puisse éviter les doublons regroupés ensemble, au lieu de rescanner toute la table pour les localiser :
Taille du fichier :
On constate que l'ordre naturel des résultats change après ajout de l'index. Dans cet exemple, SELECT * FROM weightin renverra les valeurs de "line" les plus élevées à la fin, puis utilisera les ID comme clé de tri secondaire.

#### Considérations générales

La première question pour optimiser les sélections est toujours de savoir si les index sont configurés, et si oui s'ils sont utilisés.

##### 1. Vérifier si les index sont utilisés

Les requêtes individuelles peuvent être détaillées par EXPLAIN. Pour tout le serveur les variables "Sort_%" peuvent être surveillés pour indiquer combien de fois MySQL doit aller les chercher dans le fichier de données en l'absence d'index.

##### 2. Est-ce que les index sont stockés dans un tampon

Garder les index en mémoire vive augmente les performances de lecture. Le quotient des clés lues sur les requêtes de lecture de clés reflète les réels accès de MySQL au fichier d'index sur le disque quand il nécessitait une clé.
Idem avec les clés écrites, utiliser mysqlreport pour faire le calcul. Si le pourcentage est trop haut, key_buffer_size pour MyISAM et innodb_buffer_pool_size pour InnoDB sont les variables à régler.
Les variables Key_blocks_% peuvent être utilisées pour voir combien les clés tampons sont réellement utilisées. Une unité correspond à 1 ko, sauf si key_cache_block_size a été modifié. Comme MySQL utilise les blocs internes, key_blocks_unused doit être vérifié. Pour estimer la taille du tampon à définir, celle des fichiers .MYI doit être vérifiée. Pour InnoDB il y a innodb_buffer_pool_size qui concerne tous les types de données en tampon (pas seulement les index).

##### 3. Configuration avancée

sort_buffer_size (par thread) est la mémoire utilisée pour ORDER BY et GROUP BY. Il est déconseillé par contre d'utiliser myisam_sort_buffer_size.
read_buffer_size (par thread) est la taille de mémoire allouée pour les scans complets de table (comme les tables volumineuses ne tiennent pas complètement en mémoire).

### Query cache

La principale raison de ne pas rétrograder vers des versions antérieures à MySQL 4.0.1, est la faculté de stocker les requêtes SELECT jusqu'à ce que les tables soient modifiées.
Le Query Cache peut être configuré au travers des variables query_cache_%. La plus importante est la globale query_cache_size et query_cache_limit qui préviennent les requêtes uniques à résultats anormalement plus larges que la taille du cache.
Les blocs Query Cache ont une taille variable dont le minimum est défini par query_cache_min_res_unit, donc après reset du cache le nombre de bloc libre doit être idéalement de un. Une large valeur de Qcache_free_blocks engendrerait de la fragmentation.
Voir aussi les variables :
* Qcache_free_blocks : si la valeur est haute, cela indique une forte fragmentation.
* Qcache_not_cached : si la valeur est haute, il y a soit plus de requête hors du cache (par exemple parce qu'ils utilisent des fonctions comme now) soit la valeur de query_cache_limit est trop basse.
* Qcache_lowmem_prunes : montant des anciens résultats purgés car le cache était plein, et les tables modifiées. query_cache_size doit être augmenté pour abaisser cette variable.
Exemple d'un cache vide :
Cache utilisé (savannah.gnu.org) :
Le paramètre de my.cnf correspondant est :
Pour nettoyer le cache afin d'améliorer les performances :
Pour conserver les requêtes en cache en les défragmentant :

### Attendre les locks

Les variables Table_locks_% affichent le nombre de requêtes en attente faute de pouvoir accéder aux tables actuellement verrouillées par d'autres requêtes. Ces situations peuvent être causées par la commande LOCK TABLE ou encore par plusieurs accès en écriture simultanés sur la même table.

### Cache des tables

MySQL a besoin d'un certain temps pour ouvrir une table et lire ses métadonnées comme les noms de colonnes.
Si plusieurs threads tentent d'accéder à la même table, elle est ouverte plusieurs fois.
Pour accélérer ceci la métadonnée peut être stockée dans le table_cache (alias table_open_cache depuis MySQL 5.1.3).
Une bonne valeur est le nombre max_connections multiplié par le nombre de tables moyen par sélection.
Utiliser mysqlreport ou regarder les Open_tables après que les Opened_tables ou le Uptime nombre de tables ouvertes par seconde peut être calculé (hors des heures de pointe comme la nuit).

### Connexions et threads

Pour chaque connexion de client, MySQL crée un thread séparé sous le processus principal mysqld. Pour les grands sites à plusieurs centaines de connexions par semaine, créer les threads eux-mêmes peut consommer un temps non négligeable. Pour l'accélérer, les threads en attente sont mis en cache après déconnexion de leur client. En règle générale, moins d'un thread par seconde peut être créé ensuite.
Les clients qui envoient plusieurs requêtes au serveur doit utiliser les connexions persistantes comme avec la fonction PHP mysql_pconnect.
Ce cache peut être configuré par thread_cache_size et monitoré avec les variables threads_%.
Pour éviter les surcharges MySQL bloque les nouvelles connexions si plus que max_connections sont utilisé à cet instant. Commencer par max_used_connections et surveiller le nombre de connexion rejetées (Aborted_clients) et celle time out (Aborted_connections).
Ne pas déconnecter les clients aux connexions persistantes peut rapidement provoquer un déni de service. Les connexions normales sont fermées après le wait_timeout d'inactivité en seconde.

### Tables temporaires

Il est parfaitement normal que MySQL crée des tables temporaires pendant les classements ou les résultats de regroupement. Ces tables sont soit en mémoire, soit trop larges et sont écrites sur le disque (plus lent).
Le nombre de tables sur le disque (variables Created_tmp_%) doit être négligeable ou la configuration de max_heap_table_size et tmp_table_size doit être reconsidérée.

### Écritures différées

Pour rédiger les logs d'accès au serveur web dans une base, avec de nombreux INSERT subséquents dans la même table, les performances peuvent être améliorées en conseillant au serveur de mettre en cache les requêtes d'écriture un court moment, puis de tout envoyer comme batch sur le disque.
Attention au fait que toutes les méthodes mentionnées ne contreviennent pas à la recommandation ACID car les insertions sont reconnus avec OK au client avant leur écriture définitive sur le disque, et donc des données pourraient être perdues en cas de crash.
* Pour les tables MyISAM, les écritures différées peuvent être définies avec DELAY_KEY_WRITE dans un CREATE ou un ALTER TABLE. L'inconvénient est qu'après un crash la table est automatiquement marquée comme corrompue et doit être vérifiée voire réparée ce qui prend un certain temps.
* Pour InnoDB, c'est innodb_flush_log_at_trx_commit. En cas de crash seuls les index sont reconstruits.
INSERT DELAYED fonctionne sur les principaux moteurs de stockage.

## Optimiser les tables
### Index

Il convient d'utiliser le commande suivante régulièrement pour réorganiser les enregistrements sur le disque dur, afin de réduire la taille de la table (sans rien effacer) et d'accélérer les lectures par index (grâce aux enregistrements contigus nécessitant moins de déplacement des têtes de lecture des disques durs) :
De plus, au moment de leurs créations, les types les plus petits possibles sont souhaités. Par exemple :
* si un nombre est toujours positif, choisir un type unsigned afin de pouvoir en stocker deux fois plus dans le même nombre d'octets.
* pour stocker des dates contemporaines (de 1970 à 2038) mieux vaut prendre un timestamp sur quatre octets qu'un datetime sur 8.
Pour utiliser au mieux un index, la valeur de la requête ne doit pas être convertie (ex : si l'index porte une un type entier, ne pas chercher where champ = '2' mais where champ = 2).

### Dupliquer une partie en lecture seule

Quand les anciens enregistrements ne sont plus utiles mais qu'on ne veut pas les archiver, on peut créer une table temporaire (mais pas temporary table) à partir d'une requête. Les performances de cette petite table seront alors bien meilleures.

## Optimiser les requêtes
### Principes

D'une manière générale, il est préférable d'effectuer par priorité décroissante dans le langage de requête :
* Les sélections, afin de réduire le plus grand nombre de données en mémoire. Dans la mesure du possible il faut éviter les wildcards (*) qui engendrent plus de transfert d'information en réseau (ex : ID ou dates de mises à jour inutiles).
* Les projections, toujours pour diminuer la taille des données.
* Les tris, pour accélérer les jointures.
* Les jointures. Les différents plans d'exécution examinés sont constitués des différents chemins d'accès (ex : accès aux index primaires et secondaires) et de la variété des techniques de jointure selon les hints :
** tri fusion (merge join)
** hashage (hash join)
** boucle imbriquée (nested loop join)
** produit (product join).
De même, si l'ordre des conditions dans le WHERE ne modifie jamais le résultat obtenu, il peut en revanche avoir un impact important sur les performances. En effet, il est plus performant de :
* Placer les conditions qui filtrent le plus d'enregistrements avant les autres (cela nécessite en général de connaitre la taille courante des tables).
* Vérifier l'emploi du mot clé BETWEEN, qui peut consommer plus de ressources en allant chercher des octets fragmentés sur le disque, qu'un parcours séquentiel de table.
* S'assurer que les LIKE ne sont pas remplaçables par des =.
* S'assurer que les CURSOR (en) ne sont pas remplaçables.
* Déplacer des conditions du WHERE ou du GROUP BY dans le JOIN.
* Remplacer les SUM(ROUND) par des ROUND(SUM).

### kill

Les requêtes en cours sont visibles avec :
Pour forcer l'arrêt de celles qui durent depuis plus d'une heure (3600 secondes), copier le résultat de la requête suivante pour l'exécuter dans un deuxième temps :
Si les requêtes restent en statut "Killed" au lieu de disparaitre de la liste des processus, c'est que le SGBD rollback. Si c'est trop long, on peut alors redémarrer le daemon du serveur.

### Comparer les fonctions avec BENCHMARK

BENCHMARK permet de mesurer les rapidités des fonctions ou opérateurs MySQL :
Toutefois, on ne peut pas comparer des requêtes avec :
En effet, sachant que MySQL doit parser la requête, on peut considérer que les benchmarks inférieurs à 10 s ne sont pas exploitables.

### Analyse des fonctions avec EXPLAIN

En ajoutant EXPLAIN devant SELECT, MySQL détaille les différentes opérations qu'il effectue dans le cadre de cette sélection (comment les tables sont jointes et dans quel ordre). Cela permet de placer d'éventuels hints en fonction.

#### Exemple

Jointure de deux tables sans indice :
Maintenant on ajoute un index sur la seconde table, ce qui fait descendre ensuite lors de la même sélection, la colonne rows de la seconde ligne : MySQL a donc effectué une lecture de moins pour le même résultat.
Enfin, en ajoutant un index sur la première table, la condition WHERE est améliorée car MySQL sait qu'il ne lui faut qu'une ligne de la première table (au lieu de quatre) :

### Hints d'optimisation

L'ordre des mots réservés est important si on applique plusieurs hints :

#### HIGH_PRIORITY

Généralement les commandes LMD (INSERT, DELETE, UPDATE) sont prioritaires sur le SELECT. Mais grâce à HIGH_PRIORITY un SELECT peut être traité avec elles.

#### STRAIGHT_JOIN

Force MySQL à évaluer les tables d'un JOIN dans l'ordre où elles sont nommées (de gauche à droite).

#### SQL_SMALL_RESULT

Lors d'un DISTINCT ou d'un GROUP BY, ce hint prévient l'optimiseur que la requête va renvoyer un petit nombre de lignes.

#### SQL_BIG_RESULT

Lors d'un DISTINCT ou d'un GROUP BY, dit à l'optimiseur que la requête renvoie un nombre élevé de résultats.

#### SQL_BUFFER_RESULT

Force MySQL à copier le résultat dans une table temporaire. Cela peut s'avérer utile par exemple pour supprimer des LOCK rapidement.

#### SQL_CACHE

Force MySQL à copier le résultat dans le cache. Ne fonctionne que si la valeur de query_cache_type est DEMAND ou 2.

#### SQL_NO_CACHE

Demande à MySQL de ne pas mettre le résultat en cache. C'est utile quand la requête survient très rarement, ou que le résultat change très souvent.

#### SQL_CALC_FOUND_ROWS

Si une requête contient LIMIT, ce hint dit au serveur de calculer combien de lignes auraient été retournées en cas d'absence de LIMIT. Pour récupérer le nombre il faut sélectionner FOUND_ROWS.

#### Hints sur les index

Afin d'influer sur les déroulements vu précédemment, on peut utiliser les hints suivants :
* USE INDEX : spécifie de rechercher des enregistrements de préférence en parcourant les index des tables.
* FORCE INDEX : idem en plus restrictif. Une table ne sera parcourant sans index que si l'optimiseur n'a pas le choix.
* IGNORE INDEX : demande de ne pas favoriser les index.
Exemples :
Pour appliquer ces règles lors d'une jointure, d'un tri ou d'un regroupement, il suffit d'ajouter utiliser FOR JOIN, FOR ORDER BY ou FOR GROUP BY.
Trop d'index dans une table ralentit toutes les requêtes qui y sont faites. Il est donc recommandé de ne pas en créer plus de deux ou trois dans chacune.

### Limites

Dans MariaDB 10 et MySQL 5, il existe une limite de 61 jointures par requête. Il peut donc être nécessaire de les remplacer, par exemple par :
* GROUP_CONCAT : si on n'a pas besoin que les données soient dans des colonnes séparées.
* UNION : s'il y a beaucoup de tables différentes jointes
* Une sous-requête (SELECT direct ou via une procédure stockée)
* Une classe statique de mapping dans les applications qui utilisent la base (enum pour remplacer des SELECT vers des données fixes, telles que la civilité : Mr, Mme).

## Liens externes

* (en) « MySQL Optimization »
* (en) « A guide to mysqlreport »
* (en) « High Performance MySQL » (livre)
* (en) « Tuning tips from the company EZ »
* (en) « MySysop » (démo d'un script PHP pour l'optimisation et le tuning MySQL)
* Voir aussi les newsgroups et mailing lists MySQL

# API

L'interface de programmation (API) MySQL permet aux applications d’interagir avec les bases de données.

## Optimisation
### Appels API
#### Connexions persistantes

En utilisant les connexions persistantes, plusieurs requêtes peuvent être exécutées sans reconnexion. C'est un gain de temps pour l'utilisateur, mais le serveur doit y allouer une partie de sa RAM pendant tout ce temps, ce qui peut le saturer, surtout quand tous ses sites le font.

#### Mémoire libre

Certaines requêtes enregistrent une ligne dans une variable. Cela peut donc avoir du sens de libérer cette mémoire un peu avant la fin du script, plutôt qu'à la toute fin.

#### Recherche de ligne

La plupart des APIs proposent deux types de recherches de ligne : en tableau (associatif ou pas) ou en objet.
Assigner les lignes dans un objet est plus lent, alors que le tableau non associatif est le plus rapide (et c'est le mieux si on ne prend qu'un seul champ par enregistrement).

#### API vs SQL

Généralement, les APIs ont des méthodes optimisées qui créent des commandes SQL et les envoient au serveur MySQL.

### Réduire les communications client/serveur

* Certains scripts utilisent deux requêtes pour extraire une table pivot. Les communications client/serveur étant toujours le facteur ralentissant des applications, il faut préférer une seule jointure à la place.
* Si toutefois plusieurs requêtes s'avèrent nécessaires, utiliser les connexions persistantes.
* Ne sélectionner que le minimum de champs (éviter *).
* Éviter d'inclure dans les commandes SQL des caractères inutiles (espaces, tabs, commentaires...).

#### CREATE ... SELECT, INSERT ... SELECT

Lors de création de nouvelle table depuis une existante, CREATE ... SELECT peut être utilisé.
Pour remplir une table existante, c'est INSERT ... SELECT ou REPLACE ... SELECT.

#### INSERT DELAYED

Certains scripts n'ont pas besoin de vérifier si les insertions se sont bien déroulées.
Dans ce cas, faire appel à INSERT DELAYED pour que le serveur n'attende pas.

#### REPLACE

Lors d'un DELETE précédé d'un INSERT, le serveur reçoit deux commandes SQL.
En revanche, avec REPLACE il n'en reçoit qu'une.
De plus, REPLACE DELAYED est possible.

### Autres techniques
#### Stocker les données dans des cookies

Parfois, des données de session sont stockées dans la base.
Cela nécessite un UPDATE et un SELECT à charge chargement de page.
Cela peut être évité avec les cookies (même si l'utilisateur peut refuser leur utilisation, ou bien lire leur contenu). Il faut éviter d'y stocker des mots de passe et leur attribuer un bref temps de vie sous peine de compromettre la vie privée de l'utilisateur.
Autre solution :
* Une fois l'utilisateur connecté sur le site, lancer CURRENT_TIMESTAMP et un ID au hasard ;
* Définir un cookie avec cet ID ;
* Quand l'utilisateur fait quelque chose qui nécessite de vérifier son identification :
* Mettre à jour le timestamp.

#### Créer du contenu statique

Quand un utilisateur lit du contenu dynamique (d'une base), un document HTML est généré.
Souvent, cette page ne contient pas de variable mais du contenu inséré une seule fois, sans mise à jour.
D'où l'idée de stocker des pages HTML statiques, supprimée puis regénérées lors des mises à jour de la base.

## PHP
### Pilotes

PHP possède les pilotes officiels suivant pour MySQL :
* mysql : ancien mais toujours utilisé dans les applications web ; c'est un module PHP procédural.
* mysqli : plus rapide, peut être utilisé comme un ensemble de classes ou comme une bibliothèque procédurale.
* PDO (PHP Data Objects) : utilise PDO, une couche abstraite pour interaction avec les bases, avec des pilotes pour MySQL et ODBC.
* PDO_MYSQL : propose des fonctionnalités MySQL avancées, et les émulent si absentes.
Les fonctions de ces pilotes utilisent l'API C. Elles peuvent utiliser MySQL Client Library ou mysqlnd, comme pilotes natifs pour PHP.
Parfois, activer mysql et mysqli peut causer des problèmes. Il est donc préférable d'en activer qu'un.
De plus, PHP possède une extension ODBC qui fonctionne avec MySQL.
PEAR est un framework PHP important qui prend en charge MySQL.

### register_globals et $_REQUEST

PHP a des variables d'environnement appelées register_globals. Depuis PHP 4.2, elles sont False par défaut, et ne doivent pas être activées. Dans PHP 5.3 la fonctionnalité est devenue obsolète et supprimée en PHP 5.4.0.
Cependant, si la version de PHP utilisée accepte register_globals, on peut vérifier s'il est activé lancer ini_get. Si c'est le cas, ini_set ne pourra pas le changer. Il y a deux façons de le faire :
* Éditer php.ini.
* Ajouter une ligne au .htaccess :
En fait si register_globals est True, un utilisateur peut arbitrairement ajouter des variables à votre script avec ce genre de commande :
Ne jamais utiliser le tableau $_REQUEST, lui préférer :
* $_ENV
* $_GET
* $_POST
* $_COOKIE
* $_SERVER
Cet ordre est celui suivi par PHP, mais il peut être modifié par variables_order.
Cela signifie que si votre script définit une variable serveur appelée "userid" et que l'application tente de la lire dans $_REQUEST, l'utilisateur peut prévenir en ajoutant une variable à la requête.

# Sécurité
## Sécurité

Sur Internet, des pirates tentent de récupérer des données en violant leurs accès. Ils insèrent souvent du code dans une page HTML ou PHP, par injection de code, généralement par :
* le biais d'un formulaire HTML qui permet d'entrer des données dans la base de données
* ou alors en insérant des données par le biais de la barre d'adresse de la fenêtre,
* par le dépôt d'un fichier sur le serveur (upload)
* ou toute autre voie d'accès au serveur possible.
Les bases de données permettent d'assurer le stockage des informations en liaison par exemple avec des pages HTML couplées avec PHP. Donc tout le monde peut en avoir accès par l'intermédiaire d'un client relié au serveur. MySQL basé sur SQL est notamment très utilisé avec PHP. Il est donc pertinent d'examiner quelques notions de sécurité en matière de SQL.
En ce qui concerne SQL, il faut savoir premièrement qu'une astuce de programmation ou un détournement de requête possible à partir d'un serveur a de bonnes chances de réussir sur un autre et il en est de même avec les applications web mêmes, qui peuvent être utilisées afin d'atteindre le serveur et d'en saccager les données.

### Paramètres de connexion

Parfois, les paramètres de connexion (dont login et mot de passe) sont stockés dans un fichier texte non crypté, comme un .ini. Cela n'est donc pas recommandé : si un utilisateur devine son nom il peut le lire sans peine. S'il est situé en dehors du répertoire WWW c'est mieux, mais la meilleure façon est de les enregistrer dans un programme (ex : .php).
Il est toujours possible pour un utilisateur de trouver les accès FTP, donc il vaut mieux en utiliser d'autres pour MySQL.
Inutile de se souvenir des mots de passe MySQL car le programme est censé le retrouver automatiquement. Il convient donc d'en choisir un robuste : très long, avec au moins une majuscule, une minuscule, un symbole (ex : '_') et un chiffre. Le tout sans contenir de mots du dictionnaire car ils sont utilisés pour accélérer les crackages lors d'attaque par force brute.
Ne jamais les stocker autre part, y compris dans des e-mails.

### Injections SQL
#### Définition

Normalement les valeurs stockées dans $_POST peuvent être insérées directement dans des requêtes SQL. Toutefois les injections SQL exploitent une faille de sécurité engendrée par le caractère d’échappement.

#### Exemple
##### Numérique

Soit le code PHP suivant qui récupère les données d'un formulaire :
Si on remplit le champ du formulaire avec le nombre "42", le code s'exécute normalement :
Mais si on ajoute une expression booléenne toujours vraie ensuite, "42 OR 1=1", cela change complètement le résultat :
L'utilisateur peut aussi insérer d'autres commandes à la suite, séparées par des points-virgules. Il s'agit du chainage (chaining SQL queries). Ex :

##### Chaîne

Soit le code PHP suivant :
Dans le cas des chaînes de caractères, il faut exploiter les apostrophes autour :
Pour obtenir cette requête, il faut remplir le champ de formulaire pour fermer l'apostrophe ouvrant puis commenter le fermant :
Par ailleurs, si un utilisateur découvre comment manipuler une base de cette façon, il peut très bien se créer un compte administrateur ensuite, et effectuer des modifications discrètes dans le but de récupérer de l'argent (fausses factures, phishing...).

##### Blind SQL injection

Si le code PHP empêche les injections SQL ci-dessus, il est potentiellement tout de même possible d'obtenir des informations en injectant des expressions booléennes vraies et fausses pour en comparer les retours de formulaire.
Par exemple, si les retours différent pour :
alors le champ rempli peut être exploité :

#### Solutions

Il faut vérifier que la variable stocke bien le type de données prévu :
* Pour les chaînes de caractères : comme elles sont entourées d'apostrophes, tous ceux qu'elles contiennent doivent être convertis (ex : en '' ou \'). Par exemple, PHP propose mysql_real_escape_string pour gérer ces substitutions.
* Pour les dates : les entourer d'apostrophes comme les chaînes.
* Pour les noms SQL (tables, champs...) : les entourer d'apostrophes comme les chaînes.
* Pour les nombres : s'ils contiennent autre chose que des chiffres, ils ne conviennent pas.
* NULL / UNKNOWN / TRUE / FALSE : ne doivent jamais être rentrées par l'utilisateur.
* Tronquer la valeur au nombre de caractères supporté par le champ correspondant.
Par ailleurs, aucun commentaire SQL ne devrait pourvoir être inséré par l'utilisateur.

### Mots de passe

Généralement ils sont chiffrés ou hashés puis dans la base par les applications. Par contre si c'est fait en SQL, c'est qu'ils ont été écrits au moins une fois en clair et donc été visibles :
* dans les logs du serveur,
* dans les logs MySQL,
* dans SHOW PROCESSLIST.
Il est donc fortement déconseillé d'envoyer ce genre de commandes :
Préférer depuis un .php par exemple :
Il est également déconseillé de ne rechercher que le mot de passe, il faut que le nom corresponde aussi, et utiliser une fonction de hachage cryptographique plus récente comme SHA256 :
* Ne jamais utiliser de fonctions de cryptage non sécurisées, comme PASSWORD.
* Ni de cryptage réversible (se contenter de comparer si un chaîne cryptée est égale à une autre chaîne cryptée comme authentification). Ou bien les mots de passe stockées dans une base doivent être impossibles à sélectionner (avec SELECT).
* Seulement du hachage cryptographique, comme SHA256, pas d'algorithmes plus vieux comme MD5.

### SSL

Si le contenu de la base est publique, il n'y a pas de raison de crypter les communications.
Toutefois il peut y avoir un accès restreint pour les droits d'écriture, ce qui nécessite des mots de passe.
Le cryptage SSL s'avère une bonne solution pour cela. En effet, en plus de crypter les messages, il certifie que l'utilisateur est bien celui qui était prévu (même si ce dernier a donné son mot de passe par phishing).

### Risques dans les manipulations de données

On en retrouve plusieurs types :
* Contournement d'une clause where, c'est-à-dire neutraliser la clause WHERE en injectant une condition constante (toujours vraie ou toujours fausse, comme par exemple : SELECT * FROM table WHERE 1;
* Modification sans limite, comme par exemple la destruction totale de données avec la commande DELETE : DELETE FROM table WHERE 1;
* Insertions indésirables, où le pirate peut injecter une ligne complète qui sera traitée à son insu par la base de données. L'application insère ainsi des valeurs dont elle n'est plus maîtresse, par exemple le pirate peut injecter : md5('secret') sans un formulaire, ce qui revient à crypter le mot 'secret', que la base de données ne pourra plus retrouver ou traiter.
* Exécutions multiples : par exemple une injection pour placer une requête dans une autre requête initiale, afin d'exécuter toutes les injections que veut le pirate.
* Surcharger le serveur : c'est-à-dire modifier une requête afin qu'elle engendre une charge de travail importante et inutile pour le serveur.
* Exportations cachées : en exportant des données, en les extrayant de leur dépôt habituel et les placer par la suite dans un réceptacle public plus facile à lire. Cela ce fait par exemple en contournant la clause WHERE, ou alors en insérant des fichier externes directement sur le serveur SQL.

### Chiffrement d'un mot de passe

Le chiffrement d'un mot de passe ou d'une donnée consiste en sa transformation en une autre donnée par le biais d'une méthode extrêmement difficile à inverser voire impossible avec les fonctions de hachage. MySQL offre des méthodes de chiffrement des données et par la même du mot de passe. Nous avons par exemple :

## Sécurité PHP

Voici quelques types d'attaques que l'on retrouve fréquemment :
* l'injection de code distant.
Par exemple, c'est introduire une instruction PHP dans une page, qui ira chercher du code PHP sur un site distant pour le ramener sur le serveur local du pirate et l'exécuter comme code local, afin de prendre contrôle du serveur et des données du serveur distant piraté.
* l'exécution du code à la volée.
Cela ce fait par exemple à l'aide de : eval, fonction qui prend en argument une chaîne de caractère pour l'exécuter comme du code PHP ; et par là exécuter des opérations destructrices de données par cette voie.
* le téléchargement de fichiers ; ce qui permet d'importer du code PHP sur un site.
Il suffit en effet que ce site ait des autorisations de lecture et qu'il soit accessible depuis le Web, sans besoin de droit d'exécution ou d'écriture pour exécuter tous les codes PHP que veut le pirate. En stockant le fichier, par la fonction "upload" (ex. image), le code est exécuté sur le serveur pirater et vient en saboter ou déranger le fonctionnement et même la lecture des autres documents.
* La directive register_globals;
Si elle est activée permet d'injecter dans les scripts toutes sortes de variables ; ce qui rend la programmation de script peu sûr. Heureusement que dans les versions supérieures à PHP 4.2 l'option register_globals est désactivée par défaut.
* filtrage des données ;
C'est-à-dire la gestion des erreurs, ou les mécanismes par lesquels l'application web déterminera la validité des données qui entrent et sortent de l'application. Il s'agit donc de s'assurer que : premièrement on ne puisse pas contourné le filtrage des données, que deuxièmement les données invalides ne puissent pas être confondues avec des données valides et enfin identifier l'origine des données.
Vous trouverez les solutions possibles à ces problèmes dans ce guide de sécurité PHP en ligne : http://phpsec.org/.

## La solution des systèmes de gestion de contenu ou de création de site web

Une autre solution est d'utiliser des systèmes de création et de gestion de contenu Web, pour créer un site web personnel et l'agrémenter d'une petite bases de données. L'avantage ici est que si vous utilisez par exemple une application comme http://sites.google.com/.
L'avantage est que la sécurité est ici gérée par de grandes sociétés comme ici 'Google' qui engage des experts en la matière. Il devient dès lors très difficile pour des pirates de saccager les données de l'application web que vous créez, ainsi que la base de données.
Sites.google, par exemple, est une application en ligne qui permet le plus simplement possible de créer un petit site Internet perso agrémenter d'une base données. Pas besoin ici de sécuriser les données c'est la société Google, qui s'en charge ! Par exemple, Google.site permet de rassembler rapidement en un seul endroit une série d'informations, tout en sécurité :
* vidéos
* agendas,
* présentations,
* informations stockées dans une base de données,
* pièces jointes,
* texte.
Elle permet également en matière de sécurité d'autoriser leur accès ou leur modification à un groupe restreint, ou carrément au monde entier. Encore une fois, il y a ici la garantie de Google en matière de sécurité des informations.
Voici cependant quelques mesures de sécurité qui sont offertes à l'utilisateur :
* supprimer quelqu'un d'un site ;
* contrôler l'accès au site ;
* supprimer des commentaires et des pièces jointes ;
* système de login avec mot de passe.
En matière de mot de passe il est à noter qu'il s'agit d'avoir sous la main trois types de mot de passe selon leur utilité : un pour des applications banales sans importance que vous utilisez, l'autre pour des applications personnelles (ex. e-mail), et l'autre enfin si possible plus complexes pour des informations hautement importantes et confidentielles que vous désirez gérer depuis Internet (ex. accès pour un site bancaire).

## Ouverture à un PC distant

Par défaut le serveur MySQL n'écoute pas sur le réseau. Pour changer cela, il faut préciser son IP publique dans la configuration :
ajouter ou modifier la ligne :
Si le problème persiste, vérifier que le port 3306 est bien ouvert sur le pare-feu.

## SQL SECURITY

Les droits d'une vue peuvent dépendre se son créateur ou de ses utilisateurs. Pour les définir, utiliser SQL SECURITY INVOKER et SQL SECURITY DEFINER.
Pour localiser les vues qui n'ont pas ces droits (et peuvent donc renvoyer des erreurs de permissions) :

## Références

* ↑ https://linuxfr.org/users/scurz/journaux/surveiller-un-serveur-mysql-avec-mytop
* ↑ http://hackmysql.com/mysqlreport
* ↑ https://dev.mysql.com/downloads/workbench/
* ↑ http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html
* ↑ http://dev.mysql.com/doc/refman/5.1/en/datetime.html
* ↑ (en) Ken Henderson, The Guru's Guide to Transact-SQL, Addison-Wesley Professional, 23 février 2000 (lire en ligne)
* ↑ (en) Kevin Kline, SQL in a Nutshell : A Desktop Quick, O'Reilly Media, Inc., 2004 (lire en ligne)
* ↑ https://dev.mysql.com/doc/refman/8.0/en/processlist-table.html
* ↑ http://dev.mysql.com/doc/refman/5.7/en/index-hints.html
* ↑ https://www.fromdual.com/mysql-limitations
* ↑ http://phpsec.org/
* ↑ http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
* ↑ https://www.editions-eni.fr/open/mediabook.aspx?idR=b790d48f0fe32d7b6e343a2e9e0a49ae

# Mots réservés
## Langage

Liste des mots réservés MySQL :

## Logiciel

En Unix, ce service se lance avec la commande "$ mysql ".
Voici le résultat de la première commande avec MySQL5.4.3 :
Il en existe aussi d'autres :

## Références

* http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
* http://www.htmlite.com/mysql002a.php

# Débogage
## Introduction

Comme vu précédemment dans la description de la base "mysql", il peut être utile d'activer les logs sur l'historique des requêtes lors du débogage d'une application qui utilise MySQL.

## Gestion des exceptions

En MySQL, les anomalies du type "division par zéro" ne renvoient pas d'erreur mais NULL.
Toutefois il est possible de lever des exceptions lors des manipulations de table, par exemple pour éviter qu'une liste d'insertions s'arrête au milieu à cause d'une contrainte d'unicité. L'exemple ci-dessous fonctionne sur une table InnoDB (et pas MyISAM) :
Ici une erreur surgit lors de la deuxième insertion d'un id=1. Selon une condition, on peut donc annuler les insertions de 2 et 3, ou bien les soumettre.
S'il y a plusieurs COMMIT avant un ROLLBACK (par exemple dans une boucle), ce dernier n'annulera que les opérations consécutives au dernier COMMIT.

## Erreurs MySQL
### A new statement was found, but no delimiter between it and the previous one

Ajouter un ";".
Sinon avec "DELIMITER" : sans espace avant (donc consécutivement au retour chariot).

### Access denied for user mon_compte@mon_hôte

* Vérifier le mot de passe du compte.
* Vérifier que l'hôte du compte n'a pas été changé par une règle réseau.
    Si c'est le cas, tester en shell :
* Sinon, recréer le droit avec :

### Base table or view not found: 1146 Table 'xxx' doesn't exist" is not defined, use one of: 100, 200, 250, 300, 400, 500, 550, 600

Une table temporaire n'est pas accessible, elle a peut-être été créée dans un autre thread : il faut la déplacer dans le même fichier.

### BIGINT UNSIGNED value is out of range

Se produit sur les opérations sur les grands nombres. On peut alors manipuler des nombres plus petits, par exemple en convertissant les octets en gigas dans le select.

### Can't create table 'xxx' (errno: 150)

Clé primaire invalide. Dans ce cas il vaut mieux séparer la création de la table puis l'ajout des contraintes en deux requêtes. Cela peut permettre par exemple de voir que les tables vers lesquelles pointent les contraintes doivent être créées avant ces dernières.

### Can't connect to MySQL server

Changer le paramètre "host".

### Can't create federated table

Le schéma de la table distante doit être le même que celui de la table locale.
Sinon, activer les logs sur le serveur distant pour voir le compte qui se connecte.

### Can't create/write to file 'mon_dossier/mon_fichier.csv'

Lors d'un SELECT INTO OUTFILE, si le compte du SGBD utilisé a les droits d'exportation (GRANT FILE ON .* TO 'mon_compte'@'%';), le problème est lié au compte MySQL de l'OS qui n'a pas accès au système de fichier. Comme généralement le compte "mysql" sur Linux n'a pas de shell, cela peut se confirmer ainsi :
Si les droits manquent :

### Cannot add foreign key constraint

Survient lors d'un "CREATE TABLE", et ce n'est pas lié à la valeur de foreign_key_checks.
En fait il ne faut pas créer de contrainte d'intégrité entre deux tables de moteur différent (ex : InnoDB vs MyISAM).

### Cannot drop index 'UNIQ_XXX': needed in a foreign key constraint

Il faut supprimer la clé étrangère avant l'index duquel elle dépend :

### ... check that it exists

Ajouter IF EXISTS. Ex :

### Data too long for column

Si les données avec des caractères non ASCII étaient censées tenir dans le champ, on peut passer en mode strict : SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Toutefois cela ne préserve pas leur encodage Unicode. Le mieux est donc :

### Failed to open the referenced table

Une clé étrangère tente d'être créée vers une table inexistante ou qui ne supporte pas les clés étrangères (ex : moteur MyISAM).

### Foreign data src error

Lors du create server, le compte renseigné n'existe pas.

### Foreign key constraint is incorrectly formed

Une clé étrangère n'est pas au même format que la colonne à laquelle elle fait référence. Par exemple si cette dernière n'existe pas ou si elle n'a pas le même type.

### Got error 'invalid character range'

L'utilisation de \- est parfois proscrite en regex (ou il faut l'échapper autrement ?).

### Host 'example.com' is not allowed to connect to this MySQL server

Dans le cas d'une connexion depuis un PC distant, le compte utilisé n'est pas autorisé. Il faut donc le configurer avec :
au lieu ou en plus de :

### Illegal mix of collations

Trois solutions :
* ALTER TABLE pour changer la structure d'au moins une des deux tables jointes, pour uniformiser leur collation.
* CAST(monChamp AS CHAR CHARACTER SET utf8).
* CONVERT(monChamp USING utf8).

### Invalid use of group function

* Dans le cas d'un SELECT, il conviendrait d'utiliser HAVING au lieu de WHERE pour modifier des enregistrements en fonction d'autres d'une sous-requête.
* Pour un UPDATE ou un DELETE, les champs comparés par un IN ne sont peut-être pas du même type.

### Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails

* En cas d'ajout de contrainte, il y a au moins un enregistrement qui ne peut pas être join avec la table cible.
* En cas de suppression, il manque un "DROP FOREIGN KEY" avant un "DROP TABLE" ou un "DROP COLUMN". Pour les supprimer, l'utilisateur root n'a pas le droit de modifier directement directement la table "information_schema". Il faut donc exécuter les requêtes générées par la suivante :

### La clé est trop longue. Longueur maximale: 1000

Dans le cas d'un alter table général ou de plusieurs champs, décomposer la requête pour traiter les champs un par un afin d'isoler celui qui provoque l'erreur. Ensuite on peut la résoudre en réduisant la taille du varchar (ex : de 255 à 50 si possible).

### Lock wait timeout exceeded; try restarting transaction

Le serveur SQL est surchargé : exécuter les instructions une par une. Sinon :

### View ... references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Temporairement on peut recréer la view sans "SQL SECURITY INVOKER".

### Specified key was too long; max key length is 3072 bytes

Se produit lors d'un ALTER vers un grand type comme "text". Exemple :
Solution :

### Syntax error or access violation

Utiliser phpMyAdmin pour trouver l'erreur de syntaxe.
Si cela survient après avoir collé une exportation de base, utiliser plutôt l'importation d'un .sql qui gère mieux les caractères d'échappement.

### This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Remplacer les "IN" par des jointures, ou la sous-requête par une deuxième dont le résultat est stockée dans une table temporaire.

### Too many tables (ou timeouts systématiques)

Si des requêtes sont systématiquement en timeout, c'est peut-être que la limite de 61 jointures a été atteinte. Pour pallier cela, il est possible d'en remplacer certaines par des SELECT.

### Type d'énoncé non reconnu

Certains mots clés ne sont reconnus que dans les procédures stockées, ou doivent être précédés d'un SELECT.

### Unexpected end of JSON text in argument 1 to function 'json_extract'

Lever l'exception vide avec : if(x = "", null, json_extract(x, y)).

### Unknown storage engine 'federated' mariadb

Lancer "mysql" en shell puis taper :

### 'utf8mb3' is deprecated and will be removed in a future release

Pas besoin de changer chaque champ varchar ou text, il suffit de convertir la table :

### You can't specify target table '...' for update in FROM clause

Cela se produit quand on essaie de mettre à jour ou supprimer des lignes selon une sélection de ces mêmes lignes. En effet, il est impossible de mettre à jour une table pendant en même temps qu'elle une subit sous-requête. Par exemple, pour réinitialiser un mot de passe SPIP :
* Passer par des CREATE TEMPORARY TABLE (voire DECLARE si cela rentre dans une variable scalaire).
* Sinon, il est possible de sélectionner les enregistrements à mettre à jour automatiquement en enveloppant la sous-requête dans une autre, grâce aux tables temporaires générées par les FROM.

### You do not have the SUPER privilege and binary logging is enabled
## phpMyAdmin
### Les résultats sont tronqués

Cliquer sous la requête à exécuter dans "Options supplémentaires", puis "Textes complets".

### Erreur : fonctionnalités relationnelles désactivées !

Se produit dans le concepteur de diagramme de phpMyAdmin, il faut l'activer dans config.inc.php.

## Références

* ↑ http://stackoverflow.com/questions/2950676/difference-between-set-autocommit-1-and-start-transaction-in-mysql-have-i-misse
* ↑ https://www.quora.com/How-do-I-override-a-max-limit-of-61-joins-in-a-MySQL-query
* ↑ (anglais) http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/