MySQL

Logo MySql
Tous mes trucs et astuces pour utiliser MySQL

Afficher verticalement le résulat d'une requête

Généralement, le résultat d'une requête est affiché horizontalement.

mysql> SELECT * FROM test2 LIMIT 0,5;
+----+-----+
| id | val |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+
5 rows in set (0.00 sec)

Pour afficher le résultat verticalement, il suffit de remplacer le ";" par "\G".

mysql> SELECT * FROM test2 LIMIT 0,5\G
*************************** 1. row ***************************
 id: 1
val: 10
*************************** 2. row ***************************
 id: 2
val: 20
*************************** 3. row ***************************
 id: 3
val: 30
*************************** 4. row ***************************
 id: 4
val: 40
*************************** 5. row ***************************
 id: 5
val: 50
5 rows in set (0.00 sec)

Ajouter un super utilisateur MySql

Les commandes suivantes ajoute l'utilisateur "superUser" ayant tous les droits "ALL PRIVILEGES.....WITH GRANT OPTION" sur toutes les bases de sonnées "*.*", avec le mot de passe "secret" et pouvant se connecter de n'importe quelle machine "%":

Se connecter au serveur mysql avec l'utilisateur root

$ mysql -u root -p
> GRANT ALL PRIVILEGES ON *.* TO 'superUser'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION;
> FLUSH PRIVILEGES;

 

Etiquettes: 

Connexion automatique à un serveur MySQL

Sous Ubuntu, par exemple, pour se connecter à un serveur MySQL, il est nécessaire de saisir dans la console la commande suivante :

$ mysql -h mon_serveur_mysql -u nom_user -p nom_base_de_donnees

-h pour indiquer le serveur mysql (optionnel si le serveur est localhost)
-u pour indiquer le nom d'utilisateur
-p pour permettre la saisie du mot de passe
et on termine la commande par le nom de la base de données à utiliser (optionnel).

Il existe un moyen plus simple pour se connecter à MySQL surtout si les paramètres de connexions sont souvent les mêmes.
Pour cela, il suffit de créer dans son répertoire personnel le fichier .my.cnf.

$ touch ~/.my.cnf

D'y renseigner les informations suivantes :

$ cat ~/.my.cnf
[client]
user = nom_user
password = mon_password
host = adresse_ip_ou_fqdn_du_serveur_mysql

De protéger le fichier des regards indiscrets

$ chmod 0600 ~/.my.cnf

Dorénavant, cette simple commande suffira à se connecter au serveur MySQL :

$ mysql nom_base_de_donnees

Créer un trigger

Pour créer un trigger dans MySql suite à une insertion de données avec enregistrement de données dans une autre table

$ .
> CREATE TRIGGER nom_du_trigger AFTER INSERT ON nom_de_la_table_a_surveiller
> FOR EACH ROW
> INSERT INTO nom_de_la_table (champs1, champs2, champs3) VALUES ( valeur1, valeur2, valeur3);

 

Pour créer un trigger dans MySql suite à une modification de données avec enregistrement de données dans
une autre table

$ .
> CREATE TRIGGER nom_du_trigger AFTER UPDATE ON nom_de_la_table_a_surveiller
> FOR EACH ROW
> INSERT INTO nom_de_la_table (champs1, champs2, champs3) VALUES ( valeur1, valeur2, valeur3);

Pour créer un trigger dans MySql suite à une suppression de données avec enregistrement de données dans
une autre table

$ .
> CREATE TRIGGER nom_du_trigger AFTER DELETE ON nom_de_la_table_a_surveiller
> FOR EACH ROW
> INSERT INTO nom_de_la_table (champs1, champs2, champs3) VALUES ( valeur1, valeur2, valeur3);


 
A la place du mot clé AFTER, nous pouvons également utiliser BEFORE qui exécute l'action avant l'évènement.
 
Pour récupérer les valeurs de la table surveillée utiliser les mots clés OLD et/ou NEW (OLD.nom_du_champ ou NEW.nom_du_champ)
 
Par exemple :

$ .
> CREATE TRIGGER mon_trigger AFTER INSERT ON ma_table
> FOR EACH ROW
> INSERT INTO ma_table_log (id_ajoute, valeur1_ajoutee, valeur2_ajoutee) VALUES ( NEW.id, NEW.valeur1,
NEW.valeur2);

 

Etiquettes: 

Gestion des dates

Dans MySql, les dates sont au format yyyy-mm-dd (aaaa-mm-jj)
 
