Aller au contenu | Aller au menu | Aller à la recherche


Tips SQL

Par PlaceOweb le vendredi, mai 1 2009, 16:09 - SQL - Lien permanent

Astuces pour SQL, MySQL, Oracle et les autres systèmes de gestion de base de données (SGDB).

MySQL

Se connecter avec le client MySQL sans la complétion syntaxique qui pose problème avec les tabulation

mysql --no-auto-rehash

Activation des log sous MySQL sans redémarrer le serveur

Voici une commande permettant d'enregistrer l'ensemble des requêtes exécutées sur votre serveur MySQL, ce qui peut servir en production à tracer des programmes foireux ou douteux dans leurs résultats...

-- Activer les logs MySQL à chaud sans redémarrer le serveur MySQL pour tracer les requêtes MySQL.

SELECT VERSION(), @@general_log, @@log_output, @@general_log_file;
5.1.58-1~dotdeb.0 	0 	FILE 	/var/run/mysqld/mysqld.log

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';
-- SET GLOBAL log_output = 'TABLE';

SELECT @@general_log, @@log_output, @@general_log_file;
5.1.58-1~dotdeb.0 	1 	FILE 	/var/log/mysql/mysql.log

-- Tail -f /var/log/mysql/mysql.log

Gestion des dates heures (datetime timestamp) MySql

Consultez les articles :

Affectation des variables utilisateur

SELECT @user_mysql := CURRENT_USER() AS user_mysql;
-- Enregistre la variable utilisateur de session "@user_mysql", et retourne dans le select le champ "user_mysql"

Voyez User-Defined Variables

Numération d'un auto ID virtuel

Numéroter les lignes lors d'une requête sur une table, ou remettre à jour les valeurs de d'un champs de type ID auto incrément.

SET @n:= 0;
SELECT @n:=@n+1 AS 'otAutoId' , t.* FROM maTable AS t;
SET @n:= 0;
UPDATE maTable SET monChampsId = @n:=@n+1;

SELECT field FROM table WHERE colomn = NULL ! BAD !!!

Avec les prepared statement, attention au valeurs nulles

-- Ne fonctionne pas
SELECT  field FROM table WHERE colomn = NULL;
-- Vous devez changer la requête en 
SELECT  field FROM table WHERE colomn IS NULL;
-- Ou alors génériquement, acceptant aussi bien une valeur non nulle qu'une valeur nulle
-- <=> Comparaison compatible avec NULL
-- Si l'un ou les deux arguments sont NULL, le résultat de la comparaison est NULL, exception faite pour l'opérateur <=>
SELECT  field FROM table WHERE colomn <=> NULL;

Voir aussi comment MySQL optimise IS NULL et

Finalement, pour faire une comparaison d'opposition de colonnes avec potentiellement des valeurs nulle, il faut utiliser : !(col1 <=> col2)

SELECT 
-- BAD not equal comparaison
IF(null <> null, 'different','same') AS noteq1BAD,
IF(null <> '2014-01-01', 'different','same') AS noteq2BAD,
IF('2014-01-01' <> '2014-01-01', 'different','same') AS noteq3BAD,
IF('2014-05-06' <> '2014-01-01', 'different','same') AS noteq4BAD,
-- correct equal comparaison
IF(null <=> null, 'same','different') AS eq1,
IF(null <=> '2014-01-01', 'same','different') AS eq2,
IF('2014-01-01' <=> '2014-01-01', 'same','different') AS eq3,
IF('2014-05-06' <=> '2014-01-01', 'same','different') AS eq4,
-- correct not equal comparaison (Way 1)
IF(IF(null <=> null, false, true), 'different','same') AS noteq1w1,
IF(IF(null <=> '2014-01-01', false, true), 'different','same') AS noteq2w1,
IF(IF('2014-01-01' <=> '2014-01-01', false, true), 'different','same') AS noteq3w1,
IF(IF('2014-05-06' <=> '2014-01-01', false, true), 'different','same') AS noteq4w1,
-- correct not equal comparaison (Way 2)
IF(!(null <=> null), 'different','same') AS noteq1w2,
IF(!(null <=> '2014-01-01'), 'different','same') AS noteq2w2,
IF(!('2014-01-01' <=> '2014-01-01'), 'different','same') AS noteq3w2,
IF(!('2014-05-06' <=> '2014-01-01'), 'different','same') AS noteq4w2
;
+-+-+-+-++-++-++-++-++-++-+
| noteq1BAD | noteq2BAD | noteq3BAD | noteq4BAD | eq1  | eq2       | eq3  | eq4       | noteq1w1 | noteq2w1  | noteq3w1 | noteq4w1  | noteq1w2 | noteq2w2  | noteq3w2 | noteq4w2  |
+-+-+-+-++-++-++-++-++-++-+
| same      | same      | same      | different | same | different | same | different | same     | different | same     | different | same     | different | same     | different |
+-+-+-+-++-++-++-++-++-++-+

