Caractéristiques cachées de MySQL

Je travaille avec Microsoft SQL Server depuis de nombreuses années, mais je viens tout juste de commencer à utiliser MySQL avec mes applications Web et j’ai très envie de connaissances.

Pour continuer avec la longue liste de questions sur les “fonctionnalités cachées” , j’aimerais connaître les fonctionnalités cachées ou pratiques de MySQL qui, espérons-le, amélioreront ma connaissance de cette firebase database open source.

Depuis que tu mets une prime, je partagerai mes secrets durement gagnés …

En général, tous les fichiers SQL que je syntonisais aujourd’hui nécessitaient l’utilisation de sous-requêtes. Venant du monde des bases de données Oracle, les choses que je prenais pour acquises ne fonctionnaient pas de la même manière avec MySQL. Et ma lecture sur l’optimisation de MySQL me permet de conclure que MySQL est derrière Oracle en termes d’optimisation des requêtes.

Alors que les requêtes simples requirejses pour la plupart des applications B2C peuvent bien fonctionner pour MySQL, la plupart des requêtes de rapports agrégées nécessaires à Intelligence Reporting semblent nécessiter une planification et une réorganisation des requêtes SQL pour guider MySQL à les exécuter plus rapidement.

Administration:

max_connections est le nombre de connexions simultanées. La valeur par défaut est 100 connexions (151 depuis la version 5.0) – très petite.

Remarque:

les connexions prennent de la mémoire et votre système d’exploitation peut ne pas être capable de gérer beaucoup de connexions.

Les binarys MySQL pour Linux / x86 vous permettent d’avoir jusqu’à 4096 connexions simultanées, mais les binarys auto-compilés ont souvent moins de limites.

Définissez table_cache pour qu’il corresponde au nombre de vos tables ouvertes et connexions simultanées. Surveillez la valeur de open_tables et si elle augmente rapidement, vous devrez augmenter sa taille.

Remarque:

Les 2 parameters précédents peuvent nécessiter beaucoup de fichiers ouverts. 20 + max_connections + table_cache * 2 est une bonne estimation de ce dont vous avez besoin. MySQL sous Linux a une option open_file_limit, définissez cette limite.

Si vous avez des requêtes complexes, il est probable que sort_buffer_size et tmp_table_size soient très importants. Les valeurs dépendent de la complexité de la requête et des ressources disponibles, mais 4 Mo et 32 ​​Mo, respectivement, sont des points de départ recommandés.

Remarque: Il s’agit de valeurs “par connexion”, parmi read_buffer_size, read_rnd_buffer_size et d’autres, ce qui signifie que cette valeur peut être nécessaire pour chaque connexion. Alors, considérez votre charge et la ressource disponible lors de la définition de ces parameters. Par exemple, sort_buffer_size n’est alloué que si MySQL doit faire un sorting. Remarque: veillez à ne pas manquer de mémoire.

Si vous avez établi plusieurs connexions (par exemple, un site Web sans connexions persistantes), vous pouvez améliorer les performances en définissant thread_cache_size sur une valeur différente de zéro. 16 est une bonne valeur pour commencer. Augmentez la valeur jusqu’à ce que vos threads_created ne se développent pas très rapidement.

CLÉ PRIMAIRE:

Il ne peut y avoir qu’une seule colonne AUTO_INCREMENT par table, il doit être indexé et il ne peut pas avoir de valeur DEFAULT

KEY est normalement synonyme d’INDEX. L’atsortingbut de clé PRIMARY KEY peut également être spécifié en tant que clé uniquement lorsqu’il est donné dans une définition de colonne. Cela a été mis en œuvre pour la compatibilité avec d’autres systèmes de firebase database.

Une clé primaire est un index unique dans lequel toutes les colonnes clés doivent être définies comme NOT NULL.

Si un index PRIMARY KEY ou UNIQUE se compose d’une seule colonne ayant un type entier, vous pouvez également faire référence à la colonne en tant que “_rowid” dans les instructions SELECT.

Dans MySQL, le nom d’une clé primaire est PRIMARY

Actuellement, seules les tables InnoDB (v5.1?) Prennent en charge les clés étrangères.

Généralement, vous créez tous les index dont vous avez besoin lors de la création de tables. Toute colonne déclarée comme PRIMARY KEY, KEY, UNIQUE ou INDEX sera indexée.

