Jaki jest najlepszy sposób przechowywania adresu e-mail w PostgreSQL?


40

Jaki typ danych byłby odpowiedni do przechowywania adresów e-mail w PostgreSQL?

Mogę użyć varchar(lub nawet text), ale zastanawiam się, czy istnieje bardziej konkretny typ danych dla wiadomości e-mail.

Odpowiedzi:


38

Custom DOMAINs

Nie sądzę, aby używanie citext(bez rozróżniania wielkości liter) było wystarczające [1] . Korzystając z PostgreSQL, możemy stworzyć domenę niestandardową, która jest zasadniczo pewnymi zdefiniowanymi ograniczeniami względem typu . Możemy stworzyć domenę na przykład nad citexttypem lub ponad text.

Korzystanie ze type=emailspecyfikacji HTML5

Obecnie najbardziej poprawna odpowiedź na pytanie, jakim jest adres e-mail, jest podana w RFC5322 . Ta specyfikacja jest niesamowicie złożona [2] , do tego stopnia, że wszystko ją psuje. HTML5 zawiera inną specyfikację dla e-maili ,

To wymaganie jest umyślnym naruszeniem RFC 5322, który definiuje składnię adresów e-mail, która jest jednocześnie zbyt surowa (przed znakiem „@”), zbyt niejasna (po znaku „@”) i zbyt luźna (dopuszczanie komentarzy , białych znaków i cytowanych ciągów w sposób nieznany większości użytkowników), aby można je było tutaj zastosować. [...] Poniższe wyrażenie regularne zgodne z JavaScript i Perl jest implementacją powyższej definicji.

/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$/

Jest to prawdopodobnie to, czego chcesz, a jeśli jest wystarczająco dobre dla HTML5, prawdopodobnie jest wystarczająco dobre dla Ciebie. Możemy to wykorzystać bezpośrednio w PostgreSQL. Używam również citexttutaj (co technicznie oznacza, że ​​możesz po prostu trochę regexować wizualnie, usuwając wielkie lub małe litery).

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

Teraz możesz zrobić ...

SELECT 'asdf@foobar.com'::email;

Ale nie

SELECT 'asdf@foob,,ar.com'::email;
SELECT 'asd@f@foobar.com'::email;

Ponieważ oba powracają

ERROR:  value for domain email violates check constraint "email_check"

Ponieważ jest to również oparte na citext

SELECT 'asdf@foobar.com'::email = 'ASdf@fooBAR.com';

domyślnie zwraca true.

Używanie plperlu/Email::Valid

Co ważne, istnieje bardziej poprawna metoda wykonania tego zadania, która jest o wiele bardziej złożona w użyciu plperlu. Jeśli potrzebujesz tego poziomu poprawności, nie chcesz citext. Email::Validmoże nawet sprawdzić, czy domena ma rekord MX (przykład w dokumentach Email :: Valid)! Najpierw dodaj plperlu (wymaga superużytkownika).

CREATE EXTENSION plperlu;

Następnie utwórz funkcję , zauważ, że oznaczamy jako IMMUTABLE:

CREATE FUNCTION valid_email(text)
  RETURNS boolean
  LANGUAGE plperlu
  IMMUTABLE LEAKPROOF STRICT AS
$$
  use Email::Valid;
  my $email = shift;
  Email::Valid->address($email) or die "Invalid email address: $email\n";
  return 'true';
$$;

Następnie utwórz domenę ,

CREATE DOMAIN validemail AS text NOT NULL
  CONSTRAINT validemail_check CHECK (valid_email(VALUE));

Przypisy

  1. Używanie citextjest technicznie nieprawidłowe. SMTP określa local-partwielkość liter. Ale znowu jest to przypadek głupoty ze specyfikacji . Zawiera własne kryzysy tożsamości. Specyfikacja mówi local-part(część przed @) „MOŻE być rozróżniana wielkość liter” ... „MUSI BYĆ traktowana jako rozróżniana wielkość liter”… a jednak „wykorzystywanie rozróżniania wielkości liter w lokalnych częściach skrzynki pocztowej utrudnia interoperacyjność i jest odradzane”.
  2. Specyfikacja adresu e-mail jest tak złożona, że ​​nie jest nawet samodzielna. Complex jest naprawdę mało powiedziane, a ci , którzy sprawiają, że specyfikacja nawet tego nie rozumie. . Z dokumentacji na regular-expression.info

    Żadne z tych wyrażeń regularnych nie wymusza ograniczenia długości ogólnego adresu e-mail, części lokalnej ani nazw domen. RFC 5322 nie określa żadnych ograniczeń długości. Wynika to z ograniczeń innych protokołów, takich jak protokół SMTP do faktycznego wysyłania wiadomości e-mail. RFC 1035 stwierdza, że ​​domeny muszą mieć maksymalnie 63 znaki, ale nie uwzględnia tego w specyfikacji składni. Powodem jest to, że prawdziwy język regularny nie może wymuszać ograniczenia długości i jednocześnie nie zezwalać na kolejne myślniki.


1
Link do W3.org jest zepsuty; oto alternatywne źródło: html.spec.whatwg.org/multipage/…
MaxGabriel

