05 mayo 2009

MySQL REPLACE / INSERT ... ON DUPLICATE KEY

Pequeño tip para MySQL: En lugar de escribir "REPLACE ...", también podemos usar un "INSERT ... ON DUPLICATE KEY ... "
En un suspiro, resulta que el enunciado "REPLACE" siempre se ejecuta como un "DELETE" seguido de un "INSERT" cuando ya existe una fila con esa clave primaria o una índice unique. Por otro lado, el "INSERT ... ON DUPLICATE KEY" se ejecuta como un "INSERT" común y, si la clave primaria o el índice unique están duplicados, realiza un "UPDATE".
Un ejemplo extraído de la documentación: Si la columna "a" se define como UNIQUE y contiene el valor "1"
  • "INSERT INTO table (a,b,c) VALUES (1,2,3)
    ON DUPLICATE KEY UPDATE c=c+1;"
  • "UPDATE table SET c=c+1 WHERE a=1;"
Las dos sentencias hacen exactamente lo mismo. Hay que tener cuidado que si el WHERE devuelve varias filas, sólo la primera se actualiza.

Hasta la próxima!

9 comentarios:

  1. Hola Javier:
    tal y como dice la documentación:

    "Si la columna "a" se define como UNIQUE y contiene el valor "1"
    "INSERT INTO table (a,b,c) VALUES (1,2,3)
    ON DUPLICATE KEY UPDATE c=c+1;"

    lo que hay que evitar es que "a" se repita, pero al dar la clave duplicada, lo que la sentencia modifica es "c" y no "a" que es la que crea el conflicto.

    O no entiendo nada, o debería ser:

    INSERT INTO table (a,b,c) VALUES (1,2,3)
    ON DUPLICATE KEY UPDATE a=a+1;

    Me lo explicás en plain spanish ;-)

    Saludos!

    ResponderEliminar
  2. Leo, si te fijas el Update, en realidad el ejemplo viene a ser "insertar tales valores y si la clave 'a' esta duplicada, entonces el valor de c es c+1". No cambia la clave, lo que cambia es el valor de 'c'.

    ResponderEliminar
  3. Hola a tod@s.
    Hace tres días enteros que me he metido a utilizar sql, con lo cual ya podéis imaginar lo que
    entiendo de esto = 0

    Bueno, empecé por instalar el Server.
    Luego me volví loco para que al final de montón de descargas, una me funciono y ya insertaba datos y búsquedas.
    El siguiente paso era que borrara las entradas, fue rápido.

    PERO a la hora de que quiero reemplazar una entrada completa, NADA no hay forma de conseguir hacer que se reemplacen los diferentes datos.
    Hace mas de 1 DIA que estoy buscando en mil chacts de todo esto, pero no encuentro un solo sitio que hablen cristiano.
    Todo demasiado técnico, o cuatro cientos millones de líneas de texto, de las que no te enteras de nada.
    Aquí pongo lo que creo puede ayudar a entender lo que no se solucionar.
    CREATE TABLE `datos` (`id` int(11) NOT NULL auto_increment,
    `D` text collate utf8_spanish_ci NOT NULL,
    `Fu` text collate utf8_spanish_ci NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci AUTO_INCREMENT=0 ;INSERT INTO `datos` (`id`, `D`, `Fu`) VALUES
    (1, 'entrada1', '1'),
    (2, 'ana', '2'),
    (3, 'david', '3'),
    (4, 'linea4', '4');

    php
    include ("global.php");
    SDBhost = "localhost";
    SDBuser = "root";
    SDBpass = "";
    SDBName = "busca";
    Stable = "datos";
    SDBConn = mysql_connect(SDBhost,SDBuser,SDBpass) or die("error en conexión: " . mysql_error());
    mysql_select_db(SDBName, SDBConn) or die("error al seleccionar la base: " . mysql_error());
    switch(Saccion){
    case 'regi':
    echo SD." ".SFu;
    Sconsulta="INSERT INTO datos (`D`, `Fu`) VALUES('SD','SFu')";
    Sresultado=mysql_query(Sconsulta)or die ("error en la consulta". mysql_error());
    break;

    case 'reem':
    echo SD." ".SFu;
    Sid=5;
    Sconsulta="DELETE FROM datos WHERE (id=Sid)";

    Sresultado=mysql_query(Sconsulta)or die ("error en la consulta". mysql_error());
    break;

    case 'busca':
    Sconsulta="SELECT * FROM datos WHERE `D`='Svalor';";
    Sresultado=mysql_query(Sconsulta)or die ("error en la consulta". mysql_error());
    echo "&buscar=";
    while(Sfila=mysql_fetch_row(Sresultado))
    echo "".Sfila[2]."";
    break;



    Sconsulta="REPLACE FROM datos WHERE (id=Sid)";

    He probado diferentes formas que he ido leyendo, pero nada de nada.

    Explico que es lo que quiero conseguir.

    (1, '2', '3')

    El 1 es el ID que preferiría que no se incremente, lo quite pero ya no se como poner mas filas desde el php.

    El 2. Donde quiero poner todo el contenido que hay en un xml, ya lo probé y me lo carga entero y leo perfectamente todos sus nodos.

    El 3. es para la búsqueda

    Todo esto lo hago desde flash8 para cargar paginas al flash con todos los contenidos que se editan online y los guarda en esa tabla.

    El usuario desde el admin que incorpora el flash, se encarga de moverlo todo he insertar todos los elementos de textos, botones, nuevas paginas, animaciones, fotos, formularios, banners, etc y el admin de guardar los textos y todas las posiciones de los elementos en un xml que luego se lo manda al php y a la db.

    Toda la edición online del flash, funciona perfectamente y todo lo guardo y llamo desde los xml, de manera que lo que hace mysql yo lo hago con los xml sin necesidad de DB.

    La incorporación de la db, es para incluir tiendas de con cantidad de productos.
    Con xml ya lo tengo, pero las posibilidades de búsqueda para grandes cantidades de información, esta claro que hay que utilizar mysql y en esa batalla estoy ahora.

    Alguien seria tal amable de escribirme esa línea/s para reemplazar los 3 campos por nuevos datos y si tiene alguna sugerencia para lo que quiero conseguir hacer.

    Si que agradeceré que el que lo haga, lo haga con comas y todos los detalles, ya que esta programación, es nueva para mi.

    Agradezco vuestras molestias.

    Un gran saludo para todos, este es un buen sitio.
    Jesús Bolea

    ResponderEliminar
  4. Jesús, a simple vista, te diría que necesitas aprender un poco más de programación antes de lanzarte a hacer estas cosas. Tal vez algún curso online o comprándote algún libro que te resulte ameno, pero hay varias cosas que ajustar antes de ponerte a integrar esas tecnologías.
    Que bueno lo que contas de XML! de todas maneras, las DB relacionales como MySQL no funcionan de la manera que creo vos te estás planteando. Saludos!

    ResponderEliminar
  5. Y si uno no quiere que mire si la llave primaria esta duplicada,
    si no otro campo de la tabla
    ejemplo
    insert into "afdotacion(id_empleado,dotacion,cantidad) values(73,'fff',10) ON DUPLICATE KEY UPDATE cantidad=10

    si yo quiero q dotacion sea el campo q no puede estar duplicado mientras el id_ empleado=73 y haga el update en cantidad
    agradezco me puedan ayudar

    por favorrrrrrr

    ResponderEliminar
  6. Hola Javier.
    Agradezco tus comentarios y decirte que ya lo solucione todo.

    En unos dias volvere y te dejare una dir desde la que se pueda ver el resultado de este trabajo y una demo para ti, para que veas algo que parece ser que no existe nada de esto en internet.

    Segun los expertor en flash, lo mio es imposible, pero yo lo hice realidad y nadie a visto nada igual.

    En breve te paso la dir y la demo.

    Gracias por tu atención.

    Jesús Bolea

    ResponderEliminar
  7. @Danna: Para solucionar tu problema hay un par de opciones sencillas:

    1) Definir que la columna "dotacion" tenga una restricción "unique", que hace fallar la consulta y vos lo arregles en tu aplicación.
    2) Definir un Trigger para la tabla, cuando se haga un INSERT o un UPDATE. Más información en http://dev.mysql.com/doc/refman/5.1/en/triggers.html

    De los dos métodos, yo prefiero el segundo, sobretodo porque después aclaras que no tiene que estar duplicado para un ID determinado. Escribiendo un trigger, mantenes la lógica en la base de datos, y tu aplicación no requiere cambios. Fijate y cualquier cosa escribi de nuevo. Saludos!

    ResponderEliminar
  8. Hola,

    Tengo un REPLACE en mysql, pero me arroja el siguiente error: Duplicate entry '2041858903928' for key 'PRIMARY'
    Segun lo que entiendo el REPLACE hace un DELETE y luego un INSERT por lo que no deberia suceder esto,
    me puedes ayudar?

    Saludos

    ResponderEliminar
  9. No probaste con hacer un "INSERT... ON DUPLICATE KEY UPDATE..."? No sé bien tampoco que queres solucionar con un UPDATE, sin más información es difícil aconsejarte algo. Podes encontrar más información en http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update y relacionados. Saludos!

    ResponderEliminar