NULL signifie “ne pas avoir de valeur”. Pour tester la valeur NULL, vous ne pouvez pas utiliser les opérateurs de comparaison arithmétique tels que =, . Utilisez plutôt les opérateurs IS NULL et IS NOT NULL:

NO_AUTO_VALUE_ON_ZERO supprime l’incrémentation automatique pour 0 afin que seul NULL génère le numéro de séquence suivant. Ce mode peut être utile si 0 a été stocké dans la colonne AUTO_INCREMENT d’une table. (Enregistrer 0 n’est pas une pratique recommandée, en passant.)

Pour modifier la valeur du compteur AUTO_INCREMENT à utiliser pour les nouvelles lignes:

 ALTER TABLE mytable AUTO_INCREMENT = value; 

ou SET INSERT_ID = valeur;

Sauf indication contraire, la valeur commencera par: 1000000 ou spécifiez-la ainsi:

…) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1

TIMESTAMPS:

Les valeurs des colonnes TIMESTAMP sont converties du fuseau horaire actuel à l’heure UTC pour le stockage et de l’heure UTC au fuseau horaire actuel pour l’extraction.

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Pour une colonne TIMESTAMP dans un tableau, vous pouvez affecter l’horodatage actuel comme valeur par défaut et la valeur de mise à jour automatique.

Une chose à surveiller lors de l’utilisation de l’un de ces types dans une clause WHERE, il est préférable de faire WHERE datecolumn = FROM_UNIXTIME (1057941242) et non WHERE UNIX_TIMESTAMP (datecolumn) = 1057941242. cette dernière ne profitera pas d’un index sur cette colonne.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

  UNIX_TIMESTAMP() FROM_UNIXTIME() UTC_DATE() UTC_TIME() UTC_TIMESTAMP() 

si vous convertissez un datetime en timestamp unix dans MySQL:
Et puis ajoutez 24 heures à cela:
Et puis reconvertissez-le en une date-heure qui perd une heure par magie!

Voici ce qui se passe Lors de la conversion de l’horodatage unix en date-heure, le fuseau horaire est pris en compte et il se trouve que du 28 au 29 octobre 2006, nous avons quitté l’heure d’été et perdu une heure.

Depuis MySQL 4.1.3, les fonctions CURRENT_TIMESTAMP (), CURRENT_TIME (), CURRENT_DATE () et FROM_UNIXTIME () renvoient des valeurs dans le fuseau horaire actuel de la connexion, qui est disponible sous la forme de la variable système time_zone. De plus, UNIX_TIMESTAMP () suppose que son argument est une valeur datetime dans le fuseau horaire actuel.

Le paramètre de fuseau horaire actuel n’affecte pas les valeurs affichées par les fonctions telles que UTC_TIMESTAMP () ou les valeurs des colonnes DATE, TIME ou DATETIME.

REMARQUE: ON UPDATE uniquement met à jour le DateTime si un champ est modifié Si un UPDATE ne modifie aucun champ, le DateTime n’est PAS mis à jour!

De plus, le premier TIMESTAMP est toujours AUTOUPDATE par défaut même s’il n’est pas spécifié

Lorsque je travaille avec des dates, je me rends presque toujours à Julian Date parce que les maths de données consistent simplement à append ou à soustraire des entiers et des secondes depuis minuit pour la même raison. Il est rare que j’ai besoin d’une résolution de temps d’une granularité plus fine que les secondes.

Ces deux éléments peuvent être stockés sous la forme d’un entier de 4 octets, et si l’espace est vraiment restreint, ils peuvent être combinés en temps UNIX (secondes depuis le 1/1/1970) sous la forme d’un entier non signé.

‘sec en 24 heures = 86400

‘Entier Max value = 2 147 483 647 – peut contenir 68 ans de secondes

‘Entier non signé, valeur maximale = 4 294 967 295 – peut contenir 136 années de secondes

Protocole binary:

MySQL 4.1 a introduit un protocole binary permettant d’envoyer et de renvoyer des valeurs de données non-ssortingng au format natif sans conversion vers et depuis le format de chaîne. (Très utile)

Mis à part mysql_real_query () est plus rapide que mysql_query () car il n’appelle pas strlen () pour opérer sur la chaîne d’instruction.

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html Le protocole binary prend en charge les instructions préparées côté serveur et permet la transmission de valeurs de données au format natif. Le protocole binary a subi de nombreuses révisions lors des versions précédentes de MySQL 4.1.