@MaxGabriel dzięki trzymaj się, już niedługo dostaniesz pozwolenie na edycję. Naprawię to.
Evan Carroll

Czy istnieje powód, aby mieć obie te cechy a-zi A-Zw klasach postaci?
xehpuk

@ xehpuk dobrze, ponieważ ~rozróżniana jest wielkość liter, musisz albo (a) użyć ~*rozróżniania wielkości liter, albo (b) mieć duże i małe litery w klasie char.
Evan Carroll

citext„s ~wydaje się być przypadek-niewrażliwe na mnie, dlatego pytam.
xehpuk

46

Zawsze używam CITEXTdo wiadomości e-mail, ponieważ w adresie e-mail (w praktyce) nie jest rozróżniana wielkość liter , tj. John@Example.com jest taki sam jak John@example.com.

Łatwiej jest również ustawić unikalny indeks, aby zapobiec duplikatom, w porównaniu do tekstu:

-- citext
CREATE TABLE address (
   id serial primary key,
   email citext UNIQUE,
   other_stuff json
);

-- text
CREATE TABLE address (
   id serial primary key,
   email text,
   other_stuff json
);
CREATE UNIQUE INDEX ON address ((lower(email)));

Porównywanie wiadomości e-mail jest również łatwiejsze i mniej podatne na błędy:

SELECT * FROM address WHERE email = 'JOHN@example.com';

w porównaniu do:

SELECT * FROM address WHERE lower(email) = lower('JOHN@example.com');

CITEXTjest typem zdefiniowanym w standardowym module rozszerzeń o nazwie „citext” i dostępnym po wpisaniu:

CREATE EXTENSION citext;

PS texti varcharsą praktycznie takie same w Postgres i nie ma kary za używanie, textjak można się spodziewać. Sprawdź tę odpowiedź: Różnica między tekstem a varcharem


10

Zawsze używam, varchar(254)ponieważ adres e-mail nie może być dłuższy niż 254 znaki.

Zobacz https://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address

Postgresql nie ma wbudowanego typu adresów e-mail, chociaż natknąłem się na niektóre typy danych.

Ponadto możesz dodać wyzwalacz lub inną logikę w celu ustandaryzowania adresów e-mail na wypadek, gdybyś chciał dodać do niego unikalny klucz.

W szczególności domainczęść adresu e-mail (która ma postać local-part@, domainnie rozróżnia małych i wielkich liter, podczas gdy local-partmusi być traktowana jako rozróżniana wielkość liter . Patrz http://tools.ietf.org/html/rfc5321#section-2.4

Inną kwestią jest, jeśli chcesz przechowywać nazwy i adresy e-mail w formularzu "Joe Bloggs" <joe.bloggs@hotmail.com>, w którym to przypadku potrzebujesz ciągu dłuższego niż 254 znaków i nie będziesz w stanie znacząco użyć unikalnego ograniczenia. Nie zrobiłbym tego i sugeruję osobne przechowywanie nazwiska i adresu e-mail. Ładne drukowanie adresów w tym formacie jest zawsze możliwe w warstwie prezentacji.


Zgodnie z 4.5.3.1. Limity i minima rozmiaru , maksymalna długość to 320 znaków (łącznie z @).
Andriy M

1
@AndriyM W odnośnej sekcji nie ma nic, co mówi 320. I tak jest źle; tools.ietf.org/html/rfc5321#section-4.5.3.1.3 stwierdza, że ​​maksymalna długość ścieżki wynosi 256 znaków, i że musi zawierać otaczające ją „<” i „>”, co daje maksymalnie 254.
Colin Hart

Doszedłem do 320 jako maksimum na podstawie 4.5.3.1.1 („Maksymalna łączna długość nazwy użytkownika lub innej części lokalnej to 64 oktety”) i 4.5.3.1.2 („Maksymalna łączna długość nazwy domeny lub liczba to 255 oktetów ”). Tak więc 64 + 255 + 1 (the @) = 320. Być może źle to interpretuję.
Andriy M

3
@AndriyM Przeczytaj zaakceptowaną odpowiedź na pytanie, z którym się powiązałem. To wszystko wyjaśnia. To zdecydowanie 254, a nie 320.
Colin 't Hart

3

Możesz być zainteresowany skorzystaniem z czeku CONSTRAINT (być może łatwiejszym, ale możesz odrzucić więcej, niż chcesz, lub korzystasz z FUNKCJI omówionej tutaj i tutaj . Zasadniczo chodzi o kompromisy między specyfiką a łatwością implementacji. Ciekawy temat chociaż. PostgreSQL ma nawet rodzimy typ adresu IP, ale nie jest to projekt na pgfoundry dla typu danych email tutaj . jednak najlepszym znalazłem na ten temat jest e-mail domeny. Domena jest lepsza niż ograniczenie sprawdzania, ponieważ jeśli je zmienisz, musisz to zrobić tylko raz w definicji domeny i nie podążać śladami w dół tabel nadrzędnych i podrzędnych, zmieniając wszystkie ograniczenia sprawdzania. Domeny są naprawdę fajne - trochę jak typy danych, ale prostsze do wdrożenia. Użyłem ich w Firebird - Oracle nawet ich nie ma!

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.