Jak sprawdzić, czy wartość już istnieje, aby uniknąć duplikatów?

Aby odpowiedzieć na pierwsze pytanie, najprostszym sposobem sprawdzenia, czy istnieje duplikat, jest uruchomienie zapytania SQL na podstawie tego, co próbujesz dodać!

Na przykład, jeśli chcesz sprawdzić adres url http://www.example.com/ w tabeli links, Twoje zapytanie będzie wyglądać mniej więcej tak:

SELECT * FROM links WHERE url = 'http://www.example.com/';

Twój kod PHP wyglądałby jak

$conn = mysql_connect('localhost', 'username', 'password');
if (!$conn)
{
    die('Could not connect to database');
}
if(!mysql_select_db('mydb', $conn))
{
    die('Could not select database mydb');
}

$result = mysql_query("SELECT * FROM links WHERE url = 'http://www.example.com/'", $conn);

if (!$result)
{
    die('There was a problem executing the query');
}

$number_of_rows = mysql_num_rows($result);

if ($number_of_rows > 0)
{
    die('This URL already exists in the database');
}

Napisałem to od dawna tutaj, z całym podłączeniem do bazy danych, itp. Jest prawdopodobne, że będziesz już miał połączenie z bazą danych, więc powinieneś użyć tego zamiast rozpoczynać nowe połączenie (zastąp $conn w poleceniu mysql_query i usuń rzeczy do zrobienia z mysql_connect i mysql_select_db)

Oczywiście, istnieją inne sposoby łączenia się z bazą danych, jak PDO, lub za pomocą ORM, lub podobne, więc jeśli już z nich korzystasz, ta odpowiedź może nie być istotna (i prawdopodobnie jest to trochę poza zakresem, aby dać odpowiedzi związane z tym tutaj!)

Jednak MySQL zapewnia wiele sposobów, aby zapobiec to od samego początku.

Po pierwsze, możesz zaznaczyć pole jako "unikalne".

Powiedzmy, że mam tabelę, w której chcę po prostu przechowywać wszystkie adresy URL, które są połączone z moją witryną, i ostatni raz były odwiedzane.

Moja definicja może wyglądać mniej więcej tak:-

CREATE TABLE links
(
    url VARCHAR(255) NOT NULL,
    last_visited TIMESTAMP
)

To pozwoliłoby mi dodać ten sam adres URL w kółko, chyba że napisałem jakiś kod PHP podobny do powyższego, aby to powstrzymać.

Jednakże, czy moja definicja aby zmienić na

CREATE TABLE links
(
  url VARCHAR(255)  NOT NULL,
  last_visited TIMESTAMP,
  PRIMARY KEY (url)
)

Wtedy to sprawi, że mysql rzuci błąd, gdy próbowałem wstawić tę samą wartość dwa razy.

Przykładem w PHP będzie

$result = mysql_query("INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW()", $conn);

if (!$result)
{
    die('Could not Insert Row 1');
}

$result2 = mysql_query("INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW()", $conn);

if (!$result2)
{
    die('Could not Insert Row 2');
}

Jeśli to uruchomisz, zauważysz, że przy pierwszej próbie scenariusz umrze wraz z komentarzem Could not Insert Row 2. Jednak w kolejnych biegach ginie z Could not Insert Row 1.

Dzieje się tak dlatego, że MySQL wie, że adres url jest głównym kluczem tabeli. Klucz podstawowy jest unikalnym identyfikatorem tego wiersza. Większość z czas, warto ustawić unikalny identyfikator wiersza na liczbę. Dzieje się tak dlatego, że MySQL jest szybszy w wyszukiwaniu liczb niż w wyszukiwaniu tekstu. W MySQL klucze (a zwłaszcza klucze podstawowe) są używane do definiowania relacji między dwiema tabelami. Na przykład, gdybyśmy mieli tabelę dla użytkowników, moglibyśmy ją zdefiniować jako

CREATE TABLE users (
  username VARCHAR(255)  NOT NULL,
  password VARCHAR(40) NOT NULL,
  PRIMARY KEY (username)
)

Jednak, gdy chcieliśmy przechowywać informacje o postie, który użytkownik zrobił, musieliśmy zapisać nazwę Użytkownika z tym postem, aby zidentyfikować, że post należał do tego użytkownika.