Les IF et NULL

SELECT IF( NULL IS NULL , 'Est NULL', 'Non NULL' );
-- Est NULL
SELECT IF( 'Valeur non nulle' IS NULL , 'Est NULL', 'Non NULL' );
-- Non NULL

Voir aussi les fonctions de contrôle IFNULL NULLIF IF CASE

Le cas du NULL avec un CONCAT

SELECT CONCAT('myTest','IsValid');
-- myTestIsValid

SELECT CONCAT('myTest',null);
-- NULL
SELECT CONCAT_WS(',' ,'myTest','IsValid');
-- myTest,IsValid

SELECT CONCAT_WS(',' ,'myTest',null);
-- myTest

SELECT CONCAT_WS(',' ,null,null);
-- (chaîne vide)

Remplacer une valeur (sous chaîne) dans un champs sur tous les enregistrements

Comment remplacer (avec REPLACE) toutes les occurrences de "aChanger" par "valeurDeRemplacement" dans un champs d'une table ?

Par exemple avec "aChanger" valant "%5" et "valeurDeRemplacement" valant "%PPP_ISARD%" dans le champs "champsToDo"

-- On vérifie que le résultat attendu
SELECT champsToReplace , REPLACE(champsToReplace, '%5', '%PPP_ISARD%') FROM tableReplace;
-- On replace
UPDATE tableReplace SET champsToReplace = REPLACE(champsToReplace, '%5', '%PPP_ISARD%');

-- Par exemple pour remplacer n'importe quel domaine mail, par un autre défini
SELECT correspondant_mail , 
REPLACE(correspondant_mail,  RIGHT( correspondant_mail, LENGTH(correspondant_mail) - LOCATE('@',correspondant_mail) + 1 ), '@yopmail.com') 
FROM correspondants
;
UPDATE correspondants SET correspondant_mail = REPLACE(correspondant_mail,  RIGHT( correspondant_mail, LENGTH(correspondant_mail) - LOCATE('@',correspondant_mail) + 1 ), '@yopmail.com')
;

-- Ou avec INSERT
SELECT champsToReplace, INSERT(champsToReplace, 6, 1, '1') FROM tableReplace
WHERE RIGHT(champsToReplace,7) BETWEEN '000-004' AND '000-008';

Voyez les ressources MySQL :

  • Fonctions de chaînes de caractères String Functions
    • INSERT(str,pos,len,newstr) Retourne une chaîne de caractères str, après avoir remplacé la portion de chaîne commençant à la position pos et de longueur len caractères, par la chaîne newstr
    • REPLACE(str,from_str,to_str) Retourne une chaîne de caractères str dont toutes les occurrences de la chaîne from_str sont remplacées par la chaîne to_str

Voyez aussi des fonctions pour remplacer le contenu d'une string :

La plus petite valeur (minimum entre plusieurs champs)

SELECT LEAST(COALESCE('2013-01-01', null), COALESCE(null, '2013-01-01')) AS dateMinimumBetween2Dates;
-- 2013-01-01
SELECT LEAST(COALESCE('2013-01-01', '2010-01-01'), COALESCE('2010-01-01', '2013-01-01')) AS dateMinimumBetween2Dates;
-- 2010-01-01
  • Opérateurs de comparaison
    • COALESCE(value1,value2,...) Retourne le premier élément non-NULL de la liste
    • GREATEST(value1,value2,...) Retourne la valeur la plus grande de la liste. Les arguments sont comparés en utilisant les mêmes règles que pour LEAST().
    • LEAST(value1,value2,...) Retourne la plus petite valeur de la liste.