Ajouter x jour(s) à un champ de type date :
SELECT DATE_ADD(champ_date, INTERVAL x DAY) FROM ma_table
mysql> SELECT DATE_ADD('2013-09-06', INTERVAL 10 DAY) FROM sys;
2013-09-16
ou
SELECT champ_date + INTERVAL x DAY FROM ma_table
mysql> SELECT '2013-09-06' + INTERVAL 10 DAY FROM sys;
2013-09-16
Ajouter x mois à un champ de type date :
SELECT DATE_ADD(champ_date, INTERVAL x MONTH) FROM ma_table
mysql> SELECT DATE_ADD('2013-09-06', INTERVAL 2 MONTH) FROM sys;
2013-11-06
Ajouter x année(s) à un champ de type date :
SELECT DATE_ADD(champ_date, INTERVAL x YEAR) FROM ma_table
mysql> SELECT DATE_ADD('2013-09-06', INTERVAL 2 YEAR) FROM sys;
2015-09-06
Mettre à jour un champ de type date en y ajoutant x jour(s) et/ou x mois et/ou x année(s) :
UPDATE ma_table SET champ_date = DATE_ADD(champ_date, INTERVAL x DAY)
UPDATE ma_table SET champ_date = DATE_ADD(champ_date, INTERVAL x MONTH)
UPDATE ma_table SET champ_date = DATE_ADD(champ_date, INTERVAL x YEAR)
Pour un retranchement, utiliser le signe moins (-) devant la valeur x
mysql> SELECT DATE_ADD('2013-09-06', INTERVAL -2 YEAR) FROM sys;
2011-09-06
Afficher la date du jour dans une requête :
SELECT CURRENT_DATE FROM ma_table
mysql> SELECT CURRENT_DATE FROM sys;
2013-09-06
ou
SELECT CURDATE() FROM ma_table
mysql> SELECT CURDATE() FROM sys;
2013-09-06
Afficher le jour, le mois ou l'année d'un champ de type date :
SELECT DAY(champ_date) FROM ma_table
mysql> SELECT DAY('2013-09-06') FROM sys;
6
SELECT MONTH(champ_date) FROM ma_table
mysql> SELECT MONTH('2013-09-06') FROM sys;
9
SELECT YEAR(champ_date) FROM ma_table
mysql> SELECT YEAR('2013-09-06') FROM sys;
2013

 

Etiquettes: 

Ignorer l'erreur "Duplicate entry for key"

Lors d'une insertion en masse dans une table Mysql, si une clé existe déjà, la totalité de l'insertion n'est pas exécutée.

Pour ignorer cette erreur, il suffit d'utiliser le mot clé IGNORE dans la requête d'insertion.

Exemple :

Définition de la table test2

