MySQL - comment avant tampon zip code "0"?

Question

Dans ma base de données MySQL InnoDB, j'ai sale code postal de données que je veux nettoyer.

Le propre code postal de données est alors que j'ai tous les 5 chiffres pour le code postal (par exemple, "90210").

Mais pour une raison quelconque, j'ai remarqué dans ma base de données que pour zipcodes qui commencent par un "0", 0 a été ignoré.

Donc "Holtsville, New York" avec code postale "00544" est stocké dans ma base de données "544"

et

"Dedham, MA" avec code postale "02026" est stocké dans ma base de données "2026".

Ce SQL puis-je exécuter l'avant de la pad "0" à n'importe quel code postal qui n'est pas à 5 chiffres? Sens, si le code postal est composé de 3 chiffres dans la longueur, avant tampon "00". Si le code postal est de 4 chiffres dans la longueur, avant tampon juste "0".

Mise à JOUR:

Je viens de changer le code postal pour être de type de données VARCHAR(5)

Answer 1

Stocker vos zipcodes comme CHAR(5) au lieu d'un type numérique, ou votre demande de dpa avec des zéros, lorsque vous le chargez à partir de la DB. Une façon de le faire avec php:

echo sprintf("%05d", 205); // prints 00205
echo sprintf("%05d", 1492); // prints 01492

Ou vous pourriez avoir MySQL pad pour vous:

SELECT LPAD(zip, 5, '0') as zipcode FROM table;

Voici un moyen de mettre à jour et de blocs de toutes les lignes:

ALTER TABLE `table` CHANGE `zip` `zip` CHAR(5); #changes type
UPDATE table SET `zip`=LPAD(`zip`, 5, '0'); #pads everything

Answer 2

Vous devez décider de la longueur du code postal (qui je crois doit être de 5 caractères). Ensuite, vous devez dire à MySQL à zéro-remplir les numéros.

Supposons que votre table est appelée mytable et le domaine en question est - zipcode, tapez smallint. Vous devez émettre la requête suivante:

ALTER TABLE mytable CHANGE `zipcode` `zipcode`
    smallint( 5 ) UNSIGNED ZEROFILL NOT NULL;

L'avantage de cette méthode c'est qu'elle laisse intactes toutes vos données, il n'y a pas besoin d'utiliser des déclencheurs au cours d'insertion des données mises à jour, il n'y a pas besoin d'utiliser des fonctions lorsque vous SELECT les données et que vous pouvez toujours supprimer les zéros supplémentaires ou augmenter la longueur de champ si vous changez d'avis.

Answer 3

Ok, donc vous avez changé la colonne du Numéro de type VARCHAR(5). Maintenant, vous avez besoin de mettre à jour le code postal champ à gauche-collier. Le SQL de le faire serait:

UPDATE MyTable
SET ZipCode = LPAD( ZipCode, 5, '0' );

Ce sera pavé de toutes les valeurs dans le code Postal de la colonne à 5 caractères, ajout de '0' sur la gauche.

Bien sûr, maintenant que vous avez toutes vos anciennes données fixes, vous devez vous assurer que votre toute nouvelle donnée est également complété par des zéros. Il y a plusieurs écoles de pensée sur la façon correcte de le faire:

• Gérer dans la logique métier de l'application. Avantages: base de données indépendant de la solution, n'implique pas apprendre plus au sujet de la base de données. Inconvénients: doit être traitée partout qui écrit dans la base de données, dans toutes les applications.

• Le manipuler avec une procédure stockée. Avantages: procédures Stockées imposer des règles d'entreprise pour tous les clients. Inconvénients: les procédures Stockées sont plus complexes que la simple INSERTION/mise à JOUR des énoncés, et non pas comme des portables dans les bases de données. Un nu-INSERTION/mise à JOUR peut encore insérer des non-nulle collier de données.

• Manipuler avec un déclencheur. Avantages: travail pour les Procédures Stockées et nu INSERT/UPDATE. Inconvénients: Moins solution portable. Le plus lent de la solution. Les déclencheurs peuvent être durs pour obtenir le droit.

Dans ce cas, je voudrais y faire face au niveau de l'application (le cas échéant), et non pas le niveau de base de données. Après tout, pas tous les pays qui utilisent un 5-digit code Postal (pas même les etats-unis, notre zipcodes sont effectivement Zip+4+2: nnnnn-nnnn-nn) et certains permettent de lettres ainsi que des chiffres. Mieux vaut ne PAS essayer de forcer un format de données et d'accepter les données occasionnelles d'erreur, que d'empêcher quelqu'un d'entrer la valeur correcte, même si son format n'est pas du tout ce à quoi vous vous attendiez.

Answer 4

Il serait quand même judicieux de créer votre champ code postal comme un zerofilled entier non signé de champ.

CREATE TABLE xxx ( zipcode INT(5) ZEROFILL UNSIGNED, ... )

De cette façon, mysql prend soin de la marge pour vous.

Answer 5

CHAR(5)

ou

MEDIUMINT (5) UNSIGNED ZEROFILL

Le premier prend 5 octets par code postal.

La seconde ne prend que 3 octets par code postal. L'option zerofill ne est nécessaire pour les codes postaux de zéros.