Connaitre l'utilisateur mysql en cours et extraire le schéma sans le host

SELECT SUBSTRING_INDEX(USER(),'@',1), USER(), SESSION_USER(), SYSTEM_USER(), CURRENT_USER();
-- SUBSTRING_INDEX(USER(),'@',1) 	USER() 	SESSION_USER() 	SYSTEM_USER() 	CURRENT_USER()
-- phpmyadmin 	phpmyadmin@192.168.30.15 	phpmyadmin@192.168.30.15 	phpmyadmin@192.168.30.15 	phpmyadmin@192.168.%

Avec cet exemple on constate que l'utilisateur phpmyadmin se connecte au serveur 192.168.30.15, et l'utilisateur autorisé au niveau du serveur est "phpmyadmin" en provenance de "192.168.%", donc CURRENT_USER() se distingue des autres en citant l'utilisateur utilisé par MySQL.

Voyez les fonctions d'information MySQL SESSION_USER() et SYSTEM_USER() sont des synonymes de USER().

SELECT SQL_CALC_FOUND_ROWS && FOUND_ROWS()

Voyez la fonction FOUND_ROWS() Pour un SELECT avec une clause LIMIT, le nombre de lignes qui aurait été retourné s'il n'y avait pas la clause LIMIT

Ainsi vous pouvez évitez de faire 2 requêtes lors de vos pagination, le SELECT * LIMIT 50,10 pour les données et le SELECT COUNT(*) pour calculer le nombre total de lignes afin de faire du page par page.

Une instruction SELECT peut inclure une clause LIMIT pour restreindre le nombre de lignes renvoyées par le serveur au client. Dans certains cas, il est souhaitable de savoir combien de lignes de la déclaration aurait retourné, sans la clause LIMIT, mais sans lancer à nouveau. Pour obtenir ce nombre de lignes, notamment une option SQL_CALC_FOUND_ROWS dans l'instruction SELECT, puis appelez FOUND_ROWS() après:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

Le second SELECT retourne un nombre indiquant combien de lignes le premier SELECT aurait retourné s'il n'avait pas été écrit sans la clause LIMIT.

SQL_CALC_FOUND_ROWS et FOUND_ROWS() peuvent être utile dans les situations où vous voulez restreindre le nombre de lignes retourné par une requête, mais aussi déterminer le nombre de lignes dans le résultat ensemble complet sans courir de nouveau la requête. Un exemple est un script web qui présente un affichage page contenant des liens vers les pages qui montrent d'autres sections d'un résultat de recherche. UtiliserFOUND_ROWS() vous permet de déterminer combien d'autres pages sont nécessaires pour le reste du résultat.

L'utilisation de SQL_CALC_FOUND_ROWS et FOUND_ROWS () est plus complexe pour les requêtes UNION que pour les commandes SELECT simples, car LIMIT peut intervenir plusieurs fois dans une UNION. Elle peut être appliquée à différentes commandes SELECT de l'UNION, ou globalement à l 'Union dans son ensemble.

GROUP_CONCAT pour sélectionner plusieurs lignes en une seule

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
             [SEPARATOR str_val])

SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
                         ORDER BY test_score DESC SEPARATOR " ")
FROM student
GROUP BY student_name;

HAVING

WITH ROLLUP

La clause GROUP BY permet l'utilisation de l'option WITH ROLLUP qui fait que des lignes supplémentaires seront ajoutées lors de regroupements, idéal pour le total par exemple.

Par contre, en utilisant WITH ROLLUP, vous ne pouvez pas combiner l'utilisation de ORDER BY.

Et pour définir la la colonne a valeur nulle, utilisez COALESCE(fieldname, 'Total') à la place de IFNULL(fieldname, 'Total').

CAST, CONVERT, BINARY

MySQL Fonctions de conversion de convertir une valeur d'un type de données à l'autre. Les conversions peuvent être effectuées entre chaîne, date et type de données numériques.