CREATE TABLE IF NOT EXISTS `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `val` (`val`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Définition d'une clé unique sur le champ "val".

Données de la table

mysql> SELECT * FROM test2;
+----+-----+
| id | val |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+
5 rows in set (0.00 sec)

Requête d'insertion en masse

mysql> INSERT INTO test2 (val) VALUES (60), (70), (10), (80);
ERROR 1062 (23000): Duplicate entry '10' for key 'val'
mysql> SELECT * FROM test2;
+----+-----+
| id | val |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
|  6 |  60 |
|  7 |  70 |
+----+-----+
7 rows in set (0.00 sec)

Lors de l'insertion, une erreur est générée pour la valeur "10".
Celle-ci existe déjà dans la table.

Le "SELECT" montre que seules les valeurs "60" et "70" ont été insérées.
La valeur "80" n'a pas été insérée car la requête a générée une erreur au moment de l'insertion de la valeur "10".

Requête d'insertion avec le mot clé "IGNORE"

mysql> INSERT IGNORE INTO test2 (val) VALUES (80), (90), (10), (100);
Query OK, 3 rows affected (0.00 sec)
Records: 4  Duplicates: 1  Warnings: 0

mysql> SELECT * FROM test2;
+----+-----+
| id | val |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
|  6 |  60 |
|  7 |  70 |
|  8 |  80 |
|  9 |  90 |
| 10 | 100 |
+----+-----+
10 rows in set (0.00 sec)

Le résultat de la requête indique que 3 insertions ont été exécutées sur 4.
La valeur "10" a été ignorée.

Le "SELECT" montre que les valeurs "80", "90" et "100" ont bien été insérées.

Etiquettes: 

MySQL: Mettre à jour le champ d'une table en fonction d'un champ d'une autre table

L'exemple suivant permet de mettre à jour le champ nom_region de la table codes_postaux à partir du champ nom_region de la table departements

UPDATE
    codes_postaux c
SET
    c.nom_region = (
    SELECT
        d2.nom_region
    FROM
        departements d2
    WHERE
        d2.code_departement = LEFT(c.Postcode_from,
        2))
WHERE
    EXISTS (
    SELECT
        1
    FROM
        departements d1
    WHERE
        d1.code_departement = LEFT(c.Postcode_from,
        2) )
    AND c.Country_name = 'FRANCE'

Ceci grâce principalement au mot clé EXISTS

Personnellement, je trouve que cette syntaxe est beaucoup plus rapide que certaines autres trouvées sur internet comme par exemple avec une jointure avec la table source entre le UPDATE et le SET

UPDATE table1 AS b
INNER JOIN table2 AS g ON b.champ1= g.champ1
SET b.champ2= g.champ2
WHERE  (b.champ2= '' or b.champ2= 0) and
  g.champ2 > 0
Etiquettes: 

Restaurer / importer une base de données MySql

Pour restaurer une base de données

Attention, il faut que la base de données soit créée auparavant

Dans une console

$ mysql -h adresse_ip -u user -p -D nom_de_la_base_de_données < nom_du_fichier_sql

Par exemple

$ mysql -h localhost -u root -p -D mesvideos < mesvideos.sql

Cela a pour effet d'importer le contenu du fichier mesvideos.sql dans la base de données mesvideos.

Pour restaurer toutes les bases de données

Pour info, il n'est pas nécessaire que les bases de données soient créées auparavant

Dans une console

$ mysql -h adresse_ip -u user -p < nom_du_fichier_sql

Par exemple

$ mysql -h localhost -u root -p < allbases.sql

Cela a pour effet d'importer la totalité des bases de données contenues dans le fichier allbases.sql.

Etiquettes: 

Sauvegarder / exporter une base de données

Pour sauvegarder une base de données

Dans une console

$ mysqldump -h adresse_ip -u user -p nom_de_la_base_de_donnees > nom_du_fichier_sql

Par exemple

$ mysqldump -h localhost -u root -p mesvideos > mesvideos.sql

Cela a pour effet d'exporter toutes les données de la base mesvideos dans le fichier mesvideos.sql

Pour sauvegarder toutes les bases de données

Dans une console

$ mysqldump -h adresse_ip -u user -p -A > nom_du_fichier_sql

Par exemple

$ mysqldump -h localhost -u root -p -A > allbases.sql

Cela a pour effet d'exporter toutes les bases de données dans le fichier allbases.sql

Etiquettes: 

Se connecter à un serveur MySql à partir de n'importe quel host

Par défaut, sur une distribution style Debian, le serveur MySql accepte les connexions uniquement à partir de la machine locale.

Toutes connexions à partir d'un host différent sont automatiquement refusées.

$ netstat -lpn | grep mysql
tcp        0      0 127.0.0.1:3306            0.0.0.0:*               LISTEN      4884/mysqld

Pour autoriser les connexions à partir de n'importe quels hosts, il faut modifier le fichier /etc/mysql/my.cnf.

Modifier le ligne bind-address = 127.0.0.1 et remplacer 127.0.0.1 par 0.0.0.0

$ cat /etc/mysql/my.cnf | grep "bind-address"
bind-address            = 0.0.0.0

Redémarrer le serveur MySql afin d'appliquer la modification

$ service mysql restart

Puis vérifier avec la commande netstat

$ netstat -lpn | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      6039/mysqld

Etiquettes: 

Utilisation de MySQL en console

Connexion MySQL

Pour se connecter au sevreur MySQL depuis une console

$ mysql -u nom_du_user -p

puis saisir le mot de passe

Etiquettes: 

Ajouter ou modifier le mot de passe ROOT

Pour changer le mot de passe de l'utilisateur root (qui n'est pas le même que l'utilisateur root du système), tapez la commande suivante dans un terminal :

$ sudo mysqladmin -u root password nouveau_mot_de_passe -p

Redéfinir de force le mot de passe ROOT

Méthode 1

On stoppe le serveur MySQL

$ sudo /etc/init.d/mysql stop

On se connecte au serveur sans tenir compte des privilèges utilisateurs :

$ sudo /usr/bin/mysqld_safe --skip-grant-tables & mysql -h localhost

Nous allons utiliser la table contenant les paramètres de MySQL :  

$ > use mysql

 

On met en place le nouveau mot de passe :

$ > update user set password = password('<votre_mot_de_passe>') where user = 'root' and host='localhost';

On quitte le prompt de MySQL :

$ > quit

On redémarre le serveur MySQL en utilisation normale :

$ sudo /etc.init.d/mysql restart

Méthode 2

$ sudo /etc/init.d/mysql reset-password

Méthode 3

$ sudo dpkg-reconfigure mysql-server-5.0

Etiquettes: 

Controler l'intégrité des bases de données

$ mysqlcheck -A -u root -p

Puis saisir le mot de passe

 

Enregistrer le résultat d'une requête dans un fichier

Se connecter à MySQL :

$ mysql -h localhost -u user -p

Sélectionner la base de données :

$ .
> USE maBaseDeDonnees;

Exécuter la requête :

$ .
> SELECT * INTO OUTFILE '/home/user/monFichier.txt' FROM maTable;

! Il faut que le user connecté à MySQL ait l'autorisation d'écrire dans le répertoire indiqué

Etiquettes: 

Sélectionner la base de données à utiliser

Il existe 2 manières pour sélectionner une base de données MySQL

Directement lors de la connexion

$ mysql -u root -p votre_base

Une fois connecté à MySQL mysql

$ .
> use votre_base;

Etiquettes: