MySQL/Version imprimable
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 ?
[modifier | modifier le wikicode]- Gratuiciel en licence GPL version 2.
- Facile d'utilisation : intuitif et ergonomique.
- Vitesse performante[1].
- 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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]MySQL étant un freeware, il a donc engendré des fork officieux.
MariaDB
[modifier | modifier le wikicode]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[2]. 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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Tout-en-un
[modifier | modifier le wikicode]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 :
- EasyPHPtéléchargement : 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.
- WAMPtéléchargement : 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.
- XAMPPtéléchargement : 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 Servertéléchargement : 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 :
net start ews-dbserver net start ews-httpserver net start ews-dashboard pause net stop ews-dashboard net stop ews-httpserver net stop ews-dbserver
Message d'erreur relatif à SSL
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]- 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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]LAMP
[modifier | modifier le wikicode]Logiciel tout-en-un pour Linux (Apache + MySQL + PHP), comme WAMP pour Windows.
# apt-get install tasksel
# tasksel install lamp-server
Installation manuelle
[modifier | modifier le wikicode]Apache sur Debian / Ubuntu
[modifier | modifier le wikicode]# apt-get install apache2
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 :
# /etc/init.d/apache2 start
On peut ensuite tester le serveur, pour voir si une page s'affiche ou s'il refuse la connexion :
$ lynx http://localhost/
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 :
# vi /etc/default/apache2 ... NO_START=0
Installer PHP
[modifier | modifier le wikicode]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 :
telnet localhost 9000 CTRL + ALT + ] quit
FPM signifie FastCGI Process Manager, puisque le processus PHP-fpm écoute les requêtes CGI[3]. 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 :
deb http://ftp.fr.debian.org/debian/ stable main non-free contrib deb-src http://ftp.fr.debian.org/debian/ stable main non-free contrib
Ce qui suit suppose que le serveur Web a bien été installé ; exécuter les commandes suivantes :
sudo apt-get update && apt-get install php8.2 && apt-get install libapache2-mod-php8.2
Une fois ces commandes exécutées, redémarrer le serveur Web. Dans le cas d'Apache cela s'effectue avec la commande suivante :
/etc/init.d/apache2 restart
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 :
$ lynx http://localhost/test.php
Pour débugger :
$ tail /var/log/apache2/error.log
Mise à jour
[modifier | modifier le wikicode]Pour PHP 7 ou 8 sur Ubuntu :
sudo add-apt-repository ppa:ondrej/php
Sur Debian :
sudo wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg sudo sh -c 'echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" > /etc/apt/sources.list.d/php.list'
Puis :
sudo apt update sudo apt install php8.2 php8.2-common php8.2-cli php8.2-fpm sudo a2enmod php8.2
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 :
sudo update-rc.d apache2 disable sudo update-rc.d mysql disable
Bibliothèques
[modifier | modifier le wikicode]Voici une liste de bibliothèques fréquemment utilisées dans les applications :
# apt-get install -y \ php8.2-mysql \ php8.2-cli \ php8.2-gd \ php8.2-curl \ php8.2-mbstring \ php8.2-xml
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
[modifier | modifier le wikicode]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" :
sudo apt-get install ppa-purge sudo ppa-purge ppa:ondrej/php-7.0
Apache sur Gentoo
[modifier | modifier le wikicode]Premièrement il faut installer Apache :
emerge apache
Ensuite, il faut installer PHP :
emerge dev-lang/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
[modifier | modifier le wikicode]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 :
shell> groupadd mysql shell> useradd -r -g mysql mysql shell> cd /usr/local shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz shell> ln -s full-path-to-mysql-VERSION-OS mysql shell> cd mysql shell> chown -R mysql . shell> chgrp -R mysql . shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data shell> bin/mysqld_safe --user=mysql &
APT
[modifier | modifier le wikicode]$ sudo apt-get install mysql-server mysql_secure_installation
Variante
[modifier | modifier le wikicode]La dénomination des paquets mentionnés peut varier légèrement selon la version. Dans un terminal, entrez :
$ sudo apt-get install mysql-server
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 : [1])
Lancez ensuite la commande :
cd && sudo mysql_secure_installation
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 :
$ sudo dpkg-reconfigure mysql-server
et de répondre "Non" à la question du démarrage systématique de MySQL.
Sur Gentoo
[modifier | modifier le wikicode]emerge mysql
Modules
[modifier | modifier le wikicode]De nombreux modules complémentaires peuvent être installés sur Apache.
Pour les lister, on utilise apachectl
(parfois apache2ctl
) :
apachectl -t -D DUMP_MODULES
ou
apache2ctl -M
Pour activer un module :
a2enmod Nom_du_module
Un fichier est alors créé dans /etc/apache2/mods-enabled/.
Exemple pour la réécriture d'URL :
a2enmod rewrite
Pour le désactiver :
a2dismod Nom_du_module
La configuration du module reste toutefois disponible dans /etc/apache2/mods-available/.
phpenmod mbstring
Sites
[modifier | modifier le wikicode]Pour lister les sites du serveur :
apachectl -S
Pour activer un site :
a2ensite Nom_du_site
Le fichier du site est alors visible dans /etc/apache2/sites-enabled/.
Pour le désactiver :
a2dissite Nom_du_site
Le site est dans /etc/apache2/sites-available/.
Problème d'encodage d'Apache2
[modifier | modifier le wikicode]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) :
Content-Type: text/html; charset=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 :
$ sudo gedit /etc/apache2/apache2.conf
Encodage par défaut en Latin1 (ISO-8859-1)
[modifier | modifier le wikicode]Cherchez la ligne suivante :
#AddDefaultCharset ISO-8859-1
Décommentez-la en enlevant le # :
AddDefaultCharset ISO-8859-1
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 :
AddDefaultCharset ISO-8859-15
ainsi que la ligne suivante dans le paragraphe en-dessous :
AddCharset ISO-8859-15 .iso8859-15 .latin15 .fr
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 :
$ sudo /etc/init.d/apache2 reload
Aucun encodage par défaut
[modifier | modifier le wikicode]Il est également possible de s'affranchir de tout encodage par défaut, de la manière suivante :
Cherchez la directive AddDefaultCharset :
AddDefaultCharset ISO-8859-1
Remplacez l'attribut par la valeur Off :
AddDefaultCharset Off
Là encore, on demandera à Apache de relire sa configuration :
$ sudo /etc/init.d/apache2 reload
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
[modifier | modifier le wikicode]Dans docker-compose.yml, il convient de partager un volume pour les importations de dumps :
version: "3.8" services: mysql: hostname: mysql image: mysql:8.0.32 ports: - "3306:3306" volumes: - ./mysql:/var/www/ environment: TZ: Europe/Paris MYSQL_ALLOW_EMPTY_PASSWORD: true
Hello world
[modifier | modifier le wikicode]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
- Linux :
- Ouvrir une fenêtre SQL dans PhpMyAdmin (ex : http://localhost/modules/phpmyadmin/#PMAURL-1:server_sql.php?server=1).
SELECT "hello world"; +-------------+ | hello world | +-------------+ | hello world | +-------------+ 1 row in set (0.00 sec)
mysql -pmon_mot_de_passe
.Ainsi, on peut exécuter plusieurs commandes SQL en une seule commande shell :
mysql -pmon_mot_de_passe << EOF SELECT "hello world"; EOF
Références
[modifier | modifier le wikicode]- ↑ 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
[modifier | modifier le wikicode]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 :
Installation de PhpMyAdmin
[modifier | modifier le wikicode]Docker Compose
[modifier | modifier le wikicode]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 :
version: "3.8" services: traefik: image: traefik:v2.5 command: - --providers.docker - --entryPoints.web.address=:80 - --entryPoints.websecure.address=:443 ports: - "80:80" - "443:443" volumes: - /var/run/docker.sock:/var/run/docker.sock mysql: hostname: mysql image: mysql:8.0.32 ports: - "3306:3306" environment: TZ: Europe/Paris MYSQL_ALLOW_EMPTY_PASSWORD: true phpmyadmin: image: phpmyadmin labels: - traefik.enable=true - traefik.http.routers.adminer.rule=Host(`phpmyadmin.localhost`) - traefik.http.routers.adminer.entryPoints=web depends_on: - mysql environment: - PMA_HOST=mysql
Windows
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Sous Linux, il est fournit avec LAMP. Depuis un tout-en-un, il suffit de créer un chemin accessible depuis le serveur Web :
sudo ln -s /usr/share/phpmyadmin /var/www/phpmyadmin
Si ça ne fonctionne pas, suivre le paragraphe suivant.
Installer Apache et PHP avec PhpMyAdmin
[modifier | modifier le wikicode]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.
$ sudo apt-get install phpmyadmin
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 :
# deb http://fr.archive.ubuntu.com/ubuntu hoary universe
(cette ligne est dans certain cas '# deb http://archive.ubuntu.com/ubuntu/ hoary universe main restricted multiverse')
# deb-src http://fr.archive.ubuntu.com/ubuntu hoary universe
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
Catégories ---> Dépôts ----> Ajouter et ensuite, sélectionner : maintenu par la communauté universe...
Lancer le chargement des nouvelles sources :
$ sudo apt-get update
Puis lancer l'installation de PhpMyAdmin comme décrit ci-dessus.
Test
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]PHP
[modifier | modifier le wikicode]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 :
max_execution_time = 600 ... max_input_time = 600 ... upload_max_filesize = 100M ... post_max_size = 100M
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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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[1].
Il est possible d'y stocker l'historique des requêtes entrées sur le serveur en activant :
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
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.
SET GLOBAL general_log = 'OFF';
SELECT * FROM mysql.general_log;
INFORMATION_SCHEMA
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Rechercher un nom de colonne dans toutes les tables d'une base
[modifier | modifier le wikicode]SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ma_recherche%';
Lister les tables les plus volumineuses
[modifier | modifier le wikicode]Pour trier les tables par taille :
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE table_schema = "ma_base"
ORDER BY DATA_LENGTH DESC;
Pour avoir la taille d'une table, ajouter :
AND table_name = "ma_table";
Lister les bases
[modifier | modifier le wikicode]Les commandes SQL suivantes fournissent les informations relatives aux bases de données situées sur le serveur courant.
Toutes :
SHOW DATABASES;
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
[modifier | modifier le wikicode]SHOW DATABASES LIKE 'expression';
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 :
SHOW DATABASES LIKE 'wiki%';
Filtres complexes
[modifier | modifier le wikicode]En utilisant la clause WHERE
:
SHOW DATABASES WHERE conditions;
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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode] USE `database`;
SHOW TABLES;
SHOW TABLES FROM `database`;
Les deux formes sont équivalentes.
Appliquer un filtre
[modifier | modifier le wikicode]La syntaxe est la même que pour les bases :
SHOW TABLES LIKE `expression`;
SHOW TABLES WHERE condition;
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` :
SHOW FULL TABLES;
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 :
SHOW FULL TABLES WHERE `Table_type`='VIEW';
Filtrer les tables ouvertes
[modifier | modifier le wikicode]La liste des tables non temporaires (sans les vues) ouvertes dans le cache :
SHOW OPEN TABLES;
Supprimer toutes les tables
[modifier | modifier le wikicode]Il faut exécuter le résultat de cette requête :
SELECT CONCAT('DROP ', table_name, ';') as stmt
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'nom_de_la_base';
Supprimer toutes les vues
[modifier | modifier le wikicode]SELECT CONCAT('DROP VIEW ', table_name, ';') as stmt
FROM information_schema.views
WHERE TABLE_SCHEMA = 'nom_de_la_base';
Supprimer toutes les procédures stockées
[modifier | modifier le wikicode]SELECT CONCAT('DROP ', ROUTINE_TYPE, ' `', ROUTINE_SCHEMA, '`.`', ROUTINE_NAME, '`;') as stmt
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'nom_de_la_base';
Lister les champs
[modifier | modifier le wikicode]Les commandes suivantes correspondent aux informations de la table COLUMNS de INFORMATION_SCHEMA.
mysqlshow
le permet également.
DESCRIBE
[modifier | modifier le wikicode] USE `base`;
DESCRIBE `table`;
-- ou
DESCRIBE `base`.`table`;
Le résultat contient six colonnes :
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
... | ... | ... | ... | ... | ... |
DESC
est un alias de DESCRIBE
.
USE `base`;
DESC `table` 'filtre';
'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' :
DESC `table` 'wiki%';
EXPLAIN
[modifier | modifier le wikicode]Synonyme de DESC
:
EXPLAIN `table`;
SHOW FIELDS
[modifier | modifier le wikicode]Autre synonyme de DESC
:
SHOW FIELDS FROM `table`;
Remarque : le mot FULL rajoute une colonne "Privileges" et une "Comment" :
SHOW FULL FIELDS FROM `table`;
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
[modifier | modifier le wikicode]Autre synonyme de DESC
:
SHOW COLUMNS FROM `table`;
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 :
SHOW COLUMNS FROM `table` FROM `base`;
-- ou
SHOW COLUMNS FROM `base`.`table`;
Lister les indexes
[modifier | modifier le wikicode]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
.
SHOW INDEX FROM `TABLE`;
SHOW INDEX FROM `TABLE` FROM `bases`;
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 :
DROP INDEX `date_2` on `Table1`
Un alias existe aussi : SHOW KEYS FROM `TABLE`;
.
Lister les clés étrangères
[modifier | modifier le wikicode]Pour le nom des clés étrangères d'une table :
SELECT column_name, constraint_name FROM `information_schema`.`KEY_COLUMN_USAGE` where table_name = 'maTable'
Références
[modifier | modifier le wikicode]
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 :
mysql> SELECT user_id, group_id FROM user,group LIMIT 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group LIMIT 1' at line 1
vs :
mysql> SELECT user_id, group_id FROM `user`,`group` LIMIT 1; ERROR 1146 (42S02): Table 'savannah.group' doesn't exist
Montre qu'il manquait un s à group:
mysql> SELECT user_id, group_id FROM `user`,`groups` LIMIT 1; +---------+----------+ | user_id | group_id | +---------+----------+ | 100 | 2 | +---------+----------+ 1 row in set (0.02 sec)
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 :
RENAME TABLE `user` TO ````
Par contre cette syntaxe n'est pas portable, car le standard SQL recommande le guillemet ("
).
Syntaxe
Principe
[modifier | modifier le wikicode]On peut diviser le vocabulaire SQL en trois groupes :
- Le LDD est composé de
CREATE
,ALTER
etDROP
. 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
etDELETE
. Pour ajouter, modifier et supprimer les données stockées dans les bases. - Le LCD représente
GRANT
etREVOKE
. 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
etHELP
. 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
etROLLBACK [TO SAVEPOINT]
. Affiliable au LCD dans le modèle à trois catégories.
Visualisation
[modifier | modifier le wikicode]- 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`
).
SELECT * FROM MaTable
SELECT * FROM MaTable1, MaTable2, ...
SELECT champ1, champ2, ... FROM MaTable1, MaTable2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY champ
SELECT ... FROM ... WHERE condition GROUPBY champ HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY champ1, champ2
SELECT ... FROM ... WHERE condition ORDER BY champ1, champ2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT champ1 FROM ...
SELECT DISTINCT champ1, champ2 FROM ...
Jointures
[modifier | modifier le wikicode] SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1, t2 WHERE t1.id1 = t2.id2 AND condition
SELECT ... FROM t1 INNER JOIN t2 ON (t1.id1 = t2.id2) WHERE condition
SELECT ... FROM t1 NATURAL JOIN t2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
Conditions
[modifier | modifier le wikicode] champ1 = valeur1
champ1 <> valeur1
champ1 LIKE 'valeur _ %'
champ1 IS NULL
champ1 IS NOT NULL
champ1 IS IN (valeur1, valeur2)
champ1 IS NOT IN (valeur1, valeur2)
champ1 BETWEEN valeur1 AND valeur2
condition1 AND condition2
condition1 OR condition2
Modification du contenu
[modifier | modifier le wikicode] INSERT INTO MaTable1 (champ1, champ2, ...) VALUES (valeur1, valeur2, ...)
DELETE FROM MaTable1 / TRUNCATE MaTable1
DELETE FROM MaTable1 WHERE condition
-- jointure :
DELETE FROM MaTable1, MaTable2 WHERE MaTable1.id1 = MaTable2.id2 AND condition
UPDATE MaTable1 SET champ1=nouvelle_valeur1 WHERE condition
-- jointure :
UPDATE MaTable1, MaTable2 SET champ1=nouvelle_valeur1, champ2=nouvelle_valeur2, ... WHERE MaTable1.id1 = MaTable2.id2 AND condition
Naviguer dans MySQL
[modifier | modifier le wikicode] SHOW DATABASES
SHOW TABLES
SHOW INDEX FROM MaTable
SHOW FIELDS FROM MaTable / DESCRIBE MaTable
SHOW CREATE TABLE MaTable
SHOW PROCESSLIST
KILL numero
USE ma_bdd
Créer / supprimer une base
[modifier | modifier le wikicode] CREATE DATABASE MaBase
CREATE DATABASE MaBase CHARACTER SET utf8
DROP DATABASE `MaBase`
ALTER DATABASE MaBase CHARACTER SET utf8
Créer/supprimer/modifier une table
[modifier | modifier le wikicode] CREATE TABLE MaTable (champ1 type1, champ2 type2, ...)
CREATE TABLE MaTable (champ1 type1, champ2 type2, ..., INDEX (champ))
CREATE TABLE MaTable (champ1 type1, champ2 type2, ..., PRIMARY KEY (champ1))
CREATE TABLE MaTable (champ1 type1, champ2 type2, ..., PRIMARY KEY (champ1, champ2))
CREATE TABLE MaTable1 (fk_champ1 type1, champ2 type2, ...,
FOREIGN KEY (fk_champ1) REFERENCES MaTable2 (t2_champA))
[ON UPDATE|ON DELETE] [CASCADE|SET NULL]
CREATE TABLE MaTable1 (fk_champ1 type1, fk_champ2 type2, ...,
FOREIGN KEY (fk_champ1, fk_champ2) REFERENCES MaTable2 (t2_champA, t2_champB))
CREATE TABLE IF NOT EXISTS MaTable (...)
CREATE TABLE MaTable (champ1 type1, champ2 type2, ...) SELECT ...
CREATE TEMPORARY TABLE MaTable (...)
DROP TABLE MaTable
DROP TABLE IF EXISTS MaTable
DROP TABLE MaTable1, MaTable2, ...
ALTER TABLE MaTable ADD (champ1 type1, champ2 type2, ...)
ALTER TABLE MaTable MODIFY champ1 type1
ALTER TABLE MaTable MODIFY champ1 type1 NOT NULL ...
ALTER TABLE MaTable CHANGE ancien_nom_champ1 nouveau_nom_champ1 type1
ALTER TABLE MaTable CHANGE ancien_nom_champ1 nouveau_nom_champ1 type1 NOT NULL ...
ALTER TABLE MaTable ALTER champ1 SET DEFAULT ...
ALTER TABLE MaTable ALTER champ1 DROP DEFAULT
ALTER TABLE MaTable ADD INDEX (champ);
DROP INDEX champ ON MaTable;
ALTER TABLE ancien_nom RENAME nouveau_nom;
Clés primaires et étrangères
[modifier | modifier le wikicode]Lecture
[modifier | modifier le wikicode]Les clés (primaires et étrangères) d'une table sont incluses dans sa description :
SHOW CREATE TABLE MaTable
Mais on peut aussi ne sélectionner qu'elles :
SELECT *
FROM `information_schema`.`TABLE_CONSTRAINTS`
WHERE `TABLE_NAME` = 'MaTable'
Sinon :
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'MaBase' AND REFERENCED_TABLE_NAME = 'MaTable';
Création
[modifier | modifier le wikicode] CREATE TABLE MaTable (..., PRIMARY KEY (champ1, champ2))
CREATE TABLE MaTable (..., FOREIGN KEY (champ1, champ2) REFERENCES MaTable2 (t2_champ1, t2_champ2))
Pour ajouter une clé étrangère à une table existante :
ALTER TABLE MaTable ADD FOREIGN KEY (maTable2_id) REFERENCES maTable2(id);
CONSTRAINT
[modifier | modifier le wikicode]Facultativement, la clé étrangère peut aussi être nommée : on la baptise après le mot CONSTRAINT
.
ALTER TABLE MaTable ADD CONSTRAINT fk_maTable2_id FOREIGN KEY (maTable2_id) REFERENCES maTable2(id);
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 :
ALTER TABLE `MaTable`
ADD CONSTRAINT `fk_maTable2_id` FOREIGN KEY (`MaTable2_id`) REFERENCES `MaTable2`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
CHECK
[modifier | modifier le wikicode]A ne pas confondre avec CONSTRAINT
, il gère les contraintes indépendantes des autres tables[1]. Exemples :
CREATE TABLE MaTable (
champ1 INT CHECK (champ1 > 10),
champ2 INT CONSTRAINT champ2_positif CHECK (champ2 > 0),
CHECK (champ1 > champ2)
)
Suppression
[modifier | modifier le wikicode]Pour désactiver les contraintes le temps d'une session :
SET FOREIGN_KEY_CHECK = 0;
Pour le faire globalement :
SET GLOBAL FOREIGN_KEY_CHECKS = 0;
Pour faire supprimer définitivement une contrainte :
ALTER TABLE MaTable1
DROP FOREIGN KEY FK_MaTable1_MaTable2
Mais parfois pour éviter les vérifications des contraintes, il suffit de lancer la commande suivante le temps d'un UPDATE avec un WHERE :
SET SQL_SAFE_UPDATES = 0;
Lors du clustering comme avec Galera, il faut avoir une clé primaire par table.
Créer/supprimer une vue
[modifier | modifier le wikicode] CREATE VIEW nomvue AS SELECT champ1, champ2 FROM MaTable1 -- ou
CREATE VIEW nomvue (champ1, champ2...) AS SELECT champ1, champ2 FROM MaTable1
ALTER VIEW nomvue (champ1, champ2...) AS SELECT champ2 FROM MaTable1;
DROP VIEW nomvue;
Il est impossible d'ajouter un index à une vue[2].
Permissions
[modifier | modifier le wikicode]Pour les lister :
SHOW GRANTS FOR CURRENT_USER();
Pour les définir:
GRANT ALL PRIVILEGES ON base.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'utilisateur'@'hôte'; -- une seule permission
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'utilisateur'@'hôte'; -- toutes les permissions
SET PASSWORD = PASSWORD('nouveau_pass')
SET PASSWORD FOR 'utilisateur'@'hôte' = PASSWORD('nouveau_pass')
SET PASSWORD = OLD_PASSWORD('nouveau_pass')
DROP USER 'utilisateur'@'hôte'
Oubli de mot de passe
[modifier | modifier le wikicode]$ service mysql stop $ mysqld_safe --skip-grant-MaTables > UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root'; ## Tuer mysqld_safe, avec Control + \ $ service mysql start
Réparer les tables après un arrêt soudain
[modifier | modifier le wikicode]mysqlcheck --all-databases mysqlcheck --all-databases --fast
Relancer la synchronisation de la base du serveur secondaire
[modifier | modifier le wikicode]$ mysql mysql> slave start; mysql> show slave status\G
Manipuler des variables
[modifier | modifier le wikicode]Les définitions sont effectuées à l'aide des mots clés "select" (suivi de ":=") ou "set" (avec "=") :
SELECT @test := 2;
SELECT @test + 1
SET @date1='date une', @date1='date deux'
Pour les afficher ensuite :
show variables like 'test';
show variables like 'date1';
show variables like 'date2';
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 :
mysql> set @@global.max_connections = 1000; mysql> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 60 | +---------------+-------+ 1 row in set (0.00 sec) mysql> set @@session.wait_timeout=120;
En cas de conversion de dates au format Text en Datetime, cela les efface toutes à 0000-00-00 00:00:00
Variables globales
[modifier | modifier le wikicode]Pour avoir la version de MySQL :
SHOW VARIABLES LIKE 'version';
Références
[modifier | modifier le wikicode]
Variables utilisateurs
Variables locales
[modifier | modifier le wikicode]Les types des variables MySQL sont forcément scalaires[1]. 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[2].
Elles sont déclarées après DECLARE
avec leur nom, leur type, et éventuellement leur valeur par défaut[3] :
DECLARE MaVariable1 INT DEFAULT 1;
Variables de session
[modifier | modifier le wikicode]Les variables obéissent à certaines règles :
- Leurs noms commencent par "@" (ex :
@total
). - Elles sont déclarées avec le mot
SET
, ou bienSELECT
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.
select @test := 2;
select @test + 1; -- renvoie 3
set @datedebut='date_de_debut', @datefin='date_de_fin';
SELECT @nbmembre:=count(*) FROM membres;
select @numzero := count(*) from table1 where field=0;
select @numdistint := count(distinct field) from table1 where field <> 0 ;
select @numzero @numdistinct;
Pour copier dans valeurs d'une sélection dans une ou plusieurs variables :
SET @id = 0, @nom = '';
SELECT id, nom INTO @id, @nom FROM table1 LIMIT 1;
SELECT @id, @nom;
Elles peuvent être utiles quand on doit agréger plusieurs valeurs sans jointures entre leurs tables. Ex :
SET @idCountry = (SELECT `id` FROM `country` WHERE `code` = "FR" LIMIT 1);
SET @idLanguage = (SELECT `id` FROM `language` WHERE `code` = "fr" LIMIT 1);
INSERT INTO `page` (`country`, `language`, `description`)
VALUES (@idCountry, @idLanguage, 'Text')
Variables globales
[modifier | modifier le wikicode]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 :
mysql> set @@global.max_connections = 1000; mysql> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 60 | +---------------+-------+ 1 row in set (0.00 sec) mysql> set @@session.wait_timeout=120;
sql_mode
[modifier | modifier le wikicode]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[4] :
SELECT @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Pour le modifier au démarrage dans Docker compose :
command: --sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Références
[modifier | modifier le wikicode]
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 :
SELECT
CONCAT(nom,' ', prenom) AS nom_complet,
pseudonyme AS pseudo
FROM
table1
ORDER BY
nom_complet;
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.
SELECT
COUNT(R.ID_reservation), U.Localisation
FROM
Utilisateurs U
LEFT OUTER JOIN
Reservations AS R
ON
U.ID_Utilisateur = R.ID_Utilisateur AND
R.ID_Projet = '10'
GROUP BY
U.Localisation;
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 :
SELECT
p.nom AS parent,
e.nom AS enfant,
MIN((TO_DAYS(NOW())-TO_DAYS(e.date_naissance))/365) AS agemini
FROM
personne AS p
LEFT JOIN
personne AS e
ON
p.nom=e.parent WHERE e.nom IS NOT NULL
GROUP BY
parent HAVING agemini > 50 ORDER BY p.date_naissance;
Types de données
Types de données principaux
[modifier | modifier le wikicode]Voici les valeurs acceptées pour chaque type de variable en octets, au-delà desquelles un stockage provoque un débordement[1] :
TINYINT (1o : -127+128)
SMALLINT (2o : +-65 000)
MEDIUMINT (3o : +-16 000 000)
INT (4o : +- 2 000 000 000)
BIGINT (8o : +- 9 trillions)
Intervalle précis : -(2^(8*N-1)) -> (2^8*N)-1
/!\ INT(2) = "2 chiffres affichés" -- ET NON PAS "nombre à 2 chiffres"
FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53)
/!\ 8,3 -> 12345,678 -- PAS 12345678,123!
TIME (HH:MM)
YEAR (AAAA)
DATE (AAAA-MM-JJ)
DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (comme date, mais 1970->2038, compatible Unix)
VARCHAR(ligne)
TEXT (multi-lignes; taille max=65535)
BLOB (binaire; taille max=65535)
Variantes :
TINY (max=255)
MEDIUM (max=~16000)
LONG (max=4Go)
Ex : TINYTEXT, LONGBLOB, MEDIUMTEXT
ENUM ('valeur1', 'valeur2', ...) -- (default NULL, ou '' si NOT NULL)
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
[modifier | modifier le wikicode]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[2].
TEXT et BLOB
[modifier | modifier le wikicode]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[3] :
TINYBLOB
: 255 o.MEDIUMBLOB
: 16 Mo.LONGBLOB
: 4,29 Go.
INTEGER
[modifier | modifier le wikicode]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 107.
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
.
tinyint(1)
.Le nombre entre parenthèses après les types entiers indique sur combien de chiffres l'entier stocké est prévu pour être affiché[4]. Toutefois s'il est plus long que cela, cela n'empêchera pas son stockage.
DECIMAL
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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 ().
mysql> SET time_zone = '+00:00'; -- GMT Query OK, 0 rows affected (0.00 sec)
mysql> SELECT FROM_UNIXTIME(-1); +-------------------+ | FROM_UNIXTIME(-1) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(0); -- "Epoch" +---------------------+ | FROM_UNIXTIME(0) | +---------------------+ | 1970-01-01 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916799); +---------------------------+ | FROM_UNIXTIME(2145916799) | +---------------------------+ | 2037-12-31 23:59:59 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT FROM_UNIXTIME(2145916800); +---------------------------+ | FROM_UNIXTIME(2145916800) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec)
Les fonctions de manipulation de date seront exposées dans un chapitre ultérieur.
SET et ENUM
[modifier | modifier le wikicode]SET
est un type dont les valeurs sont prédéfinies dans une liste lors de la création de la table[5].
ENUM
est similaire mais restreint à un seul membre, alors que SET
autorise le stockage de n'importe lesquelles de ses valeurs ensemble.
Exemple :
SET("madame", "monsieur") -- autorise un champ vide, "madame", "monsieur", "madame, monsieur", ou "monsieur, madame"
ENUM("madame", "monsieur") -- autorise un champ vide, "madame" ou "monsieur"
Vérification
[modifier | modifier le wikicode]Pour connaitre le type d'un champ :
DESCRIBE MaTable MonChamp
Pour vérifier que le contenu d'un champ est un toujours un nombre entier :
SELECT MonChamp FROM MaTable WHERE NOT MonChamp REGEXP '^-?[0-9]+$';
Références
[modifier | modifier le wikicode]- ↑ 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, (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
[modifier | modifier le wikicode]CREATE DATABASE Nom_de_la_base;
NB : dans MySQL, CREATE SCHEMA
est un parfait synonyme de CREATE DATABASE
, contrairement à d'autres SGBD comme Oracle ou SQL Server.
En ligne de commande
[modifier | modifier le wikicode]mysqladmin create
permet de le faire en ligne de commande[1].- Sinon :
echo 'CREATE DATABASE IF NOT EXISTS ma_base COLLATE utf8mb4_unicode_ci;'|mysql -u root
Suppression
[modifier | modifier le wikicode] DROP DATABASE Nom_de_la_base;
mysqladmin drop
permet de le faire en ligne de commande. Le paramètre -f
force celle-ci sans poser de question.
Renommage
[modifier | modifier le wikicode]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[2].
Il reste toutefois la ligne de commande pour le faire en plusieurs étapes :
mysqladmin create Nom_de_la_nouvelle_base mysqldump --opt Nom_de_la_base | mysql Nom_de_la_nouvelle_base mysqladmin drop -f Nom_de_la_base
Une autre option avec les droits root, est de renommer le répertoire de la base :
cd /var/lib/mysql/ /etc/init.d/mysql stop mv Nom_de_la_base/ Nom_de_la_nouvelle_base/ /etc/init.d/mysql start
Après renommage, il convient de migrer les permissions :
UPDATE mysql.db SET `Db`='Nom_de_la_nouvelle_base' WHERE `Db`='Nom_de_la_base';
FLUSH PRIVILEGES;
Copie
[modifier | modifier le wikicode]Avec mysqldump
[modifier | modifier le wikicode]- Pour plus de détails voir : MySQL/mysqldump.
Avec des outils de modélisation
[modifier | modifier le wikicode]Ces logiciels permettent de représenter les tables sous formes de diagrammes.
phpMyAdmin
[modifier | modifier le wikicode]- Pour plus de détails voir : MySQL/PhpMyAdmin.
MySQL Workbench
[modifier | modifier le wikicode]MySQL Workbench permet également la migration depuis d'autres bases de données, telles que Microsoft SQL Server[3].
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
[modifier | modifier le wikicode]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"Télécharger MySQL pour version 5.0).
Sous Linux :
- Remplacer le fichier libmysqlclient.so de DBDesigner par le nouveau :
sudo ln -sf /usr/lib/libmysqlclient.so.10 /usr/lib/DBDesigner4/libmysqlclient.so
- 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 :
rpm2cpio x.rpm | cpio -i
- Récupérer libXft.so.1.1 dans ce package et l'installer :
sudo cp libXft.so.1.1 /usr/lib ldconfig
Maintenant DBDesigner4 peut se connecter à MySQL5.
Kexi
[modifier | modifier le wikicode]Il existe aussi Kexi de Calligra Suite, téléchargeable sur http://userbase.kde.org/Calligra/Download/fr.
OpenOffice Base et ODBC
[modifier | modifier le wikicode]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
:
aptitude install mysql-client
Sous Fedora/CentOS :
yum install mysql
Avant d'installer ODBC, test la connexion distante localement :
$ mysql -h mysqlhost -u user1 mysqldatabase -p Enter password: PassUser1
Il faut créer la base mysqldatabase
et l'utilisateur user1
sur mysqlhost
.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysqldatabase | +--------------------+ 2 rows in set (0.00 sec) .... mysql> quit; Bye
Toujours sur la machine cliente :
aptitude install libmyodbc unixodbc
Pour Fedora/CentOS :
yum install mysql-connector-odbc unixODBC
Les fichiers /etc/odbc.ini
et /etc/odbcinst.ini
sont créés.
odbcinst.ini
déclare le pilote ODBC disponible.
Exemple pour Debian :
[MySQL]
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
CPTimeout =
CPReuse =
FileUsage = 1
Pour CentOS :
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc3.so
Setup = /usr/lib/libodbcmyS.so
FileUsage = 1
Maintenant odbcinst
est utilisable :
# odbcinst -j
unixODBC 2.2.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini
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 []) :
[MySQL-test]
Description = MySQL ODBC Database
TraceFile = stderr
Driver = MySQL
SERVER = mysqlhost
USER = user1
PASSWORD =
DATABASE = mysqldatabase
Dans ce cas, le DSN est appelé MySQL-test.
Ensuite pour tester, utiliser la commande isql :
$ isql -v MySQL-test user1 PassUser1 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-------------------+ | Database | +-------------------+ | information_schema| | mysqldatabase | +-------------------+ 2 rows affected 2 rows returned SQL> quit;
Depuis OOo :
-> File -> New -> Database -> Connecting to an existing database -> MySQL -> Next -> Connect using ODBC -> Next -> Choosing a Data Source -> MySQL-test -> Next -> Username : user1 (tick password required) -> Yes, register the database for me -> Finish
À 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
[modifier | modifier le wikicode]- Sous Linux, le mot de passe est demandé après entrée de la commande :
mysql -h localhost -u root -p MaBase < MaBase.sql
- 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 :
"C:\Program Files (x86)\EasyPHP\binaries\mysql\bin\mysql.exe" -h localhost -u root MaBase < MaBase.sql
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é :
gunzip < ~/my_dump.xml.gz | mysql -u my_user -p my_base
Références
[modifier | modifier le wikicode]
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
[modifier | modifier le wikicode]Pour exporter la base de donnée « myBase », on utilise la commande suivante :
mysqldump -u root -p myBase > myBase_backup.sql
Ceci fera l'export dans un fichier « myBase_backup.sql ».
Exemple de dump d'une base :
mysqldump --user=root --password=mon_mdp ma_base > my_base.sql
Pour dumper toutes les bases :
mysqldump --user=root --password=mon_mdp --all-databases > all_db.sql
Pour dumper certains enregistrements de certaines tables :
mysqldump --database ma_base --tables ma_table --where="id in (1, 2, 3)" --user=root --password=mon_mdp > my_query.sql
Sans les données
[modifier | modifier le wikicode]Pour ne récupérer que le schéma :
mysqldump -f --no-data --user=root --password=mon_mdp ma_base > my_base_schema.sql
Au format CSV
[modifier | modifier le wikicode]mysql -e "select * from ma_base.ma_table where id in (1, 2, 3)" --user=root --password=mon_mdp > my_query.csv
Aux formats Oracle, PostgreSQL, Microsoft SQL Server...
[modifier | modifier le wikicode]L'option --compatible
permet de spécifier à mysqldump le format à utiliser pour être compatible avec les bases de données existantes. Exemple :
mysqldump --compatible=oracle -u root -p myBase > myBase_backup.sql
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
[modifier | modifier le wikicode]Pour définir le backup automatique d'une base tous les soirs à minuit[1], sous Linux :
$ crontab -e 0 0 * * * /usr/local/bin/mysqldump -uLOGIN -PPORT -hHOST -pPASS base1 | gzip -c > `date “+\%Y-\%m-\%d”`.gz
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
[modifier | modifier le wikicode]Pour importer une base de données sauvegardée via mysqldump, on utilise la commande cliente mysql et une redirection en entrée :
# mysql -u root -p myBase < myBase_backup.sql
En cas de gros volume, il est plus rapide de copier les .bin des bases, au lieu d'utiliser mysqldump
.
Préparation
[modifier | modifier le wikicode]Pour réinjecter le dump dans d'autres bases, il vaut mieux nettoyer la base de destination[2].
mysqladmin drop -f base2 mysqladmin create base2 mysqldump --opt base1 | mysql base2
Vers une base distante
[modifier | modifier le wikicode]En utilisant mysqldump et ssh, on peut dupliquer une base de données sur une machine distante :
mysqldump testdb -p''<mot de passe local>'' | ssh pc211 'echo "create database dbalex;" | mysql -p''<mot de passe distant>'' ; cat - | mysql -p''<mot de passe distant>'' dbalex'
Manipulation de table
CREATE TABLE
[modifier | modifier le wikicode]La syntaxe de création des tables d'une base est ainsi :
CREATE TABLE tablename (field_name_1 DataType, field_name_2 DataType)
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[3].
Création à partir d'une autre table
[modifier | modifier le wikicode]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 :
CREATE TABLE LearnHindi
SELECT english.tag, english.Inenglish as english, hindi.Inhindi as hindi
FROM english, hindi
WHERE english.tag = hindi.tag
Auto-incrémentation
[modifier | modifier le wikicode]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 :
ALTER TABLE tablename AUTO_INCREMENT = 1
Moteurs
[modifier | modifier le wikicode]Le moteur de stockage d'une table est défini par la clause ENGINE=
. On en distingue deux principaux :
Tables d'archive
[modifier | modifier le wikicode]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 :
CREATE table t1 (
a int,
b varchar(32))
ENGINE=ARCHIVE
Tables temporaires
[modifier | modifier le wikicode]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" :
CREATE TEMPORARY TABLE IF NOT EXISTS MaTableTemp1 AS (SELECT * FROM MaTable1)
Exemple avec paramètre nommé :
CREATE TEMPORARY TABLE IF NOT EXISTS MaTableTemp1(id INT) AS (SELECT id FROM MaTable1)
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 :
CREATE TEMPORARY TABLE IF NOT EXISTS MaTableTemp1(id1 INT) AS (SELECT id FROM MaTable1);
SHOW FIELDS FROM MaTableTemp1;
Field Type Null Key Default Extra id1 int(11) YES NULL id int(11) NO 0
Toutes les tables temporaires sont supprimées à la fin de la connexion MySQL qui les a créée[4].
Copier une table
[modifier | modifier le wikicode]Pour obtenir la même structure (noms et types des champs, index, mais aucun enregistrement) :
CREATE TABLE `new1` LIKE `old1`;
Pour dupliquer le contenu d'une table dans le résultat :
INSERT INTO `new1` SELECT * FROM `old1`;
Pour avoir le code de création :
SHOW CREATE TABLE `old1`
CREATE TABLE `new2` LIKE `old1`;
INSERT INTO `new2` SELECT * FROM `old1` WHERE id > 10;
SHOW CREATE TABLE `new2`;
ALTER TABLE
[modifier | modifier le wikicode]ALTER TABLE
sert à ajouter, supprimer ou modifier la structure des tables (colonnes, index, propriétés).
Ajouter une colonne
[modifier | modifier le wikicode] ALTER TABLE awards
ADD COLUMN AwardCode int(2)
Modifier une colonne
[modifier | modifier le wikicode]Pour changer les caractéristiques :
ALTER TABLE awards
CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL
ALTER TABLE awards
MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL
Renommer une colonne
[modifier | modifier le wikicode]Pour renommer une colonne :
ALTER TABLE awards CHANGE `AwardCode` `newcAwardCode` VARCHAR(2) NOT NULL;
Supprimer une colonne
[modifier | modifier le wikicode] ALTER TABLE awards
DROP COLUMN AwardCode
Reclasser les enregistrements d'une table
[modifier | modifier le wikicode] ALTER TABLE awards ORDER BY id
Renommer une table
[modifier | modifier le wikicode]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.
-- Renommage :
ALTER TABLE `old` RENAME `new`
-- Raccourci :
RENAME TABLE `old_name` TO `new_name`
-- Plusieurs :
RENAME TABLE `old1` TO `new1`, `old2` TO `new2`, ...
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.
DROP TABLE
[modifier | modifier le wikicode]DROP TABLE `awards`
--Supprime toute la table (enregistrements et structure).
-- Plusieurs :
DROP TABLE `table1`, `table2`, ...
-- Avec vérification :
DROP TEMPORARY TABLE `table`;
DROP TABLE `table` IF EXISTS;
CASCADE
[modifier | modifier le wikicode]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 :
CREATE TABLE adresse_facturation (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
id_utilisateur int(11) NOT NULL,
adresse varchar(255),
PRIMARY KEY (id),
FOREIGN KEY (id_utilisateur)
REFERENCES utilisateur (id)
ON DELETE CASCADE
)
Unique
[modifier | modifier le wikicode]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 :
ALTER TABLE MaTable ADD UNIQUE (user_id)
Pour ajouter une contrainte unique nommée :
ALTER TABLE MaTable ADD UNIQUE KEY UNIQ_E6F03AD9A76ED395 (user_id)
-- ou
ALTER TABLE MaTable ADD UNIQUE INDEX UNIQ_E6F03AD9A76ED395 (user_id)
La différence entre les deux est la requête utilisée pour l'ajouter[6].
Pour un groupe de champs unique (clé composite) :
ALTER TABLE MaTable ADD PRIMARY KEY (nom, prenom);
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[7].
Exemple pour travaux pratiques
[modifier | modifier le wikicode]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
CREATE DATABASE wiki1;
USE wiki1;
-- Liste des utilisateurs
CREATE TABLE IF NOT EXISTS `wiki1_user` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varbinary(255) NOT NULL DEFAULT '',
`user_real_name` varbinary(255) NOT NULL DEFAULT '',
`user_password` tinyblob NOT NULL,
`user_newpassword` tinyblob NOT NULL,
`user_newpass_time` binary(14) DEFAULT NULL,
`user_email` tinyblob NOT NULL,
`user_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_token` binary(32) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_email_authenticated` binary(14) DEFAULT NULL,
`user_email_token` binary(32) DEFAULT NULL,
`user_email_token_expires` binary(14) DEFAULT NULL,
`user_registration` binary(14) DEFAULT NULL,
`user_editcount` int(11) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `user_email_token` (`user_email_token`),
KEY `user_email` (`user_email`(50))
) ENGINE=InnoDB DEFAULT CHARSET=binary AUTO_INCREMENT=41 ;
INSERT INTO `wiki1_user` (`user_id`, `user_name`, `user_real_name`, `user_password`, `user_newpassword`, `user_newpass_time`, `user_email`, `user_touched`, `user_token`, `user_email_authenticated`, `user_email_token`, `user_email_token_expires`, `user_registration`, `user_editcount`) VALUES
(1, 'Utilisateur1', 'admin', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 1000),
(2, 'Utilisateur2', '', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 800),
(3, 'Bot1', 'admin', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 5000),
(4, 'Utilisateur3', '', '', '', NULL, '', '', '', '', '', '20130102', '20130102', 500),
(5, 'Utilisateur4', '', '', '', NULL, '', '', '', '', '', '20130102', '20130102', 200);
(6, 'Utilisateur5', '', '', '', NULL, '', '', '', '', '', '20130103', '20130103', 200);
-- Liste des pages
CREATE TABLE IF NOT EXISTS `wiki1_page` (
`page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`page_namespace` int(11) NOT NULL,
`page_title` varbinary(255) NOT NULL,
`page_restrictions` tinyblob NOT NULL,
`page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
`page_is_redirect` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_is_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_random` double unsigned NOT NULL,
`page_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`page_latest` int(10) unsigned NOT NULL,
`page_len` int(10) unsigned NOT NULL,
PRIMARY KEY (`page_id`),
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
KEY `page_random` (`page_random`),
KEY `page_len` (`page_len`),
KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
) ENGINE=InnoDB DEFAULT CHARSET=binary AUTO_INCREMENT=8;
INSERT INTO `wiki1_page` (`page_id`, `page_namespace`, `page_title`, `page_restrictions`, `page_counter`, `page_is_redirect`, `page_is_new`, `page_random`, `page_touched`, `page_latest`, `page_len`) VALUES
(1, 0, 'Accueil', '', 0, 0, 0, 0, '', 0, 0),
(2, 8, 'Sidebar', '', 0, 0, 0, 0, '', 0, 0),
(3, 0, 'MySQL', '', 0, 0, 0, 0, '', 0, 0),
(4, 0, 'PHP', '', 0, 0, 0, 0, '', 0, 0);
-- Propriétés des pages
CREATE TABLE IF NOT EXISTS `wiki1_page_props` (
`pp_page` int(11) NOT NULL,
`pp_propname` varbinary(60) NOT NULL,
`pp_value` blob NOT NULL,
UNIQUE KEY `pp_page_propname` (`pp_page`,`pp_propname`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
INSERT INTO `wiki1_page_props` (`pp_page`, `pp_propname`, `pp_value`) VALUES
(1, 'noindex', ''),
(2, 'defaultsort', ''),
(2, 'noindex', '');
-- Hyperliens dans les pages
CREATE TABLE IF NOT EXISTS `wiki1_pagelinks` (
`pl_from` int(10) unsigned NOT NULL DEFAULT '0',
`pl_namespace` int(11) NOT NULL DEFAULT '0',
`pl_title` varbinary(255) NOT NULL DEFAULT '',
UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
UNIQUE KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
INSERT INTO `wiki1_pagelinks` (`pl_from`, `pl_namespace`, `pl_title`) VALUES
(1, 0, 'Lien1'),
(3, 0, 'Lien2');
Références
[modifier | modifier le wikicode]- ↑ 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
[modifier | modifier le wikicode]La syntaxe est la suivante :
INSERT INTO TableName (Column1, Column2, Column3)
VALUES (value1, value2, value3)
Ce qui effectue les opérations suivantes : insert value1 into Column1, value2 into Column2, and value3 into Column3
.
Insérer un enregistrement (les valeurs sont insérées dans l'ordre où les colonnes apparaissent dans la base) :
INSERT INTO TableName
VALUES (value1, value2, value3)
-- Deux lignes :
INSERT INTO TableName
VALUES (value1, value2, value3), (value4, value5, value6)
INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00);
INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');
-- Copier ceux d'une autre table :
INSERT INTO table1(field1, field2)
SELECT field1, field2
FROM table2
-- Copier une ligne :
INSERT INTO World_Events SELECT * FROM National_Events
Il vaut mieux sélectionner chaque champ à copier sinon la contrainte d'unicité sur la clé primaire peut faire échouer l'insertion.
Astuces de performances :
- Pour insérer plusieurs lignes depuis un fichier, utiliser
LOAD DATA INFILE
de préférence (bulk insert). - Si un gros volume d'insertion est trop lent sur des tables indexées non vides, augmenter la valeur de
bulk_insert_buffer_size
. - Avant des insertions en masse, retirer les clés.
- Verrouiller une table (
LOCK
) accélère lesINSERT
.
UPDATE
[modifier | modifier le wikicode] UPDATE table SET field1 = newvalue1, field2 = newvalue2 WHERE criteria ORDER BY field LIMIT n
Exemples :
UPDATE owner SET ownerfirstname = 'John'
WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');
UPDATE antiques SET price = 500.00 WHERE item = 'Chair';
UPDATE order SET discount=discount * 1.05
UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID
SET tbl1.col1 = tbl1.col1 + 1
WHERE tbl2.status='Active'
UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz')
UPDATE products_categories AS pc
INNER JOIN products AS p ON pc.prod_id = p.id
SET pc.prod_sequential_id = p.sequential_id
UPDATE table_name SET col_name =
REPLACE(col_name, 'host.domain.com', 'host2.domain.com')
UPDATE posts SET deleted=True
ORDER BY date LIMIT 1
Avec ORDER BY
il est possible de classer les enregistrements avant l'insertion, voire même sur un nombre donné de lignes (avec LIMIT
).
Astuces de performances :
- La vitesse des
UPDATE
dépend du nombre d'index mis à jour. - En cas d'
UPDATE
d'une table MyISAM au format dynamique, les colonnes larges causes des lectures de mémoire superflues. Il faut régulièrement lancerOPTIMIZE 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'unSELECT
sur une table InnoDB, prépare les locks nécessaires pour ne pas que ces données changent avant unUPDATE
les utilisant[1].
La fonction replace()
est sensible à la casse même avec les collations insensibles.
REPLACE
[modifier | modifier le wikicode]REPLACE
fonctionne depuis MySQL 5.5[2], 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
[modifier | modifier le wikicode]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.
ON DUPLICATE KEY
UPDATE id=id
.DELETE et TRUNCATE
[modifier | modifier le wikicode] DELETE [QUICK] FROM `table1`
TRUNCATE [TABLE] `table1`
Quelques précisions :
- Utiliser
DELETE
sans clauseWHERE
, supprime tous les enregistrements. - Si une table contient beaucoup d'index, on peut agrandir le cache pour accélérer les
DELETE
(variablekey_buffer_size
). - Pour les tables indexées MyISAM, parfois
DELETE
est plus rapide en spécifiant le motQUICK
(DELETE QUICK FROM
...). Cela permet de réutiliser les valeurs des index effacées. TRUNCATE
efface également les lignes rapidement, en faisantDROP
etCREATE
(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 pasTRUNCATE
.- 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 commeDELETE
.
DELETE FROM `antiques`
WHERE item = 'Ottoman'
ORDER BY `id`
LIMIT 1
Il est possible de classer les lignes avant leur suppression, tout en en choisissant le nombre.
Pour supprimer des enregistrements de plusieurs tables (multi-table delete[3] ou cross table delete) :
DELETE t1, t2
FROM t1
LEFT JOIN t2
WHERE t1.id=t2.id AND t1.value > 1;
-- Synonyme :
DELETE FROM t1, t2
USING t1
LEFT JOIN t2
ON t1.id = t2.id
WHERE t1.value > 1;
Toutefois dans les version inférieures à la 4 (et étonnamment constaté comme fausse alerte par PhpMyAdmin sur des versions ultérieures), les jointures sont interdites dans les suppressions, et on doit alors utiliser le WHERE
[4] :
DELETE t1, t2
FROM table1 t1, table2 t2
WHERE t1.id=t2.id AND t1.value > 1;
Comme la clause LIMIT
ne fonctionne pas sur les suppressions multiples, il faut contourner en supprimant les enregistrements retournés par un SELECT
avec LIMIT
.
Références
[modifier | modifier le wikicode]
Requêtes
SELECT
[modifier | modifier le wikicode]La syntaxe de sélection est la suivante (chaque clause fera l'objet d'un paragraphe explicatif ensuite) :
SELECT *
FROM nom_table
WHERE condition
GROUP BY champ1, champ2
HAVING groupe condition
ORDER BY champ
LIMIT limite, taille;
Liste de champs
[modifier | modifier le wikicode]Il faut spécifier les données à récupérer avec SELECT
:
SELECT DATABASE(); -- renvoie le nom de la base courante
SELECT CURRENT_USER(); -- l'utilisateur courant
SELECT 1+1; -- 2
L'étoile permet d'obtenir tous les champs d'une table :
SELECT * FROM `wiki1_page`;
Mais il est plutôt conseillé de nommer chaque champ (projection) pour accélérer la requête.
Noms des tables
[modifier | modifier le wikicode]Pour récupérer les champs d'une table ou d'une vue, il faut la placer dans la clause FROM
:
USE wiki1;
SELECT page_id FROM `wiki1_page`; -- renvoie les valeurs du champ "page_id" de la table "wiki1_page".
SELECT `wiki1`.`wiki1_page`.`page_id`; -- idem
Autres exemples :
SELECT MAX(page_id) FROM `wiki1_page`; -- le nombre le plus élevé
SELECT page_id*2 FROM `wiki1_page`; -- le double de chaque identifiant
WHERE
[modifier | modifier le wikicode]Cette clause permet de filtrer les enregistrements. Prenons pas exemple celui ou ceux dont le champ identifiant est égal à 42 :
SELECT * FROM `wiki1_page` WHERE `page_id`=42;
Ou bien ceux qui ne sont pas nuls :
SELECT * FROM `wiki1_page` WHERE page_id IS NOT NULL;
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
[modifier | modifier le wikicode]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 :
SELECT user_registration, MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)
FROM wiki1_user
GROUP BY `user_registration`;
Idem mais classé par nom et prénom d'utilisateur :
SELECT user_registration, user_real_name, MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)
FROM wiki1_user
GROUP BY `user_registration`, `user_real_name`;
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 :
SELECT user_email,
MAX(CASE WHEN user_id = 1 THEN user_name ELSE NULL END) AS User1,
MAX(CASE WHEN user_id = 2 THEN user_name ELSE NULL END) AS User2,
MAX(CASE WHEN user_id = 3 THEN user_name ELSE NULL END) AS User3
FROM wiki1_user
GROUP BY `user_email`;
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
[modifier | modifier le wikicode]Ajouter cette clause à un GROUP BY permet d'ajouter le résultat d'une super-agrégation reprenant l'ensemble des résultats[1].
HAVING
[modifier | modifier le wikicode]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
) :
SELECT MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)
FROM wiki1_user
GROUP BY user_real_name
HAVING user_real_name = 'admin';
Par contre, cet exemple ne peut pas être optimisé car le HAVING
utilise le résultat du MAX()
calculé après le GROUP BY
:
SELECT MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)
FROM wiki1_user
GROUP BY user_real_name
HAVING MAX(user_editcount) > 500;
ORDER BY
[modifier | modifier le wikicode]Il est possible de classer les résultat, par ordre croissant ou décroissant, des nombres ou des lettres.
SELECT * FROM `wiki1_page` ORDER BY `page_id`;
Par défaut, l'ordre est ASCENDING
(croissant). Pour le décroissant il faut donc préciser DESCENDING
:
SELECT * FROM `wiki1_page` ORDER BY `page_id` ASC; -- ASC est facultatif
SELECT * FROM `wiki1_page` ORDER BY `page_id` DESC; -- ordre inversé
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 :
SELECT `page_title`, `page_id` FROM `wiki1_page` ORDER BY 1; -- nom
SELECT `page_title`, `page_id` FROM `wiki1_page` ORDER BY 2; -- id
SELECT `page_title`, `page_id` FROM `wiki1_page` ORDER BY 1 DESC;
Les expressions SQL sont autorisées :
SELECT `page_title` FROM `wiki1_page` ORDER BY REVERSE(`page_title`)
La fonction RAND()
classe de façon aléatoire :
SELECT `page_title` FROM `wiki1_page` ORDER BY RAND()
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
:
SELECT user_registration, user_real_name, MAX(user_editcount)
FROM wiki1_user
GROUP BY `user_registration` ASC, `user_real_name` DESC;
Pour éviter ce classement automatique du GROUP BY
, utiliser ORDER BY NULL
:
SELECT user_registration, user_real_name, MAX(user_editcount)
FROM wiki1_user
GROUP BY `user_registration`, `user_real_name` ORDER BY NULL;
LIMIT
[modifier | modifier le wikicode]Le nombre maximum d'enregistrements dans le résultat est facultatif, on l'indique avec le mot LIMIT
:
SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10;
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 :
SELECT * FROM `wiki1_page` ORDER BY rand() LIMIT 3;
Il est possible de définir une plage d’enregistrements, sachant que le premier est le numéro zéro :
SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10;
SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 0, 10; -- synonyme
On peut donc paginer les requêtes dont les résultats peuvent saturer le serveur :
SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 0, 10; -- première page
SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10, 10; -- seconde page
SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 20, 10; -- troisième page
La seconde commande est équivalente à celle-ci :
SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10 OFFSET 10
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 :
SELECT ... LIMIT 0
- Conseils d'optimisation
SQL_CALC_FOUND_ROWS
peut accélérer les requêtes[2][3].LIMIT
est particulièrement pratique dans desSELECT
avecORDER BY
,DISTINCT
etGROUP 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
[modifier | modifier le wikicode]Le mot DISTINCT
peut être utilisé pour supprimer les doublons des lignes du résultat :
SELECT DISTINCT * FROM `wiki1_page` -- aucun doublon
SELECT DISTINCTROW * FROM `wiki1_page` -- synonyme
SELECT ALL * FROM `wiki1_page` -- doublons (comportement par défaut)
Cela permet par exemple de récupérer la liste de toutes les valeurs différentes d'un champ :
SELECT DISTINCT `user_real_name` FROM `wiki1_page` ORDER BY `user_real_name`
On peut également en sortir les différentes combinaisons de valeurs :
SELECT DISTINCT `user_real_name`, `user_editcount` FROM `wiki1_page` ORDER BY `user_real_name`
DISTINCT
devient inutile. C'est également le cas avec GROUP BY
.
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
[modifier | modifier le wikicode]Équivalent du signe =
, qui ne nécessite pas d'être répété quand il concerne plusieurs valeurs :
SELECT page_id
FROM wiki1_page
WHERE page_namespace IN (0, 1);
-- Liste des pages qui ont des propriétés plus celles qui n'ont aucun hyperlien
SELECT page_id
FROM wiki1_page as p, wiki1_user as u WHERE p.page_id = u.user_id
UNION
SELECT page_id
FROM wiki1_page WHERE page_id NOT IN (SELECT pp_page FROM wiki1_page_props);
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)
[4].
EXISTS
[modifier | modifier le wikicode]Fonction disponible depuis MySQL 4.
-- N'affiche la première sélection que si la seconde n'est pas nulle
SELECT page_title
FROM wiki1_page
WHERE EXISTS (SELECT * FROM wiki1_page_props WHERE pp_propname = 'noindex');
ALL
[modifier | modifier le wikicode] -- Ne renvoie que les pages dont le numéro est le seul de la seconde sélection
SELECT page_title
FROM wiki1_page
WHERE page_id = ALL (SELECT pp_page FROM wiki1_page_props WHERE pp_propname = 'defaultsort');
UNION et UNION All
[modifier | modifier le wikicode]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 :
SELECT page_title FROM wiki1_page
UNION ALL
SELECT user_name FROM wiki1_user;
UNION
est équivalent à UNION DISTINCT
, ce qui le distingue de UNION ALL
qui ne filtre pas les doublons.
SELECT page_id FROM wiki1_page
UNION
SELECT page_id FROM wiki1_page;
-- égal
(SELECT page_id FROM wiki1_page)
UNION DISTINCT
(SELECT page_id FROM wiki1_page)
ORDER BY page_id;
JOIN
[modifier | modifier le wikicode]
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.
CREATE TABLE english (Tag int, Inenglish varchar(255));
CREATE TABLE hindi (Tag int, Inhindi varchar(255));
INSERT INTO english (Tag, Inenglish) VALUES (1, 'One');
INSERT INTO english (Tag, Inenglish) VALUES (2, 'Two');
INSERT INTO english (Tag, Inenglish) VALUES (3, 'Three');
INSERT INTO hindi (Tag, Inhindi) VALUES (2, 'Do');
INSERT INTO hindi (Tag, Inhindi) VALUES (3, 'Teen');
INSERT INTO hindi (Tag, Inhindi) VALUES (4, 'Char');
select * from english | select * from hindi | ||
Tag | Inenglish | Tag | Inhindi |
1 | One | 2 | Do |
2 | Two | 3 | Teen |
3 | Three | 4 | Char |
INNER JOIN
[modifier | modifier le wikicode] SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english, hindi
WHERE english.Tag = hindi.Tag
-- égal
SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english INNER JOIN hindi ON english.Tag = hindi.Tag
Tag | Inenglish | Inhindi |
2 | Two | Do |
3 | Three | Teen |
JOIN
seul est INNER JOIN
, qui est aussi synonyme de CROSS JOIN
[5] (jointure cartésienne).La jointure cartésienne décrit le cas où chaque ligne d'une table est jointe à toutes celles d'une autre.
SELECT * FROM english, hindi
-- égal
SELECT * FROM english CROSS JOIN hindi
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
[modifier | modifier le wikicode]La jointure naturelle équivaut à INNER JOIN
sur toutes les colonnes communes des deux tables.
USING
[modifier | modifier le wikicode]Le mot USING
est compatible MySQL 4, mais change avec MySQL 5. La requête suivante est équivalente à celles INNER JOIN
ci-dessus :
SELECT hindi.tag, hindi.Inhindi, english.Inenglish
FROM hindi NATURAL JOIN english
USING (Tag)
OUTER JOIN
[modifier | modifier le wikicode] SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english OUTER JOIN hindi ON english.Tag = hindi.Tag
Tag | Inenglish | Tag | Inhindi |
1 | One | ||
2 | Two | 2 | Do |
3 | Three | 3 | Teen |
4 | Char |
LEFT JOIN / LEFT OUTER JOIN
[modifier | modifier le wikicode] SELECT field1, field2 FROM table1 LEFT JOIN table2 ON field1=field2
SELECT e.Inenglish as English, e.Tag, h.Inhindi as Hindi
FROM english AS e
LEFT JOIN hindi AS h ON e.Tag=h.Tag
WHERE h.Inhindi IS NULL
English tag Hindi One 1 NULL
inner join
, s'il y a plusieurs lignes non NULL à droite, on les retrouve toutes en résultat.RIGHT OUTER JOIN
[modifier | modifier le wikicode] SELECT e.Inenglish AS English, h.tag, h.Inhindi AS Hindi
FROM english AS e RIGHT JOIN hindi AS h
ON e.Tag=h.Tag
WHERE e.Inenglish IS NULL
English tag Hindi NULL 4 Char
- S'assurer que le type des clés de jointes est le même dans les deux tables.
- Les mots clés
LEFT
etRIGHT
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
(pasOUTER
).
FULL OUTER JOIN
[modifier | modifier le wikicode]MySQL et MariaDB n'ont pas de jointure FULL OUTER JOIN
. Voici comment l'émuler :
(SELECT a.*, b*
FROM tab1 a LEFT JOIN tab2 b
ON a.id = b.id)
UNION
(SELECT a.*, b*
FROM tab1 a RIGHT JOIN tab2 b
ON a.id = b.id)
Jointures multiples
[modifier | modifier le wikicode]Il est possible de joindre plus de deux tables :
SELECT ... FROM a JOIN (b JOIN c on b.id=c.id) ON a.id=b.id
Exemple :
mysql> SELECT group_type.type_id, group_type.nom, COUNT(people_job.job_id) AS count
FROM group_type
JOIN (groups JOIN people_job ON groups.group_id = people_job.group_id)
ON group_type.type_id = groups.type
GROUP BY type_id ORDER BY type_id
+---------+--------------------------------------+-------+
| type_id | nom | count |
+---------+--------------------------------------+-------+
| 1 | Official GNU software | 148 |
| 2 | non-GNU software and documentation | 268 |
| 3 | www.gnu.org portion | 4 |
| 6 | www.gnu.org translation team | 5 |
+---------+--------------------------------------+-------+
4 rows in set (0.02 sec)
Sous requêtes
[modifier | modifier le wikicode]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
ouHAVING
. - 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 :
SELECT ville FROM Offices WHERE Target > ???
??? est la somme des quotas des vendeurs.
SELECT SUM(Quota)
FROM SalesReps
WHERE RepOffice = OfficeNbr
En combinant ces deux requêtes, les points d'interrogations disparaissent :
SELECT ville FROM Offices
WHERE Target > (SELECT SUM(Quota) FROM SalesReps
WHERE RepOffice = OfficeNbr)
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 :
SELECT DISTINCT CustNbr
FROM Customers, Orders
WHERE CustNbr = Cust AND (CreditLimit > 50000 OR Amt > 50000);
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 = ...
WITH
permet de se référer aux sous-requêtes récurrentes, récursives ou pas[6].Exemple :
WITH
sub_request1 AS (SELECT field1, field2 FROM table1),
sub_request2 AS (SELECT field2, field3 FROM table2)
SELECT field1, field3
FROM sub_request1 INNER JOIN sub_request2
WHERE sub_request1.field1 = sub_request2.field1;
References
[modifier | modifier le wikicode]- ↑ 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/
NULL
Description
[modifier | modifier le wikicode]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.
INSERT into Singer
(F_Name, L_Name, Birth_place, Language)
values
("", "Homer", NULL, "Greek"),
("", "Sting", NULL, "English"),
("Jonny", "Five", NULL, "Binary");
NULL
ne doit pas être entouré d'apostrophes ou de guillemets, ou bien il désignera une chaine de caractères contenant son nom.
NULL
n'apparait pas dans les colonnes Varchar sous Windows XP mais sous Fedora oui.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 :
SELECT * from Singer WHERE Birth_place IS NULL;
SELECT * from Singer WHERE Birth_place IS NOT NULL;
SELECT * from Singer WHERE isNull(Birth_place)
Les enregistrements X à NULL ne sont pas renvoyés par un WHERE X != 'Y'
COUNT
ne tient pas compte des NULL
:
select count(Birth_place) from Singer;
0
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
:
SELECT (NULL=NULL) OR (NULL<>NULL) OR (NOT NULL) OR (1<NULL) OR (1>NULL) OR (1 + NULL) OR (1 LIKE NULL)
Deux valeurs inconnues ne sont donc pas égales (NULL
=NULL
renvoie NULL
).
Gérer NULL
[modifier | modifier le wikicode]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 :
SELECT COALESCE(colname,0) from table where COALESCE(colname,0) > 1;
Dans un champ date, les traiter comme celle actuelle :
ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))
EXP(SUM(LOG(COALESCE(''*the field you want to multiply*'',1)))
La fonction coalesce()
prévient des problèmes de calcul logarithmique d'une valeur nulle, et peut être optionnelle selon les circonstances.
SELECT t4.gene_name, COALESCE(g2d.score,0),
COALESCE(dgp.score,0), COALESCE(pocus.score,0)
FROM t4
LEFT JOIN g2d ON t4.gene_name=g2d.gene_name
LEFT JOIN dgp ON t4.gene_name=dgp.gene_name
LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;
IFNULL()
dans un SELECT
fait de NULL
n'importe quelle valeur désirée.
IFNULL(expr1,expr2)
Si expr1 n'est pas nulle, IFNULL()
renvoie expr1, sinon expr2.
IFNULL()
renvoie une chaine ou un nombre, selon le contexte :
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'
Attention aux résultats peu prévisibles, par exemple la requête suivante efface toutes les entrées :
DELETE FROM ma_table1 WHERE field > NULL -- fonctionne aussi avec une fonction renvoyant NULL
Pour obtenir les NULL
en dernier lors d'un ORDER BY
:
SELECT * FROM ma_table1 ORDER BY ISNULL(field), field [ ASC | DESC ]
Enfin, pour déterminer les champs d'une table qui ne peuvent pas être nuls :
SELECT *
FROM `information_schema`.`COLUMNS`
WHERE IS_NULLABLE = 'NO' AND TABLE_NAME = 'ma_table1'
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
[modifier | modifier le wikicode]L'opérateur = peut assigner une valeur à une colonne :
UPDATE `table1` SET `champ1`=0
Par contre pour assigner une valeur à une variable, l'opérateur est :=, car = est déjà utilisé pour la comparaison.
SELECT @variable1 := 1
SELECT INTO
peut aussi remplir les variables.
SELECT 1 INTO @variable1
Opérateurs de comparaison
[modifier | modifier le wikicode]Égalité
[modifier | modifier le wikicode]Pour vérifier si deux valeurs sont égales, utiliser = :
SELECT True = True -- 1
SELECT True = False -- 0
Pour vérifier si deux valeurs sont différentes, c'est <> ou != :
SELECT True <> False -- 1
SELECT True != True -- 0
Comparaison IS NULL
[modifier | modifier le wikicode]Pour savoir si une valeur est nulle, utiliser IS :
SELECT (NULL = NULL) -- NULL
SELECT (NULL IS NULL) -- 1
SELECT (1 IS NULL) -- 0
SELECT (True IS True) -- erreur
Pour savoir si une valeur n'est pas nulle :
SELECT (True IS NOT NULL) -- 1
Il existe par ailleurs l'opérateur vaisseau spatial : <=>, qui considère NULL comme une valeur normale :
SELECT NULL <=> NULL -- 1
SELECT True <=> True -- 1
SELECT col1 <=> col2 FROM table1
Comparaison IS booléen
[modifier | modifier le wikicode]IS et IS NOT fonctionnent aussi avec TRUE, FALSE et UNKNOWN (qui est purement un synonyme de NULL).
SELECT 1 IS TRUE -- 1
SELECT 1 IS NOT TRUE -- 0
SELECT 1 IS FALSE -- 0
SELECT (NULL IS NOT FALSE) -- 1 : unknown n'est pas false
SELECT (NULL IS UNKOWN) -- 1
SELECT (NULL IS NOT UNKNOWN) -- 0
Plus grand et plus petit que
[modifier | modifier le wikicode]Avec des nombres :
SELECT 100 > 0 -- 1
SELECT 4 > 5 -- 0
SELECT 1 < 2 -- 1
SELECT 2 < 2 -- 0
Avec du texte dans l'ordre alphabétique :
SELECT 'a' < 'b' -- 1
SELECT `a` >= `b` FROM `table1`
SELECT NOT (`a` < `b`) FROM `table1`
SELECT `a` <= `b` FROM `table1`
SELECT * FROM `table1` WHERE `a` >= `b`
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 :
SELECT _latin1'été', _utf8'été', _cp850'été', (_latin1'été' = _utf8'été'), (_latin1'été' LIKE _utf8'été')
-- Résultat :
été été ├®t├® 0 0
BETWEEN
[modifier | modifier le wikicode]l’opérateur BETWEEN ... AND ... permet de vérifier si une valeur appartient à une plage (bornes incluses) :
SELECT 2 BETWEEN 10 AND 100 -- 0
SELECT 10 BETWEEN 10 AND 100 -- 1
SELECT 20 BETWEEN 10 AND 100 -- 1
SELECT 8 NOT BETWEEN 5 AND 10 -- 0
IN
[modifier | modifier le wikicode]IN permet de s'assurer si une valeur est dans une liste :
SELECT 5 IN (5, 6, 7) -- 1
SELECT 1 IN (5, 6, 7) -- 0
SELECT 1 NOT IN (1, 2, 3) -- 0
Attention : si la liste contient des nombres et des chaines, il faut tout mettre entre apostrophe pour obtenir le résultat escompté.
SELECT 4 IN ('a', 'z', '5')
Il n'y a aucune limite théorique au nombre de valeurs de la liste.
Opérateurs logiques
[modifier | modifier le wikicode]Booléens logiques
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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.
SELECT NOT 1 -- 0
SELECT NOT FALSE -- 1
SELECT NOT NULL -- NULL
SELECT NOT UNKNOWN -- NULL
! est synonyme de NOT.
AND
[modifier | modifier le wikicode]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.
SELECT 1 AND 1 -- 1
SELECT 1 AND '' -- 0
SELECT '' AND NULL -- NULL
&& est synonyme de AND.
OR
[modifier | modifier le wikicode]OR renvoie TRUE si au moins une des opérandes est TRUE, sinon FALSE ; si les deux opérandes sont nulles, il renvoie NULL.
SELECT TRUE OR FALSE -- 1
SELECT 1 OR 1 -- 1
SELECT FALSE OR FALSE -- 0
SELECT NULL OR TRUE -- NULL
|| est un synonyme de OR.
XOR
[modifier | modifier le wikicode]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.
SELECT 1 XOR 0 -- 1
SELECT FALSE XOR TRUE -- 1
SELECT 1 XOR TRUE -- 0
SELECT 0 XOR FALSE -- 0
SELECT NULL XOR 1 -- NULL
Opérateurs arithmétiques
[modifier | modifier le wikicode]Addition
[modifier | modifier le wikicode] SELECT +1 -- 1
SELECT 1 + 1 -- 2
Soustraction
[modifier | modifier le wikicode] SELECT -1 -- -1
SELECT -+1 -- -1
SELECT --1 -- 1
SELECT True - 1 -- 0
Multiplication
[modifier | modifier le wikicode] SELECT 1 * 1 -- 1
Divisions
[modifier | modifier le wikicode]Renvoie un nombre de type FLOAT
:
SELECT 10 / 2 -- 5,0000
SELECT 1 / 1 -- 1,0000
SELECT 1 / 0 -- NULL
Pour retourner la valeur entière du résultat d'une division sous forme de type INTEGER
, utiliser DIV
:
SELECT 10 DIV 3 -- 3
Le reste de la division (modulo) se trouve avec '%' ou MOD
:
SELECT 10 MOD 3 -- 1
Utiliser + pour convertir des données
[modifier | modifier le wikicode]Pour convertir un INTEGER
en FLOAT
:
SELECT 1 + 0.0 -- 1.0
SELECT 1 + 0.000 -- 1.000
SELECT TRUE + 0.000 -- 1.000
Il est impossible de convertir une valeur FLOAT
en ajoutant 0.0, mais on peut forcer le type en INTEGER
:
SELECT '1' + 0 -- 1
SELECT '1' + FALSE -- 1
SELECT <nowiki>''</nowiki> + <nowiki>''</nowiki> -- 0
ROUND()
[modifier | modifier le wikicode]Pour arrondit en définissant le nombre de chiffres après la virgule. Ex :
SELECT ROUND(10 / 3, 2) # 3.33
Opérateurs de texte
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]L'opérateur LIKE
si la chaîne recherchée est inclue dans une colonne :
SELECT * FROM articles WHERE titre LIKE 'hello world'
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 enBINARY
; - l'expression contient une clause
BINARY
:
SELECT * 'test' LIKE BINARY 'TEST' -- 0
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" :
SELECT * FROM articles WHERE titre LIKE 'hello%'
- Trouver tous les titres finissant par "world" :
SELECT * FROM articles WHERE titre LIKE '%world'
- Trouver tous les titres contenant la chaine "gnu" :
SELECT * FROM articles WHERE titre LIKE '%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 :
SELECT * FROM articles WHERE titre LIKE '\_%' SELECT * FROM articles WHERE titre LIKE '\%%'
/
peut-être une alternative à \
si on le précise :
SELECT * FROM articles WHERE titre LIKE '/_%' ESCAPE '/'
Quand on utilise l'opérateur =
, les espaces des chaines sont ignorés, mais avec LIKE
ils sont reconnus :
SELECT 'word' = 'word '; -- 1 SELECT 'word' LIKE 'word '; -- 0
De même, contrairement à "=", "LIKE" compare uniquement les caractères, même si leurs règles d'interclassement les regroupent[2] :
SELECT 'ä' = 'ae' COLLATE latin1_german2_ci; -- 1 SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci; -- 0
LIKE
fonctionne aussi avec les nombres :
SELECT 123 LIKE '%2%' -- 1
Pour tester si un pattern ne fonctionne pas alors qu'il devrait, utiliser NOT LIKE
:
SELECT 'a' NOT LIKE 'b' -- 1
SOUNDS LIKE
[modifier | modifier le wikicode]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 :
SELECT `word1` SOUNDS LIKE `word2` FROM `wordList` -- forme courte SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList` -- forme longue
SOUNDS LIKE
est une extension apparue depuis MySQL 4.1.
Opérateur bit à bit
[modifier | modifier le wikicode]Il existe des opérateurs pour les opérations bit à bit.
-- Non logique bit à bit :
SELECT ~0 -- 18446744073709551615
SELECT ~1 -- 18446744073709551614
-- Et logique bit à bit :
SELECT 1 & 1 -- 1
SELECT 1 & 3 -- 1
SELECT 2 & 3 -- 2
-- Ou logique bit à bit :
SELECT 1 | 0 -- 1
SELECT 3 | 0 -- 3
SELECT 4 | 2 -- 6
-- Ou exclusif bit à bit :
SELECT 1 ^ 0 -- 1
SELECT 1 ^ 1 -- 0
SELECT 3 ^ 1 -- 2
-- Décalage de bit à gauche :
SELECT 1 << 2 -- 4
-- Décalage de bit à droite :
SELECT 1 >> 2 -- 0
Conditions
[modifier | modifier le wikicode]IF
[modifier | modifier le wikicode]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[3] : IF(condition, siVraie, siFausse);
.
Exemple : SELECT IF(-1 < 0, 0, 1);
renvoie 0.
Exemple avec plusieurs conditions (switch)[4][5] :
IF n > m THEN SET s = '>';
ELSEIF n = m THEN SET s = '=';
ELSE SET s = '<';
END IF;
CASE
[modifier | modifier le wikicode]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[6] :
CASE v WHEN 2 THEN SELECT v; WHEN 3 THEN SELECT 0; ELSE BEGIN END; END CASE;
Dans une seule requête :
SELECT CASE v WHEN 1 THEN 'a' WHEN 2 THEN 'b' WHEN 3 THEN 'c' WHEN 4 THEN 'd' ELSE 0 END as value
Précédence
[modifier | modifier le wikicode]Précédence des opérateurs
[modifier | modifier le wikicode]Du plus au moins prioritaire :
INTERVAL BINARY, COLLATE ! -, ~ ^ *, /, DIV, %, MOD -, + <<, >> & | =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN BETWEEN, CASE, WHEN, THEN, ELSE NOT &&, AND XOR ||, OR :=
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
[modifier | modifier le wikicode]Tout comme en mathématiques, les parenthèses permettent d'évaluer des sous-expressions avant d'autres :
SELECT 1 + 1 * 5 -- = 6
SELECT (1 + 1) * 5 -- = 10
Cela peut aussi se faire pour rendre les requêtes plus lisibles aux humains :
SELECT 1 + (2 * 5)
Références
[modifier | modifier le wikicode]- ↑ 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
[modifier | modifier le wikicode]À l'instar des mots réservés SQL, les noms des fonctions ne sont pas sensibles à la casse :
SELECT database() -- ok
SELECT DataBase() -- ok
SELECT DATABASE() -- ok
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 :
SELECT DATABASE () -- déconseillé
SELECT DATABASE() -- recommandé
Toutefois, cette restriction ne s'applique qu'aux fonctions natives de MySQL (pas aux procédures stockées).
Fonctions générales
[modifier | modifier le wikicode]Fonctions qui dépendent du type.
BENCHMARK(nombre, expression)
[modifier | modifier le wikicode]Exécute l'expression n fois et retourne toujours zéro[1], le chiffre pertinent est donc le temps pris par cette opération de simulation. Utile pour trouver les goulots d'étranglement des expressions SQL :
SELECT BENCHMARK(10000, 'Bonjour'); -- Traitement en 0.0010 sec
CAST(valeur AS type)
[modifier | modifier le wikicode]Renvoie la valeur convertie en chaine de caractères, comme les apostrophes.
SELECT CAST(20130101 AS date); -- 2013-01-01
Exemple avec encodage[2] :
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
CHARSET(chaine)
[modifier | modifier le wikicode]Renvoie le type de caractères de la chaine :
SELECT CHARSET(20130101); -- binary
SHOW CHARACTER SET; -- montre tous les CHARACTER SET installés
COALESCE(valeur, ...)
[modifier | modifier le wikicode]Renvoie le premier paramètre non nul. S'ils sont tous nuls, renvoie NULL
.
SELECT COALESCE(null, 'Bonjour', null); -- bonjour
COERCIBILITY(chaine)
[modifier | modifier le wikicode]Renvoie la coercibility d'une chaine (entre 0 et 5) :
SELECT COERCIBILITY('bonjour'); -- 4
Coercibility[3] | 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)
[modifier | modifier le wikicode]Renvoie la collation d'une chaine :
SELECT COLLATION('bonjour'); -- utf8_general_ci
Pour obtenir celle par défaut d'une base :
SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'maBase1'
CONCAT()
[modifier | modifier le wikicode]Concaténation sans séparateur :
SELECT CONCAT('Hello', 'World');
# HelloWorld
CONCAT_WS()
[modifier | modifier le wikicode]Concaténation avec séparateur de son choix :
SELECT CONCAT_WS(', ', 'Hello', 'World');
# Hello, World
CONNECTION_ID()
[modifier | modifier le wikicode]Renvoie l'identifiant du thread courant :
SELECT CONNECTION_ID(); -- 31
CONVERT(valeur, type)
[modifier | modifier le wikicode]Tout comme CAST()
, retourne la valeur convertie dans le type mentionné :
SELECT CONVERT (20130101, date); -- 2013-01-01
CONVERT(chaine USING charset)
[modifier | modifier le wikicode]Convertit la chaine string passée dans le CHARACTER SET spécifié :
SELECT CONVERT ('Voici une écriture' USING utf8); -- Voici une écriture
SELECT CONVERT ('Voici une écriture' USING ASCII); -- Voici une ?criture
CURRENT_USER()
[modifier | modifier le wikicode]Retourne les noms de l'utilisateur et de l'hôte courants :
SELECT CURRENT_USER(); -- root@localhost
DATABASE()
[modifier | modifier le wikicode]Retourne le nom de la base de données courante :
SELECT DATABASE(); -- wiki1
FOUND_ROWS()
[modifier | modifier le wikicode]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 :
SELECT FOUND_ROWS() AS n; -- 0
SELECT SQL_CALC_FOUND_ROWS * FROM wiki1_page ORDER BY page_id LIMIT 10 OFFSET 2; -- deux lignes
SELECT FOUND_ROWS() AS n; -- 1
GREATEST(valeur1, valeur2, ...)
[modifier | modifier le wikicode]Renvoie la plus grande valeur des paramètres :
SELECT GREATEST(1, 2, 21, 3); -- 21
INTERVAL(valeur1, valeur2, valeur3, ...)
[modifier | modifier le wikicode]Renvoie l'emplacement du premier argument supérieur au premier, en partant du zéro dans la liste des entiers en paramètres :
SELECT INTERVAL(10, 20, 9, 8, 7); -- 0
SELECT INTERVAL(10, 9, 20, 8, 7); -- 1
SELECT INTERVAL(10, 9, 8, 20, 7); -- 2
SELECT INTERVAL(10, 9, 8, 7, 20); -- 3
IF(valeur1, valeur2, valeur3)
[modifier | modifier le wikicode]If valeur1 est vraie, renvoie valeur2, sinon (fausse ou nulle) renvoie valeur3.
select if(1=2, 'irréel', 'réel'); -- réel
IFNULL(valeur1, valeur2)
[modifier | modifier le wikicode]Si valeur1 est nulle, renvoie valeur2, sinon valeur1.
SELECT IFNULL('variable1', 'défaut'); -- variable1
ISNULL(valeur)
[modifier | modifier le wikicode]Si la valeur passée est nulle, renvoie 1, sinon 0.
SELECT ISNULL('variable1'); -- 0
NULLIF(valeur1, valeur2)
[modifier | modifier le wikicode]Renvoie NULL
si valeur1 = valeur2, sinon valeur1.
SELECT NULLIF(10, 20); -- 10
LAST_INSERT_ID()
[modifier | modifier le wikicode]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, ...)
[modifier | modifier le wikicode]Renvoie la plus petite valeur dans la liste des paramètres passés :
SELECT LEAST(1, 2, 21, 3, -1); -- -1
LENGTH(chaine)
[modifier | modifier le wikicode]Affiche la taille d'une chaine en octets.
Pour avoir le nombre de caractères, utiliser CHAR_LENGTH
.
REPLACE(chaine, 'sous-chaine à remplacer', 'par')
[modifier | modifier le wikicode]SELECT REPLACE('helloworld', 'o', ''); -- hellwrld
SUBSTR(chaine, début, taille)
[modifier | modifier le wikicode]Découpe une chaine de caractère :
SELECT SUBSTR('Hello World!', 7, 5); -- World
Son alias "substring()" fonctionne aussi.
SUBSTRING_INDEX
[modifier | modifier le wikicode]Découpe une chaine selon un séparateur. Le troisième paramètre désigne la énième occurrence de ce dernier :
SELECT SUBSTRING_INDEX('Hello World!', ' ', 1);
# Hello
SELECT SUBSTRING_INDEX('Hello World!', ' ', 2);
# Hello World!
Fonctions JSON
[modifier | modifier le wikicode]MySQL (et MariaDB[4]) offrent plusieurs fonctions de manipulation de champ varchar contenant du JSON.
JSON_VALID
[modifier | modifier le wikicode]Renvoie vrai si le champ est un JSON valide, faux sinon.
JSON_UNQUOTE
[modifier | modifier le wikicode]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 :
SELECT JSON_UNQUOTE('"1"');
-- 1
JSON_KEYS
[modifier | modifier le wikicode]Renvoie toutes les clés du JSON en paramètre.
JSON_EXTRACT
[modifier | modifier le wikicode]Extrait un morceau du JSON par sa clé. Ex :
SET @json = '{"1": "2", "3": "4"}';
SELECT JSON_EXTRACT(@json, '$.1');
-- "2"
Cette fonction est combinable avec JSON_UNQUOTE pour les recherches dans des données JSON[5].. Ex :
SELECT JSON_UNQUOTE(JSON_EXTRACT(varchar_avec_json,'$.ma_clé')) AS ma_clé FROM users;
JSON_SEARCH
[modifier | modifier le wikicode]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 :
SET @json = '{"1": "2", "3": "4"}';
SELECT JSON_SEARCH(@json, 'one', '4');
-- "$.3"
JSON_VALUE
[modifier | modifier le wikicode]Retourne la valeur à partir de la clé en paramètre.
JSON_CONTAINS
[modifier | modifier le wikicode]Renvoie 1 si la JSON contient la valeur en paramètre, ou 0 sinon.
JSON_REMOVE
[modifier | modifier le wikicode]Retire la paire clé/valeur de la clé passée en paramètre.
Date et heure
[modifier | modifier le wikicode]Il existe des dizaines de fonctions liées aux dates[6].
Pour trouver la date de l'an dernier :
SELECT CURDATE() - INTERVAL 1 YEAR
Sélectionner toutes les pages du wiki non lues depuis plus un an :
SELECT * FROM wiki1_page
WHERE page_touched <= (CURDATE() - INTERVAL 1 YEAR);
Autres exemples de sélections :
SELECT IF(DAYOFMONTH(CURDATE()) <= 15,
DATE_FORMAT(CURDATE(), '%Y-%m-15'),
DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15
FROM table;
SELECT YEAR('2002-05-10'), MONTH('2002-05-10'), DAYOFMONTH('2002-05-10')
SELECT PurchaseDate FROM table WHERE YEAR(PurchaseDate) <= YEAR(CURDATE())
SELECT columns FROM table
WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00'
SELECT * FROM t1
WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'
SELECT Start_time, End_time FROM Table
WHERE Start_time >= NOW() - INTERVAL 4 HOUR
SELECT NOW() + INTERVAL 60 SECOND
SELECT UNIX_TIMESTAMP('2007-05-01'); -- 1177970400
SELECT FROM_UNIXTIME(1177970400); -- 2007-05-01 00:00:00
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()
[modifier | modifier le wikicode]Pour additionner deux dates. Par exemple pour calculer le jour d'une livraison prenant 48 h :
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY)
Pour la date d'hier :
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
Les unités à additionner ou soustraire les plus courantes sont[7] :
SECOND MINUTE HOUR DAY WEEK MONTH YEAR
DATEDIFF()
[modifier | modifier le wikicode]Pour soustraire une date à une autre. Par exemple pour calculer un âge :
SELECT DATEDIFF(NOW(), birthday_date) / 365 FROM user WHERE ISNULL(birthday_date) = 0 AND birthday_date != '0000-00-00'
STR_TO_DATE()
[modifier | modifier le wikicode]Un BETWEEN ou ">" de champ date avec une chaine de caractères ne marche pas. Il faut donc convertir la chaine en date :
SELECT * FROM my_table WHERE start_date > STR_TO_DATE('2024-08-10', '%Y-%m-%d')
Fonctions d'agrégation
[modifier | modifier le wikicode]COUNT(champ)
[modifier | modifier le wikicode]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 :
SELECT COUNT(*) FROM `wiki1_page`;
Si le mot DISTINCT
est employé, cela ignore les doublons :
SELECT COUNT(DISTINCT id) FROM `wiki1_page`;
Si le nom d'un champ est précisé, cela renvoie le nombre de valeurs non nulles :
SELECT COUNT(`user_real_name`) FROM `wiki1_user`;
SELECT COUNT(DISTINCT `user_real_name`) FROM `wiki1_user`;
Cela fonctionne aussi pour des expressions, des combinaisons de champs :
SELECT COUNT(`user_name` + `user_real_name`) FROM `wiki1_user`;
Pour afficher le décompte de plusieurs tables non jointes :
SELECT
(SELECT COUNT(*) FROM maTable1) as t1,
(SELECT COUNT(*) FROM maTable2) as t2
MAX(champ)
[modifier | modifier le wikicode]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 :
SELECT MAX(`user_editcount`) FROM `wiki1_user`;
SELECT MAX(LENGTH(CONCAT(`user_name`, ' ', `user_real_name`))) FROM `wiki1_user`;
Alternatives
[modifier | modifier le wikicode]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)
[modifier | modifier le wikicode]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 :
SELECT MIN(`user_editcount`) FROM `wiki1_user`;
SELECT MIN(LENGTH(CONCAT(`user_name`, ' ', `user_real_name`))) FROM `wiki1_user`;
AVG(champ)
[modifier | modifier le wikicode]AVG()
renvoie la valeur moyenne d'une expression, ou NULL
s'il n'y en a pas :
- Moyenne des valeurs d'un champ entier :
SELECT AVG(`user_editcount`) FROM `wiki1_user`;
- Moyenne des valeurs d'un champ date :
SELECT from_unixtime(avg(unix_timestamp(`user_registration`))) FROM `wiki1_user`;
- Moyenne d'un total obtenu par COUNT :
SELECT AVG(p.nb) as moyenne FROM ( SELECT count(*) as nb FROM wiki1_user where user_editcount > 1000 group by user_country ) as p;
SUM(champ)
[modifier | modifier le wikicode]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.
SELECT SUM( DISTINCT user_editcount )
FROM wiki1_user
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)
[modifier | modifier le wikicode]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.
CREATE TEMPORARY TABLE product (
id INTEGER, product_type VARCHAR(10), product_name VARCHAR(50)
);
INSERT INTO product VALUES
(1, 'mp3', 'iPod'),
(2, 'mp3', 'Zune'),
(3, 'mp3', 'ZEN'),
(4, 'notebook', 'Acer Eee PC'),
(4, 'notebook', 'Everex CloudBook');
SELECT * FROM product;
SELECT product_type, group_concat(product_name), group_concat(' ', product_name)
FROM product
GROUP BY product_type;
/*
# product_type, group_concat(product_name), group_concat(' ', product_name)
'mp3', 'iPod,Zune,ZEN', ' iPod, Zune, ZEN'
'notebook', 'Acer Eee PC,Everex CloudBook', ' Acer Eee PC, Everex CloudBook'
*/
Fonctions d'agrégation de bit
[modifier | modifier le wikicode]Syntaxe générale :
FUNCTION_NAME(expression)
Ces fonctions bit à bit calculent expression pour chaque ligne du résultat et entre les expressions. La précision est de 64 bit.
AND
[modifier | modifier le wikicode] SELECT BIT_AND(ip) FROM log
OR
[modifier | modifier le wikicode] SELECT BIT_OR(ip) FROM log
(retourne 0 s'il n'y a aucun résultat)
XOR
[modifier | modifier le wikicode] SELECT BIT_XOR(ip) FROM log
(retourne 0 s'il n'y a aucun résultat)
Références
[modifier | modifier le wikicode]- ↑ 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
[modifier | modifier le wikicode]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 :
delimiter |
CREATE ...
delimiter ;
Procédures stockées
[modifier | modifier le wikicode]Il en existe deux types :
FUNCTION
si elles retournent un résultat. Elles sont appelées avecSELECT
.PROCEDURE
si elles ne retournent rien après leur traitement. Elles sont exécutées avecCALL
.
Lister toutes les procédures stockées et fonctions
[modifier | modifier le wikicode]SELECT db, name FROM mysql.proc;
Gestion des PROCEDURE et FUNCTION
[modifier | modifier le wikicode]CREATE FUNCTION
[modifier | modifier le wikicode]Création de fonction. Exemple sans paramètre :
CREATE FUNCTION `HelloWorld`() RETURNS VARCHAR(20) RETURN 'Hello World!';
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
:
DELIMITER $$
CREATE FUNCTION `HelloWorld`()
RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello World!';
END $$
DELIMITER ;
SELECT HelloWorld();
# Affiche "Hello World!'"
Avec paramètre :
DELIMITER $$
CREATE FUNCTION `HelloWorld2`(_nom VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
RETURN CONCAT('Hello World ', _nom, '!');
END $$
DELIMITER ;
SELECT HelloWorld2('monsieur');
# Affiche "Hello World monsieur!"
CREATE PROCEDURE
[modifier | modifier le wikicode]Création de procédure stockée :
CREATE PROCEDURE `Module1` ( ) OPTIMIZE TABLE wiki1_page;
CALL Module1();
Pour affiner les permissions, la création ci-dessous est liée à un compte :
CREATE DEFINER = `root`@`localhost` PROCEDURE `Module2` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER OPTIMIZE TABLE wiki1_page;
Toutefois, s'il est supprimé elle ne fonctionne plus. Pour éviter cela, on peut la rattacher aux comptes qui vont l'exécuter :
CREATE DEFINER = `root`@`localhost` PROCEDURE `Module3`
SQL SECURITY INVOKER
BEGIN
OPTIMIZE TABLE wiki1_page;
END;
CALL
[modifier | modifier le wikicode]Invocation :
CALL `Module1` ();
DROP PROCEDURE
[modifier | modifier le wikicode]Suppression :
DROP PROCEDURE `Module1` ;
Modification
[modifier | modifier le wikicode]On est obligé de supprimer et de recréer le module :
DROP PROCEDURE `Module1` ;
CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN
OPTIMIZE TABLE wiki1_page;
OPTIMIZE TABLE wiki1_user;
END
Métadonnées des PROCEDURE et FUNCTION
[modifier | modifier le wikicode]SHOW FUNCTION / PROCEDURE STATUS
[modifier | modifier le wikicode] SHOW PROCEDURE STATUS;
SHOW CREATE FUNCTION / PROCEDURE
[modifier | modifier le wikicode]Pour obtenir la requête SQL qui avait créé la procédure :
SHOW CREATE PROCEDURE Module1;
INFORMATION_SCHEMA.ROUTINES
[modifier | modifier le wikicode]La base virtuelle INFORMATION_SCHEMA a une table `ROUTINES` avec les informations des procédures et fonctions.
INFORMATION_SCHEMA.PARAMETERS
[modifier | modifier le wikicode]Cette table contient toutes les valeurs des fonctions stockées.
Déclencheurs
[modifier | modifier le wikicode]Gestion des TRIGGER
[modifier | modifier le wikicode]Disponibles depuis MySQL 5.0.2, ils fonctionnent sur les tables persistantes, mais pas les temporaires.
CREATE TRIGGER
[modifier | modifier le wikicode] CREATE TRIGGER `effacer_ancien` AFTER INSERT ON `wiki1_page`
FOR EACH ROW
DELETE FROM `wiki1_page` ORDER BY `page_id` ASC LIMIT 1
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
, dontLOAD DATA
etREPLACE
;DELETE
, incluantREPLACE
, mais pasTRUNCATE
;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 :
... ON `base1`.`table1` ...
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 :
CREATE TRIGGER `use_values` AFTER INSERT ON `example_tab`
FOR EACH ROW BEGIN
UPDATE `changelog` SET `old_value`=OLD.`field1`, `new_value`=NEW.`field1` WHERE `backup_tab`.`id`=`example_tab`.`id`
END
DROP TRIGGER
[modifier | modifier le wikicode]Pour supprimer un déclencheur :
DROP TRIGGER `trigger1`
-- ou
DROP TRIGGER `base1`.`trigger1`
-- ou
DROP TRIGGER IF EXISTS `trigger1`
Pour modifier un trigger, il faut le supprimer puis le recréer.
Métadonnées des TRIGGER
[modifier | modifier le wikicode]SHOW CREATE TRIGGER
[modifier | modifier le wikicode]Disponible depuis MySQL 5.1. Affiche la commande pour recréer un déclencheur nommé :
SHOW CREATE TRIGGER effacer_ancien;
- 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
[modifier | modifier le wikicode]Pour obtenir la liste des triggers de la base courante :
SHOW TRIGGERS;
Pour obtenir la liste des triggers d'une autre base :
SHOW TRIGGERS IN `base2`
-- ou
SHOW TRIGGERS FROM `base2`
D'autres filtres sont possibles :
SHOW TRIGGERS WHERE table='wiki1_page'
LIKE
et WHERE
ensemble.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
ouAFTER
- 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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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 :
mysqld --event-scheduler=DISABLED
Ou bien en ajoutant une ligne dans le fichier de configuration (my.cnf) :
event_scheduler=DISABLED
Ou encore en cours d'utilisation :
SELECT event_scheduler -- valeurs : ON / OFF / DISABLED
SET GLOBAL event_scheduler = ON
SET GLOBAL event_scheduler = OFF
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
[modifier | modifier le wikicode]Les commandes sont CREATE EVENT, ALTER EVENT, DROP EVENT
.
CREATE EVENT
[modifier | modifier le wikicode]Pour un évènement à exécuter le lendemain :
CREATE EVENT `évènement1`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
INSERT INTO `wiki1`.`news` (`title`, `text`) VALUES ('Example!', 'This is not a real news')
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
:
CREATE EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
DO
OPTIMIZE TABLE `wiki1`.`news`
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 :
CREATE EVENT `évènement2`
ON SCHEDULE EVERY INTERVAL 1 DAY
DO
OPTIMIZE TABLE `wiki1`.`news`
STARTS CURRENT_TIMESTAMP + 1 MONTH
ENDS CURRENT_TIMESTAMP + 3 MONTH
Les unités autorisées sont :
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND
La clause DO
spécifie la commande à exécuter.
Si la tâche est composée par plus d'une commande, utiliser BEGIN ... END
:
delimiter |
CREATE EVENT `évènement3`
ON SCHEDULE
EVERY 1 DAY
DO
BEGIN
DELETE FROM `logs`.`user` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
DELETE FROM `logs`.`messages` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
UPDATE `logs`.`activity` SET `last_cleanup` = CURRENT_TIMESTAMP;
END |
delimiter ;
Si un EVENT
du même nom existe déjà, le serveur renvoie une erreur. On peut l'éviter avec IF NOT EXISTS
:
CREATE EVENT `évènement2`
IF NOT EXISTS
ON SCHEDULE EVERY 2 DAY
DO
OPTIMIZE TABLE `wiki1`.`news`
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
:
CREATE EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
ON COMPLETION PRESERVE
DO
OPTIMIZE TABLE `wiki1`.`news`
On peut aussi dire explicitement à MySQL de le supprimer :
CREATE EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
ON COMPLETION NOT PRESERVE
DO
OPTIMIZE TABLE `wiki1`.`news`
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é :
CREATE EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
ON COMPLETION NOT PRESERVE
DISABLE
DO
OPTIMIZE TABLE `wiki1`.`news`
Pour le modifier : ALTER EVENT
.
On peut aussi commenter l'évènement dans une limite de 64 caractères :
CREATE EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
ON COMPLETION NOT PRESERVE
DISABLE
COMMENT 'let\'s optimize some tables!'
DO
OPTIMIZE TABLE `wiki1`.`news`
Par ailleurs, on peut modifier l'utilisateur de l'évènement pour obtenir d'autres permissions. Par exemple depuis celui voulu avec CURRENT_USER
:
CREATE DEFINER = CURRENT_USER
EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
DO
OPTIMIZE TABLE `wiki1`.`news`
Spécifier un autre utilisateur nécessite les droits root :
CREATE DEFINER = 'allen@localhost'
EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
DO
OPTIMIZE TABLE `wiki1`.`news`
ALTER EVENT
[modifier | modifier le wikicode]Renommage d'un évènement :
CREATE EVENT `évènement2`
ON SCHEDULE EVERY 2 DAY
ON COMPLETION NOT PRESERVE
RENAME TO `évènement3`
DISABLE
COMMENT 'let\'s optimize some tables!'
DO
OPTIMIZE TABLE `wiki1`.`news`
On peut aussi ne définir que la clause à modifier :
CREATE EVENT `évènement2` ENABLE;
DROP EVENT
[modifier | modifier le wikicode]Avec les permissions sur l'évènement à supprimer :
DROP EVENT `évènement3`
S'il n'existe pas l'erreur 1517 survient. Pour l'éviter :
DROP EVENT IF EXISTS `évènement3`
Métadonnées des EVENT
[modifier | modifier le wikicode]SHOW CREATE EVENT
[modifier | modifier le wikicode]Cette commande retourne la commande CREATE EVENT
utilisée pour créer le trigger, et sur les paramètres l'impactant.
SHOW CREATE EVENT évènement2
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
[modifier | modifier le wikicode]Pour afficher tous les évènements de la base courante :
SHOW EVENTS
Pour une base en particulier :
SHOW EVENTS FROM `wiki1`
-- Ou
SHOW EVENTS IN `wiki1`
Autres filtres :
SHOW EVENTS LIKE 'év%'
SHOW EVENTS WHERE definer LIKE 'admin@%'
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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]- 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
[modifier | modifier le wikicode]Flow control
[modifier | modifier le wikicode]Les mots clés sont : IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT
[1]. Ils ne sont utilisables que dans les procédures stockées.
Loops
[modifier | modifier le wikicode]WHILE
[modifier | modifier le wikicode]DELIMITER $$
CREATE PROCEDURE compteur()
BEGIN
DECLARE x INT;
SET x = 1;
WHILE x <= 5 DO
SET x = x + 1;
END WHILE;
SELECT x; -- 6
END$$
DELIMITER ;
LOOP
[modifier | modifier le wikicode]DELIMITER $$
CREATE PROCEDURE compteur2()
BEGIN
DECLARE x INT;
SET x = 1;
boucle1: LOOP
SET x = x + 1;
IF x > 5 THEN
LEAVE boucle1;
END IF;
END LOOP boucle1;
SELECT x; -- 6
END$$
DELIMITER ;
REPEAT
[modifier | modifier le wikicode]DELIMITER $$
CREATE PROCEDURE compteur3()
BEGIN
DECLARE x INT;
SET x = 1;
REPEAT
SET x = x + 1; UNTIL x > 5
END REPEAT;
SELECT x; -- 6
END$$
DELIMITER ;
Curseurs
[modifier | modifier le wikicode]Les curseurs permettent de traiter chaque ligne différemment, mais cela ralentit considérablement les requêtes.
DELIMITER $$
CREATE PROCEDURE curseur1()
BEGIN
DECLARE resultat varchar(100) DEFAULT "";
DECLARE c1 CURSOR FOR
SELECT page_title
FROM wiki1.wiki1_page
WHERE page_namespace = 0;
OPEN c1;
FETCH c1 INTO resultat;
CLOSE c1;
SELECT resultat;
END;$$
DELIMITER ;
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 :
-- Concatène toutes les valeurs d'une colonne sur une ligne
DELIMITER $$
CREATE PROCEDURE curseur2()
BEGIN
DECLARE resultat varchar(100) DEFAULT "";
DECLARE total text DEFAULT "";
DECLARE fin BOOLEAN DEFAULT 0;
DECLARE c2 CURSOR FOR
SELECT page_title
FROM wiki1.wiki1_page
WHERE page_namespace = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;
OPEN c2;
REPEAT
FETCH c2 INTO resultat;
set total = concat(total, resultat);
UNTIL fin END REPEAT;
CLOSE c2;
SELECT total; -- AccueilMySQLPHPPHP
END;$$
DELIMITER ;
Gestion des erreurs
[modifier | modifier le wikicode]La déclaration d'un "handler" permet de spécifier un traitement en cas d'erreur[2] :
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
De plus, le type d'erreur peut être précisé :
DECLARE CONTINUE HANDLER FOR SQLSTATE [VALEUR]
DECLARE CONTINUE HANDLER FOR SQLWARNING
DECLARE CONTINUE HANDLER FOR NOT FOUND
mysql_affected_rows()
[modifier | modifier le wikicode]Cette fonction renvoie le nombre de lignes impactées par la requête précédente[3].
Références
[modifier | modifier le wikicode]- ↑ 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
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 :
[0-9]+
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 :
SELECT '123' REGEXP '^[0-9]+$'; -- 1
Les expressions rationnelles peuvent être analysées et testées via un débogueur en ligne comme https://regex101.com/.
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 ":;<=>?@"[1].
|
[^...]
|
crochets et circonflexe | classe complémentée : tous les caractères sauf ceux énumérés. |
^
|
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. |
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é.
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 :
|
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.
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[2].
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 |
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 |
\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.
- 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
?>
: 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
- Chercher une lettre u précédée d'une lettre q :
?<!
: 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 :
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[7].
(?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
[modifier | modifier le wikicode]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[8].
MySQL propose plusieurs opérateurs et fonctions pour le regex[9]. toutefois, c'est assez limité car en MySQL 8.0 il n'existe toujours pas de groupe de capture.
REGEXP
[modifier | modifier le wikicode]Syntaxe
[modifier | modifier le wikicode]SELECT 'string' REGEXP 'pattern'
RLIKE
est synonyme de REGEXP
.
Dans MySQL, le double antislash fait office de caractère d'échappement.
Exemples
[modifier | modifier le wikicode]- Est-ce que la sélection est différente des lettres de A à Z :
SELECT 'a' REGEXP '^[a-z]'; -- 1
SELECT 'A' REGEXP '^[a-z]'; -- 1
SELECT '1' REGEXP '^[a-z]'; -- 0
SELECT 'é' REGEXP '^[a-z]'; -- 0
- Sélectionner les pages de wiki qui matchent un pattern :
USE frwiki_p;
SELECT p.page_title
FROM page p
JOIN page_props pp ON p.page_id = pp.pp_page
WHERE p.page_namespace = 0 AND pp.pp_page REGEXP '\n *titre *='
REGEXP_LIKE
[modifier | modifier le wikicode]Même rôle que REGEXP
mais on peut définir les options en argument. Ex :
SELECT REGEXP_LIKE('A', '^[a-z]', 'i'); -- 1 SELECT REGEXP_LIKE('A', '^[a-z]', 'c'); -- 0
REGEXP_REPLACE
[modifier | modifier le wikicode]Fonctionne comme la fonction REPLACE() mais avec du regex. Exemple pour retirer les caractères spéciaux d'une chaine :
select REGEXP_REPLACE('hello+$ù^:world', '[^a-z]', '');
-- helloworld
REGEXP_SUBSTR
[modifier | modifier le wikicode]Extrait une sous-chaine (substring). Ex :
SELECT REGEXP_REPLACE(REGEXP_REPLACE('<html><title>Page 1</title>...</html>', '.*<title>', ''), '</title>.*', '');
-- Page 1
Références
[modifier | modifier le wikicode]- ↑ 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., (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
[modifier | modifier le wikicode]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[1] :
- Geometry
- Point
- LineString
- Polygon
- MultiPoint
- MultiLineString
- MultiPolygon
- GeometryCollection
Et six relations entre eux[2] :
- Contains
- Disjoint
- Equals
- Intersects
- Overlaps
- Within
Requêtes
[modifier | modifier le wikicode]Références
[modifier | modifier le wikicode]
Importer et exporter
Exporter
[modifier | modifier le wikicode]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
:
SELECT * FROM destinataire INTO OUTFILE '/tmp/test' WHERE id IN (41, 141, 260, 317, 735, 888, 1207, 2211);
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 :
mysql < query.txt > output.txt
Ou bien via mysqldump
.
Importer
[modifier | modifier le wikicode]Pour importer un fichier :
LOAD DATA INFILE '/tmp/test' INTO TABLE destinataire;
Le fichier doit se trouver sur le serveur MySQL.
Options additionnelles :
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES
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 :
LOAD DATA LOCAL INFILE
'/tmp/test'
INTO TABLE destinataire
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
@dummy,
name,
phone_number,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy,
@dummy
)
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
[modifier | modifier le wikicode]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 :
DROP DATABASE IF EXISTS `base1`;
CREATE DATABASE `base1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `base1`;
--DROP USER `utilisateur1`@'localhost';
CREATE USER 'utilisateur1'@'localhost' IDENTIFIED BY 'p@ssword1';
GRANT USAGE ON *.* TO 'utilisateur1'@'localhost' IDENTIFIED BY 'p@ssword1';
GRANT ALL PRIVILEGES ON `base1`.* TO 'utilisateur1'@'localhost';
-- ou GRANT ALL ON `base1` TO 'utilisateur1'@'localhost';
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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]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
[modifier | modifier le wikicode]Dans /etc/mysql/my.cnf
, section [mysqld]
:
- Définir un identifiant de serveur ; par exemple
1
:
server-id = 1
- La réplication est basée sur les logs binaires, donc les activer :
log-bin # ou log-bin = /var/log/mysql/mysql-bin.log
Créer un nouvel utilisateur pour que le slave puisse se connecter :
CREATE USER 'myreplication';
SET PASSWORD FOR 'myreplication' = PASSWORD('mypass');
GRANT REPLICATION SLAVE ON *.* to 'myreplication';
Vérifier l'identifiant de serveur :
SHOW VARIABLES LIKE 'server_id';
Configuration de chaque slave
[modifier | modifier le wikicode]Dans /etc/mysql/my.cnf
, section [mysqld]
:
- Définir un identifiant de serveur différent du master et des autres slaves :
server-id = 2
- Vérifier avec :
SHOW VARIABLES LIKE 'server_id';
- Il est aussi possible de déclarer le nom de la machine slave dans le master (cf.
SHOW SLAVE HOSTS
) :
report-host=slave1
Déclarer le master :
CHANGE MASTER TO MASTER_HOST='master_addr', MASTER_USER='myreplication', MASTER_PASSWORD='mypass';
Si la réplication sert de backup, spécifier le point de départ :
MASTER_LOG_FILE='<binary_log_from_master>', MASTER_LOG_POS=<master_binary_log_position>;
Démarrer la réplication :
START SLAVE;
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
[modifier | modifier le wikicode]Sur le slave
[modifier | modifier le wikicode] SHOW SLAVE STATUS;
Ou bien pour avoir un résultat formaté plus lisible :
SHOW SLAVE STATUS\G
Exemple :
*************************** 1. row *************************** Slave_IO_State: Master_Host: master_addr Master_User: myreplication Master_Port: 3306 ...
Vérifier en particulier :
Slave_IO_Running: Yes Slave_SQL_Running: Yes
On peut supposer une nature réplication asynchrone :
Seconds_Behind_Master: 0
Voir aussi :
mysql> SHOW GLOBAL VARIABLES LIKE "%SLAVE%";
Sur le master
[modifier | modifier le wikicode]Vérifier les connexions des slaves :
mysql> SHOW PROCESSLIST\G
[...]
*************************** 6. row ***************************
Id: 14485
User: myreplication
Host: 10.1.0.106:33744
db: NULL
Command: Binlog Dump
Time: 31272
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
If you enabled <code>report-host</code>, the slave is also visible in:
mysql> SHOW SLAVE HOSTS;
+-----------+---------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+-----------+---------+------+-------------------+-----------+
| 2 | myslave | 3306 | 0 | 1 |
+-----------+---------+------+-------------------+-----------+
1 row in set (0<