Vous pouvez utiliser la macro IS_NUM () pour tester si un champ a un type numérique. Transmettez la valeur de type à IS_NUM () et la valeur est TRUE si le champ est numérique:

Une chose à noter est que les données binarys PEUVENT être envoyées dans une requête régulière si vous y échappez et souvenez-vous que MySQL exige seulement que cette barre oblique inverse et le caractère de citation soient échappés. C’est donc un moyen très simple d’INSERER des chaînes binarys plus courtes, comme des mots de passe cryptés / salés par exemple.

Maître serveur:

http://www.experts-exchange.com/Database/MySQL/Q_22967482.html

http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2

ESCLAVE DE REPLICATION DE SUBVENTION to slave_user IDENTIFIED BY ‘slave_password’

 #Master Binary Logging Config STATEMENT causes replication to be statement-based - default log-bin=Mike binlog-format=STATEMENT server-id=1 max_binlog_size = 10M expire_logs_days = 120 #Slave Config master-host=master-hostname master-user=slave-user master-password=slave-password server-id=2 

Le fichier journal binary doit lire:

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/

http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

Vous pouvez supprimer tous les fichiers journaux binarys avec l’instruction RESET MASTER ou un sous-ensemble de ceux-ci avec PURGE MASTER.

–result-file = binlog.txt TrustedFriend-bin.000030

Normalisation:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Fonctions UDF

http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx

http://souptonuts.sourceforge.net/readme_mysql.htm

Types de données:

http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2

http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/

Une chose à noter est que sur une table mixte avec à la fois CHAR et VARCHAR, mySQL changera les CHAR en VARCHAR

RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (RecNum)

MySQL représente toujours les dates avec l’année en premier, conformément aux spécifications standard SQL et ISO 8601

Divers:

Désactiver certaines fonctionnalités de MySQl entraînera des fichiers de données plus petits et un access plus rapide. Par exemple:

–datadir spécifie le répertoire de données et

–skip-innodb désactivera l’option inno et vous fera économiser 10-20M

Plus ici http://dev.mysql.com/tech-resources/articles/mysql-c-api.html

Téléchargez le chapitre 7 – Gratuit

InnoDB est transactionnel mais il y a une surcharge de performance qui va avec. J’ai trouvé que les tables MyISAM étaient suffisantes pour 90% de mes projets. Les tables non sécurisées sur les transactions (MyISAM) ont plusieurs avantages, qui sont tous dus au fait que:

il n’y a pas de frais de transaction:

Plus vite

Baisse de l’espace disque requirejs

Moins de mémoire requirejse pour effectuer des mises à jour

Chaque table MyISAM est stockée sur disque dans trois fichiers. Les fichiers ont des noms qui commencent par le nom de la table et ont une extension pour indiquer le type de fichier. Un fichier .frm stocke le format de la table. Le fichier de données a une extension .MYD (MYData). Le fichier d’index a une extension .MYI (MYIndex).

Ces fichiers peuvent être copiés dans un emplacement de stockage intact sans utiliser la fonctionnalité de sauvegarde des administrateurs MySQL qui prend du temps (tout comme la restauration)

L’astuce consiste à faire une copie de ces fichiers puis à DROP la table. Lorsque vous remettez les fichiers, MySQl les reconnaît et met à jour le suivi des tables.

Si vous devez sauvegarder / restaurer,

La restauration d’une sauvegarde ou son importation à partir d’un fichier de sauvegarde existant peut prendre un certain temps, en fonction du nombre d’index et de clés primaires que vous avez sur chaque table. Vous pouvez accélérer considérablement ce processus en modifiant votre fichier de vidage d’origine en l’entourant des éléments suivants:

 SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; .. your dump file .. SET FOREIGN_KEY_CHECKS = 1; COMMIT; SET AUTOCOMMIT = 1; 

Pour augmenter considérablement la vitesse de rechargement, ajoutez la commande SQL SET AUTOCOMMIT = 0; au début du fichier de vidage, et ajoutez le COMMIT; commande à la fin.

Par défaut, autocommit est activé, ce qui signifie que chaque commande insert du fichier de vidage sera traitée comme une transaction distincte et écrite sur le disque avant que la suivante ne soit lancée. Si vous n’ajoutez pas ces commandes, recharger une grande firebase database dans InnoDB peut prendre plusieurs heures …

