Quand utiliser NULL dans les tables MySQL

Cependant, j’ai souvent lu des articles sur les problèmes de performance lorsque les champs sont nullables et il a été conseillé d’utiliser une chaîne vide dans les cas où NULL est correct.

Je vais être un peu difficile sur le choix des mots pour un moment:

• Même si c’était un facteur de performance significatif, cela ne rend pas sémantiquement correct d’utiliser une valeur au lieu de NULL. En SQL, NULL a un rôle sémantique, pour indiquer une valeur manquante ou inapplicable. Les caractéristiques de performance de NULL dans une implémentation de SGBDR donnée en sont indépendantes. Les performances peuvent varier d’une marque à l’autre ou d’une version à l’autre, mais l’objective de la valeur NULL dans la langue est cohérent.

En tout cas, je n’ai entendu aucune preuve que NULL fonctionne mal. Je serais intéressé par toute référence aux mesures de performance qui montrent que les colonnes nullables sont moins performantes que les colonnes non nullables.

Je ne dis pas que je ne me trompe pas ou que cela ne peut pas être vrai dans certains cas, mais simplement que cela n’a pas de sens de faire des suppositions inutiles. La science n’est pas constituée de conjectures; il faut montrer des preuves avec des mesures reproductibles.

Les mésortingques vous indiquent également dans quelle mesure la performance diffère, vous pouvez donc vous demander si cela vaut la peine de vous inquiéter. En d’autres termes, l’impact peut être mesurable et non nul, mais rest insignifiant par rapport aux facteurs de performance plus importants, tels que l’indexation correcte des tables ou le dimensionnement du cache de votre firebase database.

En MySQL, la recherche de NULL peut bénéficier d’un index:

mysql> CREATE TABLE foo ( i INT NOT NULL, j INT DEFAULT NULL, PRIMARY KEY (i), UNIQUE KEY j_index (j) ); mysql> INSERT INTO foo (i, j) VALUES (1, 1), (2, 2), (3, NULL), (4, NULL), (5, 5); mysql> EXPLAIN SELECT * FROM foo WHERE i = 3; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | foo | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ mysql> EXPLAIN SELECT * FROM foo WHERE j IS NULL; +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | foo | ref | j_index | j_index | 5 | const | 2 | Using where | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ 

Notez que ce n’est toujours pas une mesure de la performance. J’ai seulement montré que vous pouvez utiliser un index lors de la recherche de NULL. Je vais affirmer (certes sans avoir mesuré, mais bon c’est juste StackOverflow) que l’avantage d’un index éclipse toute pénalité possible lors de la recherche de NULL par rapport à une chaîne vide.

Ce n’est pas une décision de conception correcte de choisir zéro ou blanc ou toute autre valeur pour remplacer NULL. Vous devrez peut-être utiliser ces valeurs comme significatives dans la colonne. C’est pourquoi NULL existe, en tant que valeur qui est par définition en dehors du domaine des valeurs de tout type de données, vous pouvez donc utiliser toute la plage de valeurs des entiers ou des chaînes et avoir encore quelque chose qui signifie “aucune des valeurs ci-dessus.” ”

Le manuel MySQL contient en fait un article intéressant sur les problèmes liés à NULL.

J’espère que cela aide.

A également trouvé cet autre article SO sur NULL et Performance

Nous n’autorisons pas les valeurs NULL dans nos bases de données, sauf s’il s’agit de valeurs numériques ou de dates. La raison pour laquelle nous faisons cela est que les valeurs numériques ne doivent parfois pas être mises par défaut à zéro, car elles sont très, très mauvaises. Je suis développeur pour un courtier en bourse et il y a une grande différence entre NULL et 0 . L’utilisation de COALESCE est pratique si nous voulons rétablir les valeurs par défaut à zéro même si nous ne les stockons pas en tant que telles.

 MyVal = COALESCE(TheData, 0) 

Comme nous insérons en bloc des données à partir de fichiers plats, nous utilisons des fichiers de format pour déterminer l’entrée des données, ce qui convertit automatiquement les valeurs vides en chaînes vides.

Les dates par défaut à n’importe quelle valeur peuvent sembler dépendre de la collation je crois, mais les nôtres à quelque chose comme 1900, et encore une fois, les dates sont extrêmement importantes. Les autres valeurs en texte brut ne sont pas si importantes et, si elles sont laissées en blanc, elles peuvent être considérées comme correctes.

Généralement, si un atsortingbut est requirejs, il est défini sur Not NULL et s’il peut être omis, il est défini comme nullable.

Comme @ForYourOwnGood a dit – Null doit être utilisé pour les informations “inconnues”. Par exemple: Si vous avez beaucoup de champs que le client doit remplir lors de l’enregistrement et que certains sont facultatifs. Pour une raison quelconque, vous voudrez peut-être réserver un identifiant pour ce client particulier et comme vous ne savez pas si les champs optionnels sont un choix réel du client à laisser vide, vous devez les définir comme NULL, c’est-à-dire “inconnu” la ligne. Si le client soumet le formulaire, passe toutes vos validations et enregistre les informations, vous savez alors que le champ optionnel est laissé vide par intention.

Ceci est juste un bon cas d’utilisation de NULL.

La chaîne vide ne doit pas être utilisée à la place de NULL . NULL ne représente rien où la chaîne vide est quelque chose, avec rien à l’intérieur. NULL sera toujours faux par rapport à une autre valeur (même NULL ) et NULL ne sera pas additionné dans la fonction COUNT .