Już wspomniałem, że MySQL jest szybszy w wyszukiwaniu liczb niż ciągów, więc oznaczałoby to, że będziemy spędzać czas na szukaniu ciągów, gdy nie będziemy musieli.

Aby rozwiązać ten problem, możemy dodać dodatkową kolumnę, user_id, i uczynić ją głównym kluczem (tak więc, gdy wyszukamy Rekord użytkownika na podstawie postu, możemy go znaleźć szybciej)

CREATE TABLE users (
  user_id INT(10)  NOT NULL AUTO_INCREMENT,
  username VARCHAR(255)  NOT NULL,
  password VARCHAR(40)  NOT NULL,
  PRIMARY KEY (`user_id`)
)

Zauważ, że dodałem tu również coś nowego-AUTO_INCREMENT. To w zasadzie pozwala nam pozwolić to pole samo się o siebie troszczy. Za każdym razem, gdy wstawiany jest nowy wiersz, dodaje on 1 do poprzedniego numeru i zapisuje go, więc nie musimy się martwić o numerację i możemy po prostu pozwolić mu zrobić to samemu.

Więc, z powyższej tabeli, możemy zrobić coś w rodzaju

INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');

A potem

INSERT INTO users (username, password) VALUES('User', '988881adc9fc3655077dc2d4d757d480b5ea0e11');

Kiedy wybieramy rekordy z bazy danych, otrzymujemy:-

mysql> SELECT * FROM users;
+---------+----------+------------------------------------------+
| user_id | username | password                                 |
+---------+----------+------------------------------------------+
|       1 | Mez      | d3571ce95af4dc281f142add33384abc5e574671 |
|       2 | User     | 988881adc9fc3655077dc2d4d757d480b5ea0e11 |
+---------+----------+------------------------------------------+
2 rows in set (0.00 sec)

Jednak tutaj - mamy problem - nadal możemy dodać Kolejnego Użytkownika o tej samej nazwie! Oczywiście, tego nie chcemy robić!

mysql> SELECT * FROM users;
+---------+----------+------------------------------------------+
| user_id | username | password                                 |
+---------+----------+------------------------------------------+
|       1 | Mez      | d3571ce95af4dc281f142add33384abc5e574671 |
|       2 | User     | 988881adc9fc3655077dc2d4d757d480b5ea0e11 |
|       3 | Mez      | d3571ce95af4dc281f142add33384abc5e574671 |
+---------+----------+------------------------------------------+
3 rows in set (0.00 sec)

Zmienimy definicję tabeli!

CREATE TABLE users (
  user_id INT(10)  NOT NULL AUTO_INCREMENT,
  username VARCHAR(255)  NOT NULL,
  password VARCHAR(40)  NOT NULL,
  PRIMARY KEY (user_id),
  UNIQUE KEY (username)
)

Zobaczmy, co się stanie, gdy teraz spróbujemy wstawić tego samego Użytkownika dwa razy.

mysql> INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');
ERROR 1062 (23000): Duplicate entry 'Mez' for key 'username'

Teraz wróćmy do naszej tabeli linków, ale z nową definicją.

CREATE TABLE links
(
    link_id INT(10)  NOT NULL AUTO_INCREMENT,
    url VARCHAR(255)  NOT NULL,
    last_visited TIMESTAMP,
    PRIMARY KEY (link_id),
    UNIQUE KEY (url)
)

I wstawmy "http://www.example.com" do baza danych.

INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());

Jeśli spróbujemy wstawić go ponownie....

ERROR 1062 (23000): Duplicate entry 'http://www.example.com/' for key 'url'

Ale co się stanie, jeśli chcemy zaktualizować czas ostatniej wizyty?

Cóż, moglibyśmy zrobić coś skomplikowanego z PHP, jak tak:-

$result = mysql_query("SELECT * FROM links WHERE url = 'http://www.example.com/'", $conn);

if (!$result)
{
    die('There was a problem executing the query');
}

$number_of_rows = mysql_num_rows($result);

if ($number_of_rows > 0)
{
    $result = mysql_query("UPDATE links SET last_visited = NOW() WHERE url = 'http://www.example.com/'", $conn);

    if (!$result)
    {
        die('There was a problem updating the links table');
    }
}

Lub nawet pobrać id wiersza w bazie danych i użyć go do aktualizacji.

$result = mysql_query ("SELECT * FROM links WHERE url =" http://www.example.com/'", $conn);

if (!$result)
{
    die('There was a problem executing the query');
}

$number_of_rows = mysql_num_rows($result);

if ($number_of_rows > 0)
{
    $row = mysql_fetch_assoc($result);

    $result = mysql_query('UPDATE links SET last_visited = NOW() WHERE link_id = ' . intval($row['link_id'], $conn);

    if (!$result)
    {
        die('There was a problem updating the links table');
    }
}

Ale MySQL ma ładną wbudowaną funkcję o nazwie REPLACE INTO

mysql> SELECT * FROM links;
+---------+-------------------------+---------------------+
| link_id | url                     | last_visited        |
+---------+-------------------------+---------------------+
|       1 | http://www.example.com/ | 2011-08-19 23:48:03 |
+---------+-------------------------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());
ERROR 1062 (23000): Duplicate entry 'http://www.example.com/' for key 'url'
mysql> REPLACE INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM links;
+---------+-------------------------+---------------------+
| link_id | url                     | last_visited        |
+---------+-------------------------+---------------------+
|       2 | http://www.example.com/ | 2011-08-19 23:55:55 |
+---------+-------------------------+---------------------+
1 row in set (0.00 sec)

Zauważ, że podczas używania REPLACE INTO, jest aktualizowany czas last_visited, a nie wyrzucany błąd!

Dzieje się tak, ponieważ MySQL wykrywa, że próbujesz zastąpić wiersz. Zna wiersz, który chcesz, ponieważ Ustawiłeś adres URL jako unikalny. MySQL wylicza wiersz do zastąpienia, używając bitu, który przekazałeś, który powinien być unikalny (w tym przypadku url) i aktualizując dla tego wiersza inne wartości. Jest również zaktualizowany link_id - co jest trochę nieoczekiwane! (W rzeczywistości, nie zdawałem sobie sprawy, że to się stanie, dopóki nie zobaczyłem, że to się stało!)

Ale co jeśli chcesz dodać nowy adres URL? Cóż, REPLACE INTO będzie szczęśliwie wstawić nowy wiersz, jeśli nie może znaleźć pasującego unikalnego wiersza!

mysql> REPLACE INTO links (url, last_visited) VALUES ('http://www.stackoverflow.com/', NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM links;
+---------+-------------------------------+---------------------+
| link_id | url                           | last_visited        |
+---------+-------------------------------+---------------------+
|       2 | http://www.example.com/       | 2011-08-20 00:00:07 |
|       3 | http://www.stackoverflow.com/ | 2011-08-20 00:01:22 |
+---------+-------------------------------+---------------------+
2 rows in set (0.00 sec)

Mam nadzieję, że to odpowie na twoje pytanie i da ci trochę więcej informacji o tym, jak działa MySQL!

Czy martwisz się wyłącznie o adresy URL, które są dokładnie tym samym ciągiem znaków .. jeśli tak, jest wiele dobrych rad w innych odpowiedziach. Czy też musisz się martwić o kanonizację?

Na przykład: http://google.com i http://go%4fgle.com są dokładnie tym samym adresem URL, ale będą dozwolone jako duplikaty przez dowolną z technik bazodanowych. Jeśli jest to problem, należy wstępnie przetworzyć adresy URL w celu rozwiązania i sekwencji znaków specjalnych.

W zależności gdzie adresy URL pochodzą od ciebie będziesz musiał również martwić się o parametry i czy są one istotne w Twojej aplikacji.

Najpierw przygotuj bazę danych .

• nazwy domen nie uwzględniają wielkości liter, ale musisz założyć, że reszta adresu URL jest. (Nie wszystkie serwery www respektują przypadek w adresach URL, ale większość tak, i nie można łatwo stwierdzić, patrząc.)
• zakładając, że musisz przechowywać więcej niż nazwę domeny, użyj sortowania uwzględniającego wielkość liter.
• jeśli zdecydujesz się przechowywać adres URL w dwóch kolumnach-jednej dla nazwy domeny i jednej dla lokalizatora zasobów-rozważ użycie sortowania bez rozróżniania wielkości liter dla nazwa domeny oraz zestawienie uwzględniające wielkość liter dla lokalizatora zasobów. Na Twoim miejscu przetestowałbym oba sposoby (URL w jednej kolumnie vs URL w dwóch kolumnach).
• Umieść unikalne ograniczenie w kolumnie adresu URL. Lub na parze kolumn, jeśli przechowujesz nazwę domeny i lokalizator zasobów w oddzielnych kolumnach, jako UNIQUE (url, resource_locator).
• użyj ograniczenia CHECK (), aby utrzymać zakodowane adresy URL poza bazą danych. To ograniczenie CHECK() jest niezbędne, aby złe dane nie trafiały do kopii zbiorczej lub przez SQL shell.

Po drugie, przygotuj adres URL .

• nazwy domen nie uwzględniają wielkości liter. Jeśli przechowujesz pełny adres URL w jednej kolumnie, we wszystkich adresach URL Oznacz nazwę domeny małą literą. Należy jednak pamiętać, że niektóre języki mają wielkie litery, które nie mają małych odpowiedników.

• Pomyśl o przycinaniu końcowych znaków. Na przykład te dwa adresy URL z amazon.com wskaż ten sam produkt. Prawdopodobnie chcesz przechowywać drugą wersję, a nie najpierw.

  Http://www.amazon.com/Systemantics-Systems-Work-Especially-They/dp/070450331X/ref=sr_1_1?ie=UTF8&qid=1313583998&sr=8-1

  Http://www.amazon.com/Systemantics-Systems-Work-Especially-They/dp/070450331X

• Dekodowanie zakodowanych adresów URL. (Zobacz funkcję urldecode () php. Zwróć uwagę uważnie na jego niedociągnięcia, jak opisano w komentarzach na tej stronie.) Osobiście wolałbym raczej zajmować się tego typu transformacjami w bazie danych niż w kodzie klienta. Wymagałoby to cofnięcia uprawnień do tabel i widoków oraz zezwalania na wstawianie i aktualizowanie tylko za pomocą procedur składowanych; procedury składowane obsługują wszystkie operacje ciągów, które umieszczają adres URL w postaci kanonicznej. Ale miej oko na wydajność, gdy tego spróbujesz. Ograniczenia CHECK () (patrz wyżej) są twoją siatką bezpieczeństwa.

Po Trzecie, Jeśli wstawiasz tylko adres URL, nie sprawdzaj najpierw jego istnienia . Zamiast tego spróbuj wstawić i uwięzić błąd, który otrzymasz, jeśli wartość już istnieje. Testowanie i wstawianie trafia do bazy danych dwa razy dla każdego nowego adresu URL. Insert-and-trap trafia tylko raz do bazy danych. Zwróć uwagę, że insert-and-trap to nie to samo, co insert-and-ignore-errors. Tylko jeden konkretny błąd oznacza, że naruszyłeś unikalne ograniczenie; inne błędy oznaczają, że są inne problemy.

Z drugiej strony, jeśli wstawiasz adres URL wraz z innymi danymi w tym samym wierszu, musisz zdecydować z wyprzedzeniem czy będziesz obsługiwał zduplikowane adresy URL przez

• usunięcie starego wiersza i wstawienie nowego (Zobacz zamień rozszerzenie MySQL na SQL )
• aktualizowanie istniejących wartości (Patrz Po aktualizacji duplikatu klucza)
• ignorowanie problemu
• wymaganie od użytkownika podjęcia dalszych działań

REPLACE eliminuje potrzebę pułapki duplikatów błędów kluczy, ale może mieć niefortunne skutki uboczne, jeśli istnieją odniesienia do kluczy obcych.

Aby zagwarantować wyjątkowość, musisz dodać unikalne ograniczenie. Zakładając, że nazwa tabeli to "URL", a nazwa kolumny to "url" , możesz dodać unikalne ograniczenie za pomocą polecenia alter table:

alter table urls add constraint unique_url unique (url);

ALTER table prawdopodobnie zawiedzie (kto naprawdę wie z MySQL), jeśli już masz duplikaty adresów URL w tabeli.

Proste rozwiązania SQL wymagają unikalnego pola; rozwiązania logiczne nie.

Powinieneś znormalizować swoje adresy URL, aby upewnić się, że nie ma duplikacji. Funkcje w PHP takie jak strtolower () i urldecode () lub rawurldecode().

Założenia: nazwa tabeli To "strony internetowe", nazwa kolumny adresu url to "url", a dowolne dane, które mają być powiązane z adresem URL, znajdują się w kolumnie "dane".

Rozwiązania Logiczne

SELECT COUNT(*) AS UrlResults FROM websites WHERE url='http://www.domain.com'

Test poprzednie zapytanie z instrukcjami if W SQL lub PHP, aby upewnić się, że jest 0 przed kontynuacją instrukcji INSERT.

Proste polecenia SQL

Scenariusz 1: Twój db jest tabelą kto pierwszy ten lepszy i nie masz ochoty na duplikaty wpisów w przyszłości.

ALTER TABLE websites ADD UNIQUE (url)

Uniemożliwi to wprowadzanie jakichkolwiek wpisów do bazy danych, jeśli wartość url już istnieje w tej kolumnie.

Scenariusz 2: chcesz najbardziej aktualne informacje dla każdego adresu url i nie chcesz powielać treści. Istnieją dwa rozwiązania tego scenariusza. (Rozwiązania te wymagają również, aby " url " był unikalny, więc rozwiązanie w scenariusz 1 będzie również musiał zostać zrealizowany.)

REPLACE INTO websites (url, data) VALUES ('http://www.domain.com', 'random data')

Spowoduje to uruchomienie akcji DELETE, jeśli wiersz istnieje, a następnie wstaw we wszystkich przypadkach, więc uważaj na deklaracje DELETE.

INSERT INTO websites (url, data) VALUES ('http://www.domain.com', 'random data')
ON DUPLICATE KEY UPDATE data='random data'

Spowoduje to uruchomienie akcji UPDATE, jeśli wiersz istnieje, a INSERT, jeśli tak nie.

Rozważając rozwiązanie tego problemu, musisz najpierw zdefiniować, co oznacza "zduplikowany adres URL" dla Twojego projektu. To określi, jak canonicalize adresy URL przed dodaniem ich do bazy danych.

Istnieją co najmniej dwie definicje:

1. dwa adresy URL są uważane za duplikaty, jeśli reprezentują ten sam zasób, nie wiedząc nic o odpowiedniej Usłudze internetowej, która generuje odpowiednią zawartość. Niektóre rozważania obejmują:
   • The scheme i Domain name część adresów URL są niewrażliwe na wielkość liter, więc HTTP://WWW.STACKOVERFLOW.COM / jest tym samym co http://www.stackoverflow.com/.
   • Jeśli jeden adres URL określa port, ale jest to konwencjonalny port dla schematu i w przeciwnym razie są one równoważne, to są takie same ( http://www.stackoverflow.com / i http://www.stackoverflow.com:80/).
   • jeśli parametry w łańcuchu zapytania są prostymi przearanżowaniami i nazwy parametrów są różne, wtedy są takie same; np. http://authority/?a = test&b = test i http://authority/?b = test&a = test . Zauważ, że http://authority/?a%5B%5D=test1&a%5B%5D=test2 nie jest tym samym, według tej pierwszej definicji identyczności, jak http://authority/?a%5B%5D=test2&a%5B%5D=test1 .
   • jeśli schemat jest HTTP lub HTTPS, to fragmenty skrótu adresów URL mogą zostać usunięte, ponieważ ta część adresu URL nie jest wysyłana do Internetu serwer.
   • skrócony adres IPv6 można rozszerzyć.
   • dołączenie ukośnika w przód do organu tylko , jeśli go brakuje.
   • Unicode canonicalization zmienia odnośny zasób; np. nie można wywnioskować, że http://google.com/?q=%C3%84 (%C3%84 reprezentuje " Ä " w UTF-8) jest takie samo jak http://google.com/?q=A%CC%88 (%CC%88 reprezentuje u+0308, łącząc DIEREZĘ).
   • jeśli schemat jest HTTP lub HTTPS, 'www. 'w jednym adresie URL' s autorytet nie może być po prostu usunięty, jeśli oba adresy URL są równoważne, ponieważ tekst nazwy domeny jest wysyłany jako wartość nagłówka HTTP Host, a niektóre serwery internetowe używają hostów wirtualnych do wysyłania różnych treści opartych na tym nagłówku. Mówiąc bardziej ogólnie, nawet jeśli nazwy domen zostaną rozdzielone na ten sam adres IP, nie można stwierdzić, że odwołane zasoby są takie same.
2. Zastosuj podstawową kanoniczność URL (np. małe litery schematu i nazwy domeny, podaj domyślny port, stabilny sortuje parametry zapytań według nazwy parametru, usuwa część hash w przypadku HTTP i HTTPS, ...), i uwzględniają znajomość serwisu internetowego. Być może założysz, że wszystkie usługi internetowe są wystarczająco inteligentne, aby canonicalize Unicode input (Wikipedia jest, na przykład), więc można zastosować Unicode normalizacji formularza Canonical Composition (NFC). Usuniesz 'www.' ze wszystkich adresów URL przepełnienia stosu. Możesz użyć PostRank ' s postrank-uri kod, przeniesiony do PHP, aby usunąć wszystkie niepotrzebne fragmenty adresów URL(np. &utm_source=...).

Definicja 1 prowadzi do stabilnego rozwiązania(tzn. nie ma dalszej kanonicalizacji, która może być przeprowadzona i kanonicalizacja adresu URL nie ulegnie zmianie). Definicja 2, która moim zdaniem jest tym, co człowiek uważa za definicję kanoniczności URL, prowadzi do procedury kanonicznej, która może przynieść różne wyniki w różnych momentach w czas.

Bez względu na to, którą definicję wybierzesz, sugeruję użycie oddzielnych kolumn dla części scheme, login, host, port i path. Pozwoli to na inteligentne korzystanie z indeksów. Kolumny dla scheme I host mogą używać kolacji znaków (wszystkie kolacje znaków są niewrażliwe na wielkość liter w MySQL), ale kolumny dla loginu i ścieżki muszą używać binarnej, bez rozróżniania wielkości liter. Ponadto, jeśli używasz definicji 2, Musisz zachować oryginalny schemat, autorytet i ścieżkę części, gdyż pewne reguły kanoniczne mogą być od czasu do czasu dodawane lub usuwane.

EDIT: oto przykładowe definicje tabeli:

CREATE TABLE `urls1` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `scheme` VARCHAR(20) NOT NULL,
    `canonical_login` VARCHAR(100) DEFAULT NULL COLLATE 'utf8mb4_bin',
    `canonical_host` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci', /* the "ci" stands for case-insensitive. Also, we want 'utf8mb4_unicode_ci'
rather than 'utf8mb4_general_ci' because 'utf8mb4_general_ci' treats accented characters as equivalent. */
    `port` INT UNSIGNED,
    `canonical_path` VARCHAR(4096) NOT NULL COLLATE 'utf8mb4_bin',

    PRIMARY KEY (`id`),
    INDEX (`canonical_host`(10), `scheme`)
) ENGINE = 'InnoDB';


CREATE TABLE `urls2` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `canonical_scheme` VARCHAR(20) NOT NULL,
    `canonical_login` VARCHAR(100) DEFAULT NULL COLLATE 'utf8mb4_bin',
    `canonical_host` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `port` INT UNSIGNED,
    `canonical_path` VARCHAR(4096) NOT NULL COLLATE 'utf8mb4_bin',

    `orig_scheme` VARCHAR(20) NOT NULL, 
    `orig_login` VARCHAR(100) DEFAULT NULL COLLATE 'utf8mb4_bin',
    `orig_host` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `orig_path` VARCHAR(4096) NOT NULL COLLATE 'utf8mb4_bin',

    PRIMARY KEY (`id`),
    INDEX (`canonical_host`(10), `canonical_scheme`),
    INDEX (`orig_host`(10), `orig_scheme`)
) ENGINE = 'InnoDB';

Tabela 'urls1' służy do przechowywania kanonicznych adresów URL zgodnie z definicją 1. Tabela 'urls2' służy do przechowywania kanonicznych adresów URL zgodnie z definicją 2.

Niestety nie będzie można określić ograniczenia UNIQUE na krotce ('scheme' / 'canonical_scheme' ` 'canonical_login' ` 'canonical_host', 'port', ' canonical_path`) ponieważ MySQL ogranicza długość kluczy InnoDB do 767 bajtów.

Nie znam składni MySQL, ale wszystko, co musisz zrobić, to zawinąć INSERT z instrukcją IF, która zapyta tabelę i sprawdzi, czy rekord z podanym adresem url istnieje, jeśli istnieje - nie wstawiaj nowego rekordu.

Jeśli MSSQL możesz to zrobić:

IF NOT EXISTS (SELECT 1 FROM YOURTABLE WHERE URL = 'URL')
INSERT INTO YOURTABLE (...) VALUES (...)

Jeśli chcesz wstawić adresy URL do tabeli, ale tylko te, które jeszcze nie istnieją, możesz dodać unikalny kontraint na kolumnie i w zapytaniu Wstaw dodaj Ignoruj, aby nie uzyskać błędu.

Przykład: INSERT IGNORE INTO urls SET url = 'url-to-insert'

Po kolei. Jeśli nie utworzyłeś jeszcze tabeli lub utworzyłeś tabelę, ale nie masz w niej danych, musisz dodać unikalną stałą lub unikalny indeks. Więcej informacji na temat wyboru między indeksem a ograniczeniami znajduje się na końcu postu. Ale obie osiągają to samo, wymuszając, że kolumna zawiera tylko unikalne wartości.

Aby utworzyć tabelę z unikalnym indeksem w tej kolumnie, możesz użyć.

CREATE TABLE MyURLTable(
ID INTEGER NOT NULL AUTO_INCREMENT
,URL VARCHAR(512)
,PRIMARY KEY(ID)
,UNIQUE INDEX IDX_URL(URL)
);

Jeśli chcesz mieć unikalny ograniczenie i brak indeksu na tej tabeli, można użyć

CREATE TABLE MyURLTable(
ID INTEGER NOT NULL AUTO_INCREMENT
,URL VARCHAR(512)
,PRIMARY KEY(ID)
,CONSTRAINT UNIQUE UNIQUE_URL(URL)
);

Teraz, jeśli masz już tabelę i nie ma w niej danych, możesz dodać indeks lub ograniczenie do tabeli za pomocą jednego z poniższych fragmentów kodu.

ALTER TABLE MyURLTable
ADD UNIQUE INDEX IDX_URL(URL);

ALTER TABLE MyURLTable
ADD CONSTRAINT UNIQUE UNIQUE_URL(URL);

Teraz możesz mieć już tabelę z danymi w niej. W takim przypadku możesz już mieć w nim zduplikowane dane. Możesz spróbować utworzyć stały lub indeks pokazany powyżej, a to się nie powiedzie, jeśli masz już zduplikowane dane. Jeśli nie masz zduplikowane dane, świetnie, jeśli to zrobisz, będziesz musiał usunąć duplikaty. Możesz zobaczyć podświetlone adresy URL z duplikatami za pomocą następującego zapytania.

SELECT URL,COUNT(*),MIN(ID) 
FROM MyURLTable
GROUP BY URL
HAVING COUNT(*) > 1;

Aby usunąć wiersze, które są duplikatami, i zachować jeden, wykonaj następujące czynności:

DELETE RemoveRecords
FROM MyURLTable As RemoveRecords
LEFT JOIN 
(
SELECT MIN(ID) AS ID
FROM MyURLTable
GROUP BY URL
HAVING COUNT(*) > 1
UNION
SELECT ID
FROM MyURLTable
GROUP BY URL
HAVING COUNT(*) = 1
) AS KeepRecords
ON RemoveRecords.ID = KeepRecords.ID
WHERE KeepRecords.ID IS NULL;

Teraz, gdy usunąłeś wszystkie rekordy, możesz śmiało utworzyć indeks lub ograniczenie. Teraz, jeśli chcesz wstawić wartość do bazy danych, powinieneś użyć czegoś takiego.

INSERT IGNORE INTO MyURLTable(URL)
VALUES('http://www.example.com');

, który będzie próbował wykonać insert, a jeśli znajdzie duplikat, nic się nie stanie. Powiedzmy, że masz inne kolumny, możesz zrobić coś takiego.

INSERT INTO MyURLTable(URL,Visits) 
VALUES('http://www.example.com',1)
ON DUPLICATE KEY UPDATE Visits=Visits+1;

To będzie wyglądać spróbować wstawić wartość, a jeśli znajdzie adres URL, to zaktualizuje rekord zwiększając licznik odwiedzin. Oczywiście zawsze możesz zrobić zwykłą starą wstawkę i poradzić sobie z wynikowym błędem w kodzie PHP. Teraz, jeśli chodzi o to, czy powinieneś używać ograniczeń lub indeksów, zależy to od wielu czynników. Indeksy pozwalają na szybsze wyszukiwanie, więc twoje wydajność będzie lepsza w miarę powiększania się tabeli, ale przechowywanie indeksu zajmie dodatkowe miejsce. Indeksy również zwykle sprawiają, że wstawki i aktualizacje trwają dłużej, ponieważ muszą zaktualizować indeks. Jednakże, ponieważ wartość będzie musiała być sprawdzana w obu kierunkach, aby wymusić wyjątkowość, w tym przypadku może być szybciej po prostu mieć indeks. Jeśli chodzi o cokolwiek związanego z wydajnością, odpowiedzią jest wypróbowanie obu opcji i profilowanie wyników, aby zobaczyć, które najlepiej pasuje do twojej sytuacji.

Jeśli chcesz tylko odpowiedzieć tak lub nie, ta składnia powinna dać ci najlepszą wydajność.

select if(exists (select url from urls where url = 'http://asdf.com'), 1, 0) from dual

Jeśli chcesz się tylko upewnić, że nie ma duplikatów, dodaj unikalny indeks do pola url, w ten sposób nie ma potrzeby jawnego sprawdzania, czy url istnieje, po prostu wstaw jak zwykle, a jeśli już tam jest, to wstawka zawiedzie z duplikatem błędu klucza.

Odpowiedź zależy od tego, czy chcesz wiedzieć, kiedy podejmowana jest próba wprowadzenia rekordu z duplikatem pola. Jeśli cię to nie obchodzi, użyj " INSERT... ON DUPLICATE KEY " składni, ponieważ to sprawi, że próba spokojnie powiedzie się bez tworzenia duplikatu.

Jeśli z drugiej strony chcesz wiedzieć, kiedy takie zdarzenie się wydarzy i zapobiec temu, powinieneś użyć unikalnego ograniczenia klucza, które spowoduje, że próba Wstawienia / aktualizacji nie powiedzie się ze znaczącym błędem.

$url = "http://www.scroogle.com";

$query  = "SELECT `id` FROM `urls` WHERE  `url` = '$url' ";
$resultdb = mysql_query($query) or die(mysql_error());   
list($idtemp) = mysql_fetch_array($resultdb) ;

if(empty($idtemp)) // if $idtemp is empty the url doesn't exist and we go ahead and insert it into the db.
{ 
   mysql_query("INSERT INTO urls (`url` ) VALUES('$url') ") or die (mysql_error());
}else{
   //do something else if the url already exists in the DB
}

Niech kolumna primary key

Możesz zlokalizować (i usunąć) za pomocą połączenia własnego. Twoja tabela ma kilka adresów URL, a także kilka PK (wiemy, że PK nie jest URL, ponieważ w przeciwnym razie nie możesz mieć duplikatów)

SELECT
    *
FROM
    yourTable a
JOIN
    yourTable b -- Join the same table
        ON b.[URL] = a.[URL] -- where the URL's match
        AND b.[PK] <> b.[PK] -- but the PK's are different

Zwróci wszystkie wiersze, które mają zduplikowane adresy URL.

Powiedz jednak, że chcesz wybrać tylko duplikaty i wykluczyć oryginał.... Cóż, musiałbyś zdecydować, co stanowi oryginał. Na potrzeby tej odpowiedzi Załóżmy, że najniższy PK To "Oryginalny"

Wystarczy dodać następującą klauzulę do powyższego zapytania:

WHERE
    a.[PK] NOT IN (
        SELECT 
            TOP 1 c.[PK] -- Only grabbing the original!
        FROM
            yourTable c
        WHERE
            c.[URL] = a.[URL] -- has the same URL
        ORDER BY
            c.[PK] ASC) -- sort it by whatever your criterion is for "original"

Teraz masz zbiór wszystkich nieoryginalnych duplikowanych wierszy. Możesz łatwo wykonać DELETE lub cokolwiek chcesz z tego zestawu wyników.

Zauważ, że takie podejście może być nieefektywne, po części dlatego, że mySQL nie zawsze obsługuje IN dobrze, ale rozumiem z OP, że jest to rodzaj "sprzątania" na stole, nie zawsze Kontrola.

Jeśli chcesz sprawdzić w INSERT czasie, czy wartość już istnieje, możesz uruchomić coś takiego

SELECT 
    1
WHERE
    EXISTS (SELECT * FROM yourTable WHERE [URL] = 'testValue')

Jeśli uzyskasz wynik, możesz stwierdzić, że wartość już istnieje w Twoim DB przynajmniej raz.

Możesz wykonać to zapytanie:

SELECT url FROM urls WHERE url = 'http://asdf.com' LIMIT 1

Następnie sprawdź czy mysql_num_rows() == 1 Sprawdzić, czy istnieje.