La taille maximale d’une ligne dans une table MySQL est de 65 535 octets

La longueur maximale effective d’un VARCHAR dans MySQL 5.0.3 et sur = taille maximale de la ligne (65 535 octets)

Les valeurs VARCHAR ne sont pas remplies lorsqu’elles sont stockées. Les espaces de fin sont conservés lorsque les valeurs sont stockées et récupérées, conformément à la norme SQL.

Les valeurs CHAR et VARCHAR de MySQL sont comparées sans tenir compte des espaces de fin.

L’utilisation de CHAR accélère votre access uniquement si la taille de l’enregistrement est fixe. C’est-à-dire que si vous utilisez un object de taille variable, vous pourriez tout faire en taille variable. Vous n’obtenez aucune vitesse en utilisant un CHAR dans une table qui contient également un VARCHAR.

La limite VARCHAR de 255 caractères a été scope à 65535 caractères à partir de MySQL 5.0.3

Les recherches en texte intégral sont sockets en charge pour les tables MyISAM uniquement.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Les colonnes BLOB n’ont pas de jeu de caractères et le sorting et la comparaison sont basés sur les valeurs numériques des octets dans les valeurs de colonne.

Si le mode SQL ssortingct n’est pas activé et que vous affectez une valeur à une colonne BLOB ou TEXT qui dépasse la longueur maximale de la colonne, la valeur est tronquée et un avertissement est généré.

Commandes utiles:

vérifier le mode ssortingct: SELECT @@ global.sql_mode;

désactiver le mode ssortingct:

SET @@ global.sql_mode = ”;

SET @@ global.sql_mode = ‘MYSQL40’

ou supprimer: sql-mode = “STRICT_TRANS_TABLES, …

MONTREZ LES COLONNES DE mytable

SELECT max (namecount) AS virtualcolumn DE mytable ORDER BY virtualcolumn

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id ()

vous obtient la PK de la dernière ligne insérée dans le thread en cours max (pkcolname) vous obtient la dernière PK globale.

Note: si la table est vide, max (pkcolname) renvoie 1 mysql_insert_id () convertit le type de retour de la fonction MySQL C native mysql_insert_id () en un type long (nommé int en PHP).

Si votre colonne AUTO_INCREMENT a un type de colonne BIGINT, la valeur renvoyée par mysql_insert_id () sera incorrecte. Au lieu de cela, utilisez la fonction SQL interne MySQL LAST_INSERT_ID () dans une requête SQL.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Juste une note que lorsque vous essayez d’insérer des données dans une table et que vous obtenez l’erreur:

 Unknown column 'the first bit of data what you want to put into the table' in 'field list' 

en utilisant quelque chose comme

 INSERT INTO table (this, that) VALUES ($this, $that) 

C’est parce que vous n’avez pas d’apostrophes autour des valeurs que vous essayez de coller à la table. Donc, vous devriez changer votre code pour:

 INSERT INTO table (this, that) VALUES ('$this', '$that') 

rappel que “ sont utilisés pour définir les champs, bases de données ou tables MySQL, pas les valeurs;)

Connexion perdue au serveur pendant la requête:

http://dev.mysql.com/doc/refman/5.1/fr/gone-away.html

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

http://dev.mysql.com/doc/refman/5.1/en/show-variables.html

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

http://dev.mysql.com/doc/refman/5.1/en/error-log.html

Réglage des requêtes

http://www.artfulsoftware.com/infotree/queries.php?&bw=1313

Eh bien cela devrait suffire pour gagner le bonus que je penserais … Les fruits de nombreuses heures et de nombreux projets avec une grande firebase database gratuite . Je développe des serveurs de données d’application sur des plates-formes Windows principalement avec MySQL. Le pire désordre que je devais résoudre était

Le cauchemar ultime de la firebase database MySQL

Cela nécessitait une série d’applications pour traiter les tables en quelque chose d’utile en utilisant un grand nombre des astuces mentionnées ici.

Si vous trouvez cela incroyablement utile, exprimez vos remerciements en votant.

Consultez également mes autres articles et livres blancs sur: http://www.coastrd.com

Une des caractéristiques moins cachées de MySQL est qu’il n’est pas vraiment bon d’être compatible avec SQL, mais pas vraiment les bogues, mais plus de pièges … 🙂

Une commande pour savoir quelles tables sont actuellement dans le cache:

 mysql> SHOW open TABLES FROM test; +----------+-------+--------+-------------+ | DATABASE | TABLE | In_use | Name_locked | +----------+-------+--------+-------------+ | test | a | 3 | 0 | +----------+-------+--------+-------------+ 1 row IN SET (0.00 sec) 

(Du blog de performance MySQL )

Une commande pour savoir qui fait quoi:

 mysql> show processlist; show processlist; +----+-------------+-----------------+------+---------+------+----------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------------+------+---------+------+----------------------------------+------------------+ | 1 | root | localhost:32893 | NULL | Sleep | 0 | | NULL | | 5 | system user | | NULL | Connect | 98 | Waiting for master to send event | NULL | | 6 | system user | | NULL | Connect | 5018 | Reading event from the relay log | NULL | +-----+------+-----------+---------+---------+-------+-------+------------------+ 3 rows in set (0.00 sec) 

Et vous pouvez tuer un processus avec:

 mysql>kill 5 

J’aime particulièrement le support inet_ntoa() de inet_ntoa() pour inet_ntoa() et inet_aton() . Cela facilite la gestion des adresses IP dans les tables (du moins tant qu’elles ne sont que des adresses IPv4!)

J’adore on duplicate key (AKA upsert, merge) pour tous les types 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 plusieurs lignes dans une requête et gérer immédiatement l’index dupliqué pour chacune des lignes.

Encore une fois – pas vraiment de fonctionnalités cachées, mais vraiment pratique:

Fonctionnalité

Saisissez facilement 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 

Fonction: Fonction d’agrégat GROUP_CONCAT () Crée une chaîne concaténée de ses arguments par détail et les agrège en les concaténant 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 | +-------------+------------------------------------+ 

Fonctionnalité: 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

Feature / Gotcha: pas besoin d’inclure des expressions non agrégées dans la liste GROUP BY

La plupart des SGBDR-es appliquent un GROUP BY compatible SQL92, ce qui nécessite que toutes les expressions non agrégées de la liste SELECT apparaissent dans GROUP BY. Dans ces SGBDR-es, cette déclaration:

 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 Country.Continent qui n’apparaît pas dans la liste GROUP BY. Dans ces SGBDR-es, vous devez soit modifier la liste GROUP BY à lire

 GROUP BY Country.Code, Country.Continent 

ou vous devez append un agrégat non-sens à Country.Continent, par exemple

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

Maintenant, le fait est que logiquement, il n’y a rien qui exige que Country.Continent soit agrégé. Voir, 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épendante de la clé primaire Country.Code. Donc, il doit exister exactement une valeur dans Country.Continent pour chaque Country.Code distinct. Si vous vous en rendez compte, vous réalisez qu’il n’a pas de sens de l’agréger (il n’ya qu’une seule valeur, à droite) ni de le regrouper (car cela ne rendra pas le résultat plus unique puisque vous le regroupez déjà par le pk)

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