Si vous devez représenter des informations inconnues, rien ne remplace NULL .

Le principal avantage, bien sûr, est la signification sémantique de NULL, que vous avez mentionnée.

En plus de cela – et cela peut dépendre de votre moteur de stockage, comme toujours, vérifiez la documentation – mais dans au moins certaines bases de données, les valeurs NULL prennent beaucoup moins de place qu’une valeur normale. Par exemple, si vous avez une colonne “varchar” déclarée avec 20 caractères et qu’elle est rarement remplie, vous pouvez économiser beaucoup d’espace disque en le rendant NULL au lieu d’une chaîne vide.

Je n’ai jamais entendu parler de problèmes de performance liés à l’utilisation de NULL, l’un au contraire. J’ai entendu parler de gens qui comptaient leurs comptes parce qu’ils comptaient des valeurs NULL incorrectes, mais jamais des performances. Si c’est une chose réelle, j’aimerais en entendre parler!

La signification d’une colonne NULL est plus ou moins “ne s’applique pas dans ce contexte”. J’utilise généralement des colonnes NULL dans deux cas:

• Si le champ ne s’applique pas (disons que vous avez une colonne booléenne is_thirsty et que vous ajoutez deux jeux de données. Un humain et une pierre. Dans le cas de l’humain, vous définissez is_thirsty sur true ou false, alors que dans le cas de la pierre , vous le définiriez probablement sur NULL.
• Si j’ai besoin de marquer quelque chose et de stocker des données avec la valeur. Comme une date de clôture d’inventaire, que vous utiliseriez pour a) spécifier que l’inventaire ne peut plus être modifié et b) spécifier le moment où l’inventaire a été fermé. Au lieu d’avoir deux colonnes ( closed_at et is_closed ), je crée simplement la colonne closed_at et la règle sur NULL si l’ensemble d’inventaire peut toujours être modifié, mais définissez la date une fois fermée.

Fondamentalement, cela revient au fait que j’utilise NULL lorsque le vide d’un champ a une sémantique unique différente d’un champ vide. L’absence d’initiale intermédiaire est justement cela. L’absence de date de clôture a le sens de l’ensemble d’inventaire toujours ouvert aux modifications.

Les valeurs NULL peuvent avoir des effets secondaires désagréables et rendre la vie plus difficile pour append des données à la table. Plus souvent qu’autrement, vous pouvez vous retrouver avec un mélange de valeurs NULL et de chaînes vides, par exemple.

De plus, NULL n’est pas égal à rien, ce qui va vider les requêtes partout si vous n’êtes pas très prudent.

Personnellement, j’utilise les colonnes NULL uniquement lorsqu’un des deux cas ci-dessus s’applique. Je ne l’utilise jamais pour signifier des champs vides lorsque le vide n’a pas d’autre sens que l’absence d’une valeur.

Je comprends qu’il y a des moments où la sémantique NULL de MySQL est tout à fait appropriée.

Cela dit, ils gênent sérieusement, particulièrement avec les champs de texte.

Voici un exemple concret.

Nous souhaitons copier les données d’une firebase database FileMaker dans une table mysql.

Si nous faisons “SELECT * from table où textfield <> ‘test'”, les lignes qui ont un champ de texte NULL NE seront PAS retournées. Ce n’est probablement pas ce que vous attendiez ou souhaitiez.

si un champ qui est nullable est utilisé dans une requête where, seul ou en tant que partie d’un AND, les entrées NULL ne seront JAMAIS renvoyées, sauf si le test IS NULL est utilisé. Nous devons faire quelque chose comme “où ((textfield <>” test “) OR (textfield IS NOT NULL))” qui est au mieux laid.

Donc, dans ce cas, nous ne voulons probablement pas que le champ soit nullable.

Le problème est que vous ne pouvez PAS insérer une chaîne vide dans MySQL en utilisant Filemaker. Il est converti en NULL, ce qui provoque des erreurs si vous avez rendu la colonne non nulle! Si vous permettez NULL, alors le transfert vers mysql fonctionne, mais alors vos requêtes n’agissent pas comme vous le souhaitez!

le workaroud consiste à modifier la table deux fois, à convertir les valeurs NULL existantes après l’importation en chaîne vide, puis à modifier à nouveau la table pour autoriser la valeur NULL. youch!

putain de cinéaste

Tous les moteurs de firebase database qui se respectent de nos jours ne devraient offrir aucune pénalité pour l’utilisation correcte des valeurs NULL, à moins que votre requête ne soit pas conçue correctement (ce qui n’est généralement pas un problème pour NULL).

Vous devez faire attention à utiliser la firebase database (y compris les valeurs NULL) comme prévu; puis se soucier des conséquences optimizatin quand et si elles se produisent.

L’effet cumulatif des valeurs de colonne incorrectement NULL à la fois dans la complexité et la précision de SQL l’emportera presque certainement sur les avantages de tromper avec DBMS Mother. De plus, cela vous gâchera la tête, ainsi que celle de quiconque, plus tard, essaiera de comprendre ce que vous essayez de faire.

Sur certaines bases de données comme Oracle, il est possible que MySQL soit vrai:

• Les valeurs nulles ne sont pas indexées, alors si la recherche de valeurs nulles peut être un goulot d’étranglement.
• Les null finaux sur les lignes économisent de l’espace.