MySQL - jak umieścić kod pocztowy z przodu z „0”?

W mojej bazie danych MySQL InnoDB mam brudne dane kodu pocztowego, które chcę wyczyścić.

Czyste dane kodu pocztowego są wtedy, gdy mam wszystkie 5 cyfr kodu pocztowego (np. „90210”).

Ale z jakiegoś powodu zauważyłem w mojej bazie danych, że w przypadku kodów pocztowych zaczynających się od „0”, 0 zostało usunięte.

Tak więc „ Holtsville, Nowy Jork ” z kodem pocztowym „ 00544” jest przechowywane w mojej bazie danych jako „ 544”

i

„ Dedham, MA ” z kodem pocztowym „ 02026” jest przechowywane w mojej bazie danych jako „ 2026”.

Jaki SQL mogę uruchomić na przednim polu „0” dla dowolnego kodu pocztowego, który nie ma 5 cyfr? Oznacza to, że jeśli kod pocztowy ma 3 cyfry, przednia wkładka „00”. Jeśli kod pocztowy ma 4 cyfry, przednia wkładka to tylko „0”.

AKTUALIZACJA :

Właśnie zmieniłem kod pocztowy na typ danych VARCHAR (5)

Przechowuj swoje kody pocztowe jako CHAR (5) zamiast typu numerycznego lub poproś aplikację, aby wypełniła je zerami podczas ładowania z bazy danych. Sposób na zrobienie tego z PHP przy użyciu sprintf():

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

Lub możesz mieć MySQL dla Ciebie LPAD() :

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

Oto sposób na aktualizację i wypełnienie wszystkich wierszy:

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

Musisz zdecydować o długości kodu pocztowego (który moim zdaniem powinien mieć 5 znaków). Następnie musisz powiedzieć MySQL, aby wypełnił liczby zerami.

Załóżmy, że wywoływana jest Twoja tabela, mytablea dane pole to zipcodewpisz smallint. Musisz wysłać następujące zapytanie:

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

Zaletą tej metody jest to, że pozostawia dane w stanie nienaruszonym, nie ma potrzeby używania wyzwalaczy podczas wstawiania / aktualizacji danych, nie ma potrzeby używania funkcji podczas pobierania SELECTdanych i zawsze można usunąć dodatkowe zera lub zwiększyć długość pola. zmieniasz zdanie.

Ok, więc zmieniłeś kolumnę z Number na VARCHAR (5). Teraz musisz zaktualizować pole kodu pocztowego, aby było wypełnione po lewej stronie. SQL, który to zrobiłby, wyglądałby następująco:

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

Spowoduje to dopełnienie wszystkich wartości w kolumnie ZipCode do 5 znaków, dodając „0” po lewej stronie.

Oczywiście, teraz, gdy masz już naprawione wszystkie stare dane, musisz upewnić się, że wszystkie nowe dane są również wypełnione zerami. Istnieje kilka szkół myślenia o tym, jak to zrobić:

• Obsługuj to w logice biznesowej aplikacji. Zalety: rozwiązanie niezależne od bazy danych, nie wymaga uczenia się więcej o bazie danych. Wady: wymaga obsługi wszędzie tam, gdzie zapisuje się do bazy danych, we wszystkich aplikacjach.

• Obsługuj to za pomocą procedury składowanej. Zalety: procedury składowane wymuszają reguły biznesowe dla wszystkich klientów. Wady: procedury składowane są bardziej skomplikowane niż proste instrukcje INSERT / UPDATE i nie są przenoszone między bazami danych. Sama INSERT / UPDATE może nadal wstawiać dane wypełnione niezerami.

• Obsługuj go za pomocą spustu. Zalety: będzie działać w przypadku procedur składowanych i samych instrukcji INSERT / UPDATE. Wady: Najmniej przenośne rozwiązanie. Najwolniejsze rozwiązanie. Wyzwalacze mogą być trudne do naprawienia.

W tym przypadku załatwiłbym to na poziomie aplikacji (jeśli w ogóle), a nie na poziomie bazy danych. W końcu nie wszystkie kraje używają 5-cyfrowego kodu pocztowego (nawet USA - nasze kody pocztowe to w rzeczywistości Zip + 4 + 2: nnnnn-nnnn-nn), a niektóre dopuszczają zarówno litery, jak i cyfry. Lepiej NIE próbować wymuszać formatu danych i akceptować sporadyczny błąd danych, niż uniemożliwić komuś wprowadzenie poprawnej wartości, nawet jeśli format nie jest taki, jakiego się spodziewałeś.

Wiem, że to dobrze po OP. Jednym ze sposobów jest zachowanie tabeli przechowującej dane kodu pocztowego jako niepodpisane INT, ale wyświetlane z zerami, jest następujące.

select LPAD(cast(zipcode_int as char), 5, '0') as zipcode from table;

Chociaż zachowuje to oryginalne dane jako INT i może zaoszczędzić trochę miejsca w pamięci, serwer przeprowadzi konwersję INT na CHAR za Ciebie. Można to wrzucić do widoku, a osoba, która potrzebuje tych danych, może zostać skierowana tam, a nie sama tabela.

Nadal miałoby sens utworzenie pola kodu pocztowego jako pola z zerową liczbą całkowitą bez znaku.

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

W ten sposób mysql zajmie się wypełnieniem za Ciebie.

CHAR(5)

lub

MEDIUMINT (5) UNSIGNED ZEROFILL

Pierwsza zajmuje 5 bajtów na kod pocztowy.

Drugi zajmuje tylko 3 bajty na kod pocztowy. Opcja ZEROFILL jest konieczna w przypadku kodów pocztowych z zerami na początku.

powinieneś użyć UNSIGNED ZEROFILLw swojej strukturze tabeli.

LPAD współpracuje z VARCHAR2, ponieważ nie umieszcza spacji na pozostawione bajty. LPAD zmienia pozostałe / puste bajty na zera w przypadku typu danych LHS SO powinien mieć wartość VARCHAR2