MySQL: Inserisci record se non esiste nella tabella

Sto cercando di eseguire la query seguente:

INSERT INTO table_listnames (name, address, tele)
VALUES ('Rupert', 'Somewhere', '022')
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name='value'
);

Ma questo restituisce un errore. Fondamentalmente non ho voglia di inserire un record se il campo ‘nome’ del record già presente in un altro record – come controllare se il nuovo nome è unico?

  • possibile duplicato di Come inserire se non esiste’ in MySQL?
  • Tutte le risposte date a questa o gonzi si supponga che è possibile aggiungere un indice univoco. A volte la decisione è basata sulla logica di business che non può essere imposto su tutto il tavolo. Per esempio, si consente a più righe con un certo valore in una colonna, ma un altro valore nella colonna sarà consentita solo per essere visualizzato su una riga. Come facciamo a realizzare questo?
  • vedi anche: stackoverflow.com/q/1361340/4418
InformationsquelleAutor Rupert | 2010-07-02

 

15 Replies
  1. 443

    In realtà non sto suggerendo che si esegue questa operazione, come la UNIQUE indice come suggerito da Piskvor e gli altri è un modo migliore per farlo, ma si può effettivamente fare quello che stavano cercando:

    CREATE TABLE `table_listnames` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(255) NOT NULL,
      `address` varchar(255) NOT NULL,
      `tele` varchar(255) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB;

    Inserire un record:

    INSERT INTO table_listnames (name, address, tele)
    SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM table_listnames WHERE name = 'Rupert'
    ) LIMIT 1;
    
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    SELECT * FROM `table_listnames`;
    
    +----+--------+-----------+------+
    | id | name   | address   | tele |
    +----+--------+-----------+------+
    |  1 | Rupert | Somewhere | 022  |
    +----+--------+-----------+------+

    Tenta di inserire nuovamente lo stesso record:

    INSERT INTO table_listnames (name, address, tele)
    SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM table_listnames WHERE name = 'Rupert'
    ) LIMIT 1;
    
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    +----+--------+-----------+------+
    | id | name   | address   | tele |
    +----+--------+-----------+------+
    |  1 | Rupert | Somewhere | 022  |
    +----+--------+-----------+------+

    Inserire un altro record:

    INSERT INTO table_listnames (name, address, tele)
    SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM table_listnames WHERE name = 'John'
    ) LIMIT 1;
    
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    SELECT * FROM `table_listnames`;
    
    +----+--------+-----------+------+
    | id | name   | address   | tele |
    +----+--------+-----------+------+
    |  1 | Rupert | Somewhere | 022  |
    |  2 | John   | Doe       | 022  |
    +----+--------+-----------+------+

    E così via…

    • Grazie che ha aiutato. Il mio problema attuale è molto più complesso e la colonna può essere unico e non dipendono dalla chiave primaria. Ma questo è esattamente quello che stavo cercando.
    • Questo dovrebbe funzionare, ma io sono un po ‘ preoccupato per le prestazioni, a meno che non è un indice name, il più interno selezionare sarà terribilmente lenti. Se c’è un indice, quindi questo dovrebbe funzionare bene.
    • Concordato. @Rupert: si dovrebbe indice la colonna di cui all’istruzione select interna (name, in questo caso), se possibile. Si noti inoltre che è possibile fare SELECT 'John', 'Doe', '022' FROM table_listnames, invece di SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp – ma che funziona solo se table_listnames già contiene una o più righe. Dubito che la velocità è diversa, però, quindi probabilmente non è un problema.
    • se si desidera aggiungere una riga con lo stesso nome significa, funzionerà?
    • Ho pensato INSERT query non può prendere WHERE clausole? Per favore qualcuno può spiegare a me perché questo funziona, nonostante @Piskvor dicendo che non dovrebbe, nella sua risposta?
    • Questo funziona perché si Inserisce il risultato di un’istruzione Select. Leggere la query con attenzione -> Il WHERE istruzione appartiene alla SELECT query. La query Select restituisce un unico data-riga (dati è inserita) o mancanza di dati (niente è inserito)
    • Questo non funziona se si desidera inserire lo stesso valore per due volte in diversi campi (ad esempio SELECT 'Humbert', 'Humbert', '1' in select interna). Ho ricevuto un ERROR 1060 (42S21): Duplicate column name
    • Penso che si può utilizzare INSERT IGNORE o REPLACE o INSERT ... ON DUPLICATE KEY UPDATE per
    • Ho avuto lo stesso problema #1060 - Duplicate column name. Hai trovato una soluzione? Edit: Trovato. Aggiungi dietro ogni valore un ESEMPIO: SELECT * FROM (SELECT 'Rupert' as name, 'Rupert' as friend, '022' as number) AS tmp
    • u può spiegare la tua risposta è davvero utile per me non ricevo perché u scrivere tmp qui
    • Perché la tabella derivata (subquery) ha bisogno di un nome (anche chiamato alias). (SELECT 'Rupert', 'Somewhere', '022') è la subquery e aggiungere AS tmp (o solo tmp) conferisce il nome. Vedere stackoverflow.com/q/1888779/367456
    • Io sto usando questo, come ho bisogno di inserire dove non esiste una tabella diversa. E ha bisogno di non essere soggetto alle condizioni di gara. Questo è perfetto, grazie.
    • questo non può lavorare sulla concorrenza situazione ? traino sessione di eseguire la stessa istruzione in quanto è in dotazione ?
    • Ciao Mike, il tuo codice mi ha aiutato a risolvere un problema, ma ho aggiunto qualcosa per lavorare meglio, lo potete vedere di seguito
    • Posso usare la stessa sintassi per ottenere un messaggio che dice: “Un record Esistente”? Io di solito uso una procedura per farlo.
    • risposta eccellente. Ora ho un’altra domanda: c’è un modo per sapere se il registro ha inserito?
    • Yo può intert, ma non l’aggiornamento stesso tempo.
    • U può spiegare la query ?? plzz @Mike
    • Voglio usare la tua soluzione per inserire più righe (matrice) di dati utilizzando PDO ma $query->execute(); restituisce sempre TRUE che ha senso, ma mi piacerebbe sapere quale indice dell’array non inserire. Tutte le idee
    • Il tuo suggerimento è di meglio di INSERT IGNORE con un’unica chiave. Ignorando gli errori possono portare a perdere guasti per altri motivi che l’unico vincolo di chiave. Per esempio, se qualcuno aggiunge una nuova colonna e poi la tua stored procedure viene eseguita con un inserto ignorare.
    • questa è una grande questione. Se si desidera creare un nuovo MODO questione di fare riferimento a questa soluzione.
    • È possibile fare riferimento alla tabella alias (tmp) nella clausola WHERE come questo: WHERE NOT EXISTS ( SELECT name FROM table_listnames CUI nome = tmp.nome )
    • Se non si dispone di un UNICO vincolo, credo che questo approccio potrebbe ancora portare a voci duplicate nel raro caso di una condizione di competizione: una voce duplicata avrebbe potuto essere inserito in una query parallele tra di SELEZIONARE sottoquery e la tua query di INSERIMENTO. Anche se, io non l’ho provato e mi piacerebbe essere smentito.
    • Questa soluzione crea errori quando due dei valori si tenta di inserire sono le stesse come SELEZIONARE ‘3’, ‘1’, ‘1’. Per evitare questo, dare ad ogni valore di un alias come SELEZIONARE ‘3’ COME, ‘1’ B, ‘1’ C.
    • Che cosa succede se abbiamo due colonne, con gli stessi valori, ad esempio SELECT * FROM (SELECT ‘Giovanni’, ‘Doe’, ‘022’,’022′) ?

  2. 273

    INSERIRE vieta DOVE nella sintassi.

    Cosa si può fare: creare un UNIQUE INDEX sul campo deve essere univoco (name), quindi:

    • normale INSERT (e gestire l’errore se il nome esiste già)
    • INSERT IGNORE (che fallisce silenziosamente causare un avviso (invece di errore) se il nome esiste già)
    • INSERT ... ON DUPLICATE KEY UPDATE (che eseguirà il UPDATE alla fine se il nome esiste già, vedere la documentazione)
    • quando l’INSERTO è ignorato valore auto_increment aumenta…
    • INSERT IGNORE lavorato per me. Esso è l’opzione migliore, esp. per i grandi set di dati.
    • se è necessario per ottenere il messaggio di avviso è possibile poi fare mysql> show warnings\G
    • ime, il messaggio di avviso, in caso di INSERT IGNORE, corrisponde la regex di ^Duplicate entry '.+' for key '.+'$
    • se si utilizza insert ignore, è ignorare gli altri errori che possono verificarsi a causa doppioni?
    • Non so largo della parte superiore della mia testa, ma questo non dovrebbe influire non correlate errori.
    • Sì, lo farà. Questo è un grosso problema con l’utilizzo di INSERT IGNORE IMO.
    • Nota: questa risposta potrebbe non essere la scelta migliore se una delle colonne l’unico vincolo di chiave è nullable! Il vincolo di opere, ma si potrebbe aspettare gli altri risultati 🙂 Inserimento (“John Doe”, NULL), (“John Doe”, NULL), i risultati in due righe, anche se entrambi i campi sono insieme in un unico vincolo di chiave. Vedi anche stackoverflow.com/questions/4081783/unique-key-with-nulls

  3. 51

    Lavorato :

    INSERT INTO users (full_name, login, password) 
      SELECT 'Mahbub Tito','tito',SHA1('12345') FROM DUAL
    WHERE NOT EXISTS 
      (SELECT login FROM users WHERE login='tito');
    • Questo funziona solo per Oracle database giusto? en.wikipedia.org/wiki/DUAL_table Questa domanda è in particolare su MySQL !
    • Non posso testare in un database Oracle. Si sta lavorando bene in MySQL e testato.
    • Ho imparato dal “doppio” è disponibile in MySQL, ma non in MariaDB, l’open source ramo di MySQL
    • Da @HighlyIrregular il link su wikipedia “e Diversi altri database (Microsoft SQL Server, MySQL, PostgreSQL, SQLite, e Teradata) consentono di omettere la clausola del tutto, se non è necessario. Questo evita la necessità per ogni tabella fittizia.” La stessa query sembra funzionare in MySql (5.7, se quello che conta) senza il DUAL.
    • Testato e funziona bene in MariaDB! Grazie @MahbubTito
  4. 25

    MySQL fornisce un molto carino soluzione :

    REPLACE INTO `table` VALUES (5, 'John', 'Doe', SHA1('password'));

    Molto facile da usare dal momento che hanno dichiarato una chiave primaria univoca (qui con valore 5).

    • Ma si aggiorna il record (guardate le righe interessate)
    • per evitare che avete bisogno di creare un indice univoco, maggiori info qui:link
    • Molto pericoloso. Non tentare se non si sa cosa si sta facendo. In realtà, non tentare affatto. Controlla le altre risposte.
    • Nota, questo ELIMINA tutte le righe corrispondenti e quindi re-inserisce, più sicura la versione di questo stesso comportamento è per l’uso SU DUPLICTE TASTO UPDATE, che aggiorna combinando tra loro le righe, piuttosto che cancellare e re-inserimento di loro: dev.mysql.com/doc/refman/5.7/en/replace.html
  5. 19
    INSERT IGNORE INTO `mytable`
    SET `field0` = '2',
    `field1` = 12345,
    `field2` = 12678;

    Qui mysql query, che inserire i record, se non esiste e ignorare esistenti record simili.

    ----Untested----
    • non ha funzionato per me, INSERT IGNORE IN emAssignedTagsForEvent SET eventId=’2′,defaultTagId=’1′;
    • Si presenta come un impasto di inserire e aggiornare la sintassi. Vuoi dire INSERT IGNORE INTO `mytable` (`field0`, `field1`, `field2`) values ('2', 12345, 12678);?
    • Da MySQL Manuale dev.mysql.com/doc/refman/5.7/en/insert.html INSERIRE sintassi: può essere INSERT [...] IGNORE INTO mytable VALUES ... O INSERT [...] IGNORE INTO mytable SET col_name={expr | DEFAULT},...
    • “Se si utilizza la IGNORA modificatore, gli errori che si verificano durante l’esecuzione dell’istruzione INSERT vengono ignorati.”. Quindi, fondamentalmente, non hai risolto nulla.
  6. 16

    Si può facilmente utilizzare il seguente modo :

    INSERT INTO ... ON DUPLICATE KEY UPDATE ...

    In questo modo è possibile inserire qualsiasi nuovo raw e se si dispone di dati duplicati, sostituire specifica colonna ( migliori colonne è timestamp ).

    Per esempio :

    CREATE TABLE IF NOT EXISTS Devices (
      id         INT(6)       NOT NULL AUTO_INCREMENT,
      unique_id  VARCHAR(100) NOT NULL PRIMARY KEY,
      created_at VARCHAR(100) NOT NULL,
      UNIQUE KEY unique_id (unique_id),
      UNIQUE KEY id (id)
    )
      CHARACTER SET utf8
      COLLATE utf8_unicode_ci;
    
    INSERT INTO Devices(unique_id, time) 
    VALUES('$device_id', '$current_time') 
    ON DUPLICATE KEY UPDATE time = '$current_time';
    • funziona bene! Grazie!
  7. 12

    Se davvero non si può ottenere un indice univoco nella tabella, si potrebbe provare…

    INSERT INTO table_listnames (name, address, tele)
        SELECT 'Rupert', 'Somewhere', '022'
            FROM some_other_table
            WHERE NOT EXISTS (SELECT name
                                  FROM table_listnames
                                  WHERE name='Rupert')
            LIMIT 1;
  8. 11

    Per superare un problema simile, ho fatto la tabella io sono l’inserimento di avere un’unica colonna. Usando il tuo esempio, nella creazione avrei qualcosa come:

    name VARCHAR(20),
    UNIQUE (name)

    e quindi utilizzare la seguente query quando inserendo in esso:

    INSERT IGNORE INTO train
    set table_listnames='Rupert'
  9. 9

    Questa query funziona bene:

    INSERT INTO `user` ( `username` , `password` )
        SELECT * FROM (SELECT 'ersks', md5( 'Nepal' )) AS tmp
        WHERE NOT EXISTS (SELECT `username` FROM `user` WHERE `username` = 'ersks' 
        AND `password` = md5( 'Nepal' )) LIMIT 1

    E si può creare la tabella con la seguente query:

    CREATE TABLE IF NOT EXISTS `user` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `username` varchar(30) NOT NULL,
        `password` varchar(32) NOT NULL,
        `status` tinyint(1) DEFAULT '0',
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    Nota: per Creare la tabella utilizzando la seconda query prima di tentare di usare prima query.

  10. 4

    Brian Hooper :
    Hai quasi raggiunto il punto, ma si dispone di un errore di synatx. Inserisci il tuo non funzionerà mai. L’ho testato sul mio database e qui è la risposta giusta:

    INSERT INTO podatki (datum,ura,meritev1,meritev1_sunki,impulzi1,meritev2,meritev2_sunki,impulzi2)
                SELECT '$datum', '$ura', '$meritve1','$sunki1','$impulzi1','$meritve2','$sunki2','$impulzi2'
                    FROM dual
                    WHERE NOT EXISTS (SELECT datum,ura
                                          FROM podatki
                                          WHERE datum='$datum' and ura='$ura'

    Io sto dando il mio esempio di y tabella. Inserisci è quasi lo stesso come Bian Hooper ha scritto, tranne che ho messo l’selezionare DUAL ont da un’altra tabella.
    Cind saluti, Ivan

    • Che cos’è questo “doppio”? Una parola chiave predefinita? Io non vedo la differenza da quello che Brian ha detto… EDIT: Ulteriori indagini, si sono mescolate e contraddittoria (?) risultati. Mentre SQL DUAL tabella dice che MySQL non supporta il DUAL tabelle, MySQL manuale, dice che lo fa. Il mio test mostrano che non è così, poiché dà unknown table status: TABLE_TYPE messaggi, anche se la query ha prodotto un risultato. Probabilmente perchè MySQL non richiedono la FROM DUAL clausola?
  11. 4
    insert into customer_keyskill(customerID, keySkillID)
    select  2,1 from dual
    where not exists ( 
        select  customerID  from customer_keyskill 
        where customerID = 2 
        and keySkillID = 1 )
    • dual è una tabella Oracle MySQL non
  12. 3

    Inserimento di Aggiornamento del risultato.
    È possibile definire il nome di colonna in colonna principale o set è unico.

    • cosa è che non posso impostare come primaria o unica
  13. 3

    Ho avuto un problema, e il metodo di Mike consiglia lavorato a parte, ho avuto un errore Dublicate Colonna name = ‘0’ e cambiato la sintassi della query come questa`

         $tQ = "INSERT  INTO names (name_id, surname_id, sum, sum2, sum3,sum4,sum5) 
                    SELECT '$name', '$surname', '$sum', '$sum2', '$sum3','$sum4','$sum5' 
    FROM DUAL
                    WHERE NOT EXISTS (
                    SELECT sum FROM names WHERE name_id = '$name' 
    AND surname_id = '$surname') LIMIT 1;";

    Il problema era con i nomi di colonna. sum3 è stato pari a sum4 e mysql throwed dublicate i nomi di colonna, e ho scritto il codice in questo sintassi e ha funzionato perfettamente,

    • Life-saver, grazie amico!
    • Il proprietario del DUAL è SYS SYS possiede il dizionario dei dati, quindi DOPPIO fa parte del dizionario di dati.) ma DUAL può essere utilizzato da qualsiasi utente. La tabella ha un solo VARCHAR2(1) colonna denominata MANICHINO che ha un valore di ‘X’. MySQL permette di DUAL essere indicato come una tabella nella query che non hanno bisogno di dati dalle tabelle.Il DUAL tabella è un particolare di una riga, una colonna della tabella presente di default in Oracle e altre installazioni di database. In Oracle, la tabella ha un solo VARCHAR2(1) colonna denominata MANICHINO che ha un valore di ‘X’. È adatto per l’utilizzo nella scelta di un pseudo colonna come SYSDATE o UTENTE.
  14. 3

    Ho avuto un problema simile e ho bisogno di inserire più se non esistente. Così dagli esempi sopra sono arrivato a questa combinazione… è qui solo nel caso in cui qualcuno avrebbe bisogno.

    Avviso:
    Ho avuto definire il nome ovunque come MSSQL richiesto… MySQL funziona con * troppo.

    INSERT INTO names (name)
    SELECT name
    FROM
    (
      SELECT name
      FROM
      (
         SELECT 'Test 4' as name
      ) AS tmp_single
      WHERE NOT EXISTS
      (
         SELECT name FROM names WHERE name = 'Test 4'
      )
      UNION ALL
      SELECT name
      FROM
      (
         SELECT 'Test 5' as name
      ) AS tmp_single
      WHERE NOT EXISTS
      (
         SELECT name FROM names WHERE name = 'Test 5'
      )
    ) tmp_all;

    MySQL:
    CREARE LA TABELLA names (
    OID int(11) NOT NULL AUTO_INCREMENT,
    name varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (OID),
    LA CHIAVE UNICA name_UNIQUE (name)
    ) ENGINE=InnoDB AUTO_INCREMENT=1;

    o

    MSSQL:
    CREATE TABLE [nomi] (
    [OID] INT IDENTITY (1, 1) NOT NULL,
    [nome] di tipo NVARCHAR (32) NOT NULL,
    PRIMARY KEY CLUSTERED ([OID] ASC)
    );
    CREARE un INDICE cluster UNIVOCO [Index_Names_Name] SU [nome]([nome] ASC);

  15. 1

    Questa query può essere utilizzata nel codice PHP.

    Ho un ID di colonna di questa tabella, quindi ho bisogno di verificare per la duplicazione di tutte le colonne tranne questa colonna ID:

    #need to change values
    SET @goodsType = 1, @sybType=5, @deviceId = asdf12345SDFasdf2345;    
    
    
    INSERT INTO `devices` (`goodsTypeId`, `goodsId`, `deviceId`) #need to change tablename and columnsnames
    SELECT * FROM (SELECT @goodsType, @sybType, @deviceId) AS tmp
    WHERE NOT EXISTS (
        SELECT 'goodsTypeId' FROM `devices` #need to change tablename and columns names
        WHERE `goodsTypeId` = @goodsType
            AND `goodsId` = @sybType
            AND `deviceId` = @deviceId
    ) LIMIT 1;

    e ora nuovo elemento viene aggiunto solo in caso di c’è non esiste riga con i valori configurati in SET stringa

Lascia un commento