XML MySQL

Pour extraire une donnée d'un champs xml, on utilise ExtractValue() (basé sur XPath)

-- Extraction du contenu de la balise "<laBalise>" et de la valeur de l'attribut "<lAttribut>" de la balise "<laBalise>"
SELECT id, xml ,
ExtractValue(xml, '//laBalise') AS valeur_de_la_balise,
ExtractValue(xml, '//laBalise/text()') AS valeur_de_la_balise_syntaxe_longue, -- même chose que "//laBalise", identique à "//laBalise/text()"
ExtractValue(xml, '//laBalise/@lAttribut') AS valeur_de_l_attribut_de_balise_syntaxe_courte,
ExtractValue(xml, '//laBalise/attribute::lAttribut') AS valeur_de_l_attribut_de_balise_syntaxe_longue, -- même chose que "//laBalise/@lAttribut", récupère la valeur de l'attribut "lAttribut"
ExtractValue(xml, 'count(//laBalise/*)'),  -- compte le nombre de fils sur le noeud "//laBalise"
FROM maTable

The :: operator is not supported in combination with node types such as the following:

   axis::comment()
   axis::text()
   axis::processing-instructions()
   axis::node()

However, name tests (such as axis::name and axis::*) are supported, as shown in these examples:

The following XPath functions are not supported, or have known issues as indicated:    
   Prior to MySQL 5.1.24, the boolean() function did not produce correct results for some string and nodeset values, including the NULL string (see Bug #26051). 
   id()
   lang()
   Prior to MySQL 5.1.8, the last() function was not supported (see Bug #16318).
   local-name()
   name() Bug #30690 Contrib: Missing name function in XPATH Implements XPATH Name() function lib_mysqludf_xql uses the xmlwriter library of libxml2, which is a superfast non-cached, forward-only generator of XML data. 
   namespace-uri()
   normalize-space()
   starts-with()
   string()
   substring-after()
   substring-before()
   translate()
The following axes are not supported:
   following-sibling
   following
   preceding-sibling
   preceding

Attribuer un rang (numéro) aux lignes d'un résultat en MySQL

SELECT (
	SELECT COUNT(id) FROM table t2
	WHERE t2.titre <= t1.titre 
	) AS rang , t1.*
FROM table t1
ORDER BY t1.titre
; -- bien que je doute des performances sur les grosses tables...

Sélectionner la première ligne d'un groupe

/*
+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+
*/

select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

/*
+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| cherry | bing     |  2.55 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
+--------+----------+-------+
*/

Dédoublonner (avec SOUNDEX)

-- Sélectionne tous les noms individuels 'nom_de_l_entreprise' qui ont
-- un soundex_code qui apparait plus d'une fois
SELECT
  `id`,
  `nom_de_l_entreprise`,
  SOUNDEX(`nom_de_l_entreprise`) AS soundex_code
FROM `table_des_entreprises_noms`
WHERE SOUNDEX(`nom_de_l_entreprise`) IN (
  -- Subquery: selectionne tous les soundex_codes apparaissants plus d'une fois
  SELECT SOUNDEX(`nom_de_l_entreprise`) AS soundex_code
  FROM `table_des_entreprises_noms`
  WHERE 1 = 1
    -- Vos critères specifiques pour définir ce que vous recherchez
  GROUP BY soundex_code
  HAVING COUNT(*) > 1
)
ORDER BY
  soundex_code,
  `nom_de_l_entreprise`
;

Calculer le temps que la requête MySQL en millisecondes (ms) - Benchmark

La recherche "mysql client show query time milliseconds", retourne :

Pofilage MySQL (infos, et temps de requêtes en ms)

SET profiling = 1;
-- EXPLAIN SELECT ...

mysql> SELECT COUNT(*) FROM myTable;
+----------+
| COUNT(*) |
+----------+
|   361523 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000066 |
| checking query cache for query | 0.000011 |
| checking privileges on cached  | 0.000005 |
| sending cached result to clien | 0.000011 |
| logging slow query             | 0.000005 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM myTable WHERE etat > 2;
+----------+
| COUNT(*) |
+----------+
|   358458 |
+----------+
1 row in set (0.20 sec)

mysql> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000074 |
| checking query cache for query | 0.000073 |
| Opening tables                 | 0.000030 |
| System lock                    | 0.000006 |
| Table lock                     | 0.000028 |
| init                           | 0.000029 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000070 |
| preparing                      | 0.000015 |
| executing                      | 0.000007 |
| Sending data                   | 0.193767 |
| end                            | 0.000030 |
| query end                      | 0.000006 |
| freeing items                  | 0.000377 |
| storing result in query cache  | 0.000011 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+
17 rows in set (0.00 sec)

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00010200 | SELECT COUNT(*) FROM myTable                |
|        2 | 0.19454400 | SELECT COUNT(*) FROM myTable WHERE etat > 2 |
+----------+------------+----------------------------------------+
2 rows in set (0.00 sec)


mysql> SET profiling = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM myTable WHERE etat > 4;
+----------+
| COUNT(*) |
+----------+
|    15820 |
+----------+
1 row in set (0.01 sec)

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00010200 | SELECT COUNT(*) FROM myTable                |
|        2 | 0.19454400 | SELECT COUNT(*) FROM myTable WHERE etat > 2 |
+----------+------------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000066 |
| checking query cache for query | 0.000011 |
| checking privileges on cached  | 0.000005 |
| sending cached result to clien | 0.000011 |
| logging slow query             | 0.000005 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
6 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000074 |
| checking query cache for query | 0.000073 |
| Opening tables                 | 0.000030 |
| System lock                    | 0.000006 |
| Table lock                     | 0.000028 |
| init                           | 0.000029 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000070 |
| preparing                      | 0.000015 |
| executing                      | 0.000007 |
| Sending data                   | 0.193767 |
| end                            | 0.000030 |
| query end                      | 0.000006 |
| freeing items                  | 0.000377 |
| storing result in query cache  | 0.000011 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+
17 rows in set (0.01 sec)

MySQL : connaître le détail et total en Mo et Go de la taille d'une base de données (de chacun des nom de schéma : SCHEMA_NAME)

SET information_schema_stats_expiry = 0; -- pour ne pas utiliser le cache MySQL. The default cache for schema stats is 86400s (1 day) so it's likely that you are just seeing cached information here. It's a session variable though, so you can just set it to zero for any situation where you want up-to-date information.
SELECT TABLE_SCHEMA, 
ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024,4) AS "Size (MB)",
ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/(1024*1024*1024),2) AS "Size (GB)"
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
WITH ROLLUP
LIMIT 9999;

