Fonctions cachées de MySQL

Question

J'ai travaillé avec Microsoft SQL Server depuis de nombreuses années mais je n'ai commencé que récemment à utiliser MySQL avec mes applications web, et je suis avide de connaissances.

Pour poursuivre la longue lignée des Questions sur les "fonctions cachées". J'aimerais connaître toutes les fonctions cachées ou pratiques de MySQL, ce qui me permettrait d'améliorer mes connaissances de cette base de données open source.

Answer 1

J'aime on duplicate key (AKA upsert, merge) pour toutes sortes de compteurs créés paresseusement :

insert into occurances(word,count) values('foo',1),('bar',1) 
  on duplicate key cnt=cnt+1

Vous pouvez insérer de nombreuses lignes en une seule requête, et gérer immédiatement un index dupliqué pour chacune des lignes.

Answer 2

Encore une fois, ce ne sont pas vraiment des fonctions cachées, mais elles sont très pratiques :

Fonctionnalité

Saisissez facilement le DDL :

SHOW CREATE TABLE CountryLanguage

sortie :

CountryLanguage | CREATE TABLE countrylanguage (
  CountryCode char(3) NOT NULL DEFAULT '',
  Language char(30) NOT NULL DEFAULT '',
  IsOfficial enum('T','F') NOT NULL DEFAULT 'F',
  Percentage float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (CountryCode,Language)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Caractéristique : Fonction agrégée GROUP_CONCAT() Crée une chaîne concaténée de ses arguments par détail, et agrège en concaténant ceux par groupe.

Exemple 1 : simple

SELECT   CountryCode
,        GROUP_CONCAT(Language) AS List
FROM     CountryLanguage
GROUP BY CountryCode             

Sortie :

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | Dutch,English,Papiamento,Spanish   |
. ...         . ...                                .
| ZWE         | English,Ndebele,Nyanja,Shona       |
+-------------+------------------------------------+

Exemple 2 : arguments multiples

SELECT   CountryCode
,        GROUP_CONCAT(
             Language
,            IF(IsOfficial='T', ' (Official)', '')
         )               AS List
FROM     CountryLanguage
GROUP BY CountryCode

Sortie :

+-------------+---------------------------------------------+
| CountryCode | List                                        |
+-------------+---------------------------------------------+
| ABW         | Dutch (Official),English,Papiamento,Spanish |
. ...         . ...                                         .
| ZWE         | English (Official),Ndebele,Nyanja,Shona     |
+-------------+---------------------------------------------+

Exemple 3 : Utilisation d'un séparateur personnalisé

SELECT   CountryCode
,        GROUP_CONCAT(Language SEPARATOR ' and ') AS List
FROM     CountryLanguage
GROUP BY CountryCode

Sortie :

+-------------+----------------------------------------------+
| CountryCode | List                                         |
+-------------+----------------------------------------------+
| ABW         | Dutch and English and Papiamento and Spanish |
. ...         . ...                                          .
| ZWE         | English and Ndebele and Nyanja and Shona     |
+-------------+----------------------------------------------+

Exemple 4 : Contrôle de l'ordre des éléments de la liste

SELECT   CountryCode
,        GROUP_CONCAT(
         Language
         ORDER BY CASE IsOfficial WHEN 'T' THEN 1 ELSE 2 END DESC
         ,        Language
         )               AS List
FROM     CountryLanguage
GROUP BY CountryCode

Sortie :

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | English,Papiamento,Spanish,Dutch,  |
. ...         . ...                                .
| ZWE         | Ndebele,Nyanja,Shona,English       |
+-------------+------------------------------------+

Caractéristique : COUNT(DISTINCT ) avec plusieurs expressions

Vous pouvez utiliser plusieurs expressions dans une expression COUNT(DISTINCT ...) pour compter le nombre de combinaisons.

SELECT COUNT(DISTINCT CountryCode, Language) FROM CountryLanguage

Fonctionnalité / Gotcha : Inutile d'inclure les expressions non agrégées dans la liste GROUP BY

La plupart des SGBDR appliquent un GROUP BY conforme à SQL92 qui exige que toutes les expressions non agrégées de la liste SELECT apparaissent dans le GROUP BY. Dans ces SGBDR, cette instruction :

SELECT     Country.Code, Country.Continent, COUNT(CountryLanguage.Language)
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

n'est pas valide, car la liste SELECT contient la colonne non agrégée Pays.Continent qui n'apparaît pas dans la liste GROUP BY. Dans ces SGBDR, vous devez soit modifier la liste GROUP BY pour qu'elle se lise comme suit

GROUP BY   Country.Code, Country.Continent

ou vous devez ajouter un agrégat insensé à Country.Continent, par exemple

SELECT     Country.Code, MAX(Country.Continent), COUNT(CountryLanguage.Language)

Maintenant, le problème est que, logiquement, il n'y a rien qui exige que Country.Continent soit agrégé. Vous voyez, Country.Code est la clé primaire de la table Country. Country.Continent est également une colonne de la table Country et est donc, par définition, fonctionnellement dépendant de la clé primaire Country.Code. Ainsi, il doit exister exactement une valeur dans Country.Continent pour chaque Country.Code distinct. Si vous vous rendez compte de cela, alors vous vous rendez compte qu'il n'y a pas de sens à l'agréger (il n'y a qu'une seule valeur, n'est-ce pas) ni à la grouper par elle (car cela ne rendra pas le résultat plus unique puisque vous groupez déjà par la clé primaire).

Quoi qu'il en soit, MySQL vous permet d'inclure des colonnes non agrégées dans la liste SELECT sans vous obliger à les ajouter également à la clause GROUP BY.

Le problème est que MySQL ne vous protège pas si vous utilisez une colonne non agrégée. Donc, une requête comme celle-ci :

SELECT     Country.Code, COUNT(CountryLanguage.Language), CountryLanguage.Percentage
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

sera exécuté sans plainte, mais la colonne CountryLanguage.Percentage contiendra des non-sens (c'est-à-dire que parmi tous les pourcentages de langues, une des valeurs disponibles pour le pourcentage sera choisie au hasard ou du moins hors de votre contrôle.

Voir : Démystifier les mythes du groupe par

Answer 3

La commande "pager" dans le client

Si vous avez, disons, 10 000 lignes dans votre résultat et que vous voulez les visualiser (Ceci suppose que les commandes "less" et "tee" sont disponibles, ce qui est normalement le cas sous Linux ; sous Windows YMMV).

pager less
select lots_of_stuff FROM tbl WHERE clause_which_matches_10k_rows;

Et vous les obtiendrez dans le visualiseur de fichiers "less", ce qui vous permettra de les feuilleter joliment, de les rechercher, etc.

Aussi

pager tee myfile.txt
select a_few_things FROM tbl WHERE i_want_to_save_output_to_a_file;

Ecrit commodément dans un fichier.

Answer 4

Certaines choses peuvent vous intéresser :

<query>\G -- \G in the CLI instead of the ; will show one column per row
explain <query>; -- this will show the execution plan for the query

Answer 5

Ce n'est pas une fonction cachée, mais elle est néanmoins utile : http://mtop.sourceforge.net/