Le problème avec ceci 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 se plaindre, mais la colonne CountryLanguage.Percentage contiendra des non-sens (c’est-à-dire de tous les pourcentages de langues, une des valeurs disponibles pour le pourcentage sera choisie au hasard ou au moins hors de votre contrôle.

Voir: Groupe de déboulonnage par mythes

La commande “pager” du client

Si vous avez, par exemple, 10 000 lignes dans votre résultat et que vous souhaitez les afficher (cela suppose que les commandes “less” et “tee” soient 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 la visionneuse de fichiers “moins” afin que vous puissiez les parcourir, rechercher, etc.

Aussi

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

Facilement écrire dans un fichier.

Certaines choses peuvent vous intéresser:

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

Pas une fonctionnalité cachée, mais néanmoins utile: http://mtop.sourceforge.net/

Voici quelques-uns de mes conseils – J’ai blogué à leur sujet dans mon blog ( Link )

  1. Vous n’avez pas besoin d’utiliser le signe ‘@’ pour déclarer des variables.
  2. Vous devez utiliser un délimiteur (la valeur par défaut est ‘;’) pour délimiter la fin d’une déclaration – Lien
  3. Si vous essayez de déplacer des données entre MS-SQL 2005 et mySQL, il existe quelques obstacles à franchir – Lien
  4. Faire des correspondances sensibles à la casse dans mySQL – lien

Si vous voulez travailler avec des bases de données InnoDb de grandes et / ou de grandes transactions, apprenez et comprenez le blog de performance Mysql “SHOW INNODB STATUS”, cela deviendra votre ami.

Si vous utilisez cmdline Mysq, vous pouvez interagir avec la ligne de commande (sur les machines Linux – ne savez pas s’il existe un effet équivalent sous Windows) en utilisant le point d’interrogation / exclamation. Par exemple:

 \! cat file1.sql 

affichera le code pour fichier1.sql. Pour enregistrer votre relevé et votre requête dans un fichier, utilisez la fonction tee

 \T filename 

pour désactiver cette utilisation \ t

Enfin, pour exécuter un script que vous avez déjà enregistré, utilisez “source filename”. Bien sûr, l’alternative normale est de diriger dans le nom du script lors du démarrage de mysql à partir de la ligne de commande:

  mysql -u root -p < case1.sql 

J'espère que c'est utile à quelqu'un!

Edit: Je viens de me rappeler un autre - en appelant mysql depuis la ligne de commande, vous pouvez utiliser le commutateur -t pour que la sortie soit au format table - une vraie aubaine avec certaines requêtes (bien sûr, terminer des requêtes avec \ G comme mentionné ici est aussi utile à cet égard). Beaucoup plus sur les différents commutateurs Outil de ligne de commande

Vous venez de trouver un bon moyen de changer l’ordre d’un sorting (utilisez normalement Case ...) Si vous voulez changer l’ordre d’un sorting (peut-être sortinger par 1, 4, 3, 2 au lieu de 1, 2, 3, 4) vous pouvez utiliser la fonction de champ dans la clause Order by. Par exemple

Ordre par champ (sort_field, 1,4,3,2)

  • Trouvé ceci par Ordre par day_of_week en MySQL courtesey de l'utilisateur gms8994

Je ne pense pas que ce soit spécifique à MySQL, mais pour moi:

Au lieu d’écrire

 WHERE (x.id > y.id) OR (x.id = y.id AND x.f2 > y.f2) 

Vous pouvez simplement écrire

 WHERE (x.id, x.f2) > (y.id, y.f2) 

mysqlsla – L’un des outils d’parsing des journaux de requêtes très couramment utilisés. Vous pouvez voir les 10 requêtes les plus problématiques depuis le dernier déploiement des journaux de requêtes lents. Il peut également vous indiquer le nombre de fois où la requête BAD a été déclenchée et le temps total nécessaire pour le serveur.

En réalité documenté , mais très ennuyeux: conversions automatiques pour des dates incorrectes et autres saisies incorrectes.

Avant MySQL 5.0.2, MySQL pardonne les valeurs de données illégales ou incorrectes et les contraint à des valeurs légales pour la saisie de données. En MySQL 5.0.2 et versions ultérieures, cela rest le comportement par défaut, mais vous pouvez changer le mode SQL du serveur pour sélectionner un traitement plus traditionnel des mauvaises valeurs afin que le serveur les rejette et abandonne l’instruction dans laquelle elles se produisent.

En ce qui concerne les dates: parfois, vous serez “chanceux” quand MySQL n’ajuste pas l’entrée aux dates valides à proximité, mais la stocke au lieu de 0000-00-00 qui, par définition, n’est pas valide. Cependant, même dans ce cas, vous souhaitiez peut-être que MySQL échoue plutôt que de stocker cette valeur en silence.

Le profileur SQL intégré.

InnoDB par défaut stocke toutes les tables d’un espace table global qui ne sera jamais réduit .

Vous pouvez utiliser innodb_file_per_table qui placera chaque table dans un espace table distinct qui sera supprimé lorsque vous déposerez la table ou la firebase database.

Planifiez à l’avance car vous devez vider et restaurer la firebase database pour récupérer de l’espace autrement.

Utilisation d’espaces de table par table

Si vous insérez dans la colonne datetime une valeur de chaîne vide “”, MySQL conservera la valeur 00/00/0000 00:00:00. Contrairement à Oracle, qui permettra d’économiser une valeur nulle.

Pendant mes tests avec de grands ensembles de données et des champs DATETIME, il est toujours plus lent de faire cette requête:

 SELECT * FROM mytable WHERE date(date_colum) BETWEEN '2011-01-01' AND ''2011-03-03'; 

Que cette approche:

 SELECT * FROM mytable WHERE date_column BETWEEN '2011-01-01 00:00:00' AND '2011-03-03 23:59:59'