MySQL : connaître le détail et total en Mo et Go de la taille des tables les plus volumineuses (données et index) d'une base de données pour un schéma

SET information_schema_stats_expiry = 0; -- pour ne pas utiliser le cache MySQL. The default cache for schema stats is 86400s (1 day) so it's likely that you are just seeing cached information here. It's a session variable though, so you can just set it to zero for any situation where you want up-to-date information.
SELECT TABLE_NAME, 
ROUND((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024 * 1024), 2) AS tailleDataIndexGo,
ROUND((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024), 2) AS tailleDataIndexMo,
ROUND((DATA_LENGTH) / (1024 * 1024), 2) AS tailleDataMo,
ROUND((INDEX_LENGTH) / (1024 * 1024), 2) AS tailleIndexMo
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mon_schema_base_de_donnees'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

Dupliquer (copier) une base de données MySQL vers une autre autre base

mysqladmin create DB_name -u DB_user --password=DB_pass
mysqldump -u DB_user --password=DB_pass DB_name | mysql -u DB_user --password=DB_pass -h DB_host DB_name

Correspondance entre MySQL et Oracle

  • asp-php.net : SQL : Equivalences entre Oracle et MySQL
  • sqlpro.developpez.com Le comparatif fait une synthèse des fonctions de la norme SQL (1992 à 2008) entre Norme SQL , Paradox , Access , MySQL , PostGreSQL , SQL Server , Oracle , Interbase

Différences de tables et de de row

Ressources

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Fil des commentaires de ce billet