Sincroniza tablas en MySQL

Sincroniza las filas desde una tabla origen hacia una tabla destino usando MySQL: agrega las filas que faltan; modifica las filas que hayan cambiado y elimina las filas que sobran.

Objetivo y requisitos

Deseamos que la tabla tclonica tenga exactamente los mismos datos que la tabla tmaestra. Supongamos que su estructura es exactamente igual (los campos e índices coinciden).

Los datos siempre lo vamos a copiar desde la tabla tmaestra hacia la tabla tclonica. Nunca en sentido inverso.

Una posible solución sería borrar todos los datos de tclonica y luego insertar todos los datos de tmaestra. Esta solución la vamos a descartar porque nuestro objetivo es hacer la mínima cantidad de operaciones posible.

Hay 2 campos que serán obligatorios:

  • Un campo id de tipo INT, que tome valores AUTO_INCREMENT y que sea PRIMARY KEY (o al menos UNIQUE KEY)
  • Un campo instante de tipo TIMESTAMP que por omisión sea CURRENT_TIMESTAMP y que al actualizar la fila tome el CURRENT_TIMESTAMP

1) Creación de las tablas

Para crear una nueva tabla si no existiese:

CREATE TABLE tmaestra (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 campo1 …,
 campo2 …,
 …
 instante TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Crea tclonica a partir de tmaestra

  1. Obtén la estructura de tmaestra:
    SHOW CREATE TABLE tmaestra;
  2. Cambiamos el nombre tmaestra por tclonica y ejecutamos la sentencia CREATE TABLE obtenida

Base de datos de ejemplo

En la base de datos bd_sincro.sql podrás ver como crear las tablas tmaestra y tclonica. Hay datos de ejemplo y unas pruebas de sincronización directa.

2) Agrega y modifica filas

Marca de tiempo al insertar o modificar la fila maestra

Para conocer que filas han cambiado desde la última vez añadiremos un campo a la tabla que indique el momento en que una fila fue insertada o modificada. A este campo lo llamaremos instante y lo podemos crear así:

ALTER TABLE tmaestra
  ADD COLUMN instante TIMESTAMP NOT NULL 
    DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP;

En la tabla tclonica también hay que añadir este campo:

ALTER TABLE tclonica
  ADD COLUMN instante TIMESTAMP NULL;

Obtén la última actualización efectuada en tclonica

Aquí se requiere que la tabla tclonica no cambie o que al menos el campo instante no tuviese valor predeterminado ni se actualizase al cambiar la fila.

SELECT COALESCE(MAX(instante), '0000-01-01 00:00:00') FROM tclonica;

Obtén las filas a actualizar en tclonica desde tmaestra

SELECT * FROM tmaestra WHERE instante >= 'YYYY-MM-DD hh:mm:ss';

Obtén las filas en JSON

Los datos obtenidos se podrían convertir a sentencias SQL de actualización o bien se podrían guardar en formato JSON:

SELECT JSON_OBJECT(
  'id', id, 
  'campo1', campo1, 
  'campo2', campo2, 
  … 
  'instante', instante) AS object
FROM tmaestra WHERE instante >= 'YYYY-MM-DD hh:mm:ss';

Resultado:

object
{"id": 5, "campo1": "cinco", "campo2": "pentágono", … "instante": "2019-07-17 07:10:29.000000"}
{"id": 6, "campo1": "seis", "campo2": "insecto", … "instante": "2019-07-17 07:10:29.000000"}
{"id": 7, "campo1": "siete", "campo2": "semana", … "instante": "2019-07-17 07:10:29.000000"}

Obtén el archivo JSON completo

También podemos obtener todo el JSON de un tirón:

SELECT JSON_PRETTY(JSON_ARRAYAGG(JSON_OBJECT( 
  'id', id, 
  'campo1', campo1, 
  'campo2', campo2, 
  … 
  'instante', instante))) AS json 
FROM tmaestra WHERE instante >= 'YYYY-MM-DD hh:mm:ss';

Resultado:

[
 {
   "id": 5,
   "campo1": "cinco",
   "campo2": "pentágono",
   …
   "instante": "2019-07-17 07:10:29.000000"
 },
 {
   "id": 6,
   "campo1": "seis",
   "campo2": "insecto",
   …
   "instante": "2019-07-17 07:10:29.000000"
 },
 {
   "id": 7,
   "campo1": "siete",
   "campo2": "semana",
   …
   "instante": "2019-07-17 07:10:29.000000"
 },
 …
]

Sentencia para actualizar o modificar una fila en MySQL

Se trata de un híbrido entre una sentencia INSERT INTO y una sentencia UPDATE

INSERT INTO tclonica(id, campo1, campo2, …, instante) 
  VALUES (1, 'valor1', 'valor2', …, '2019-02-22 17:03:57') 
  ON DUPLICATE KEY UPDATE
    id = 1,
    campo1 = 'valor1', 
    campo2 = 'valor2', 
    …, 
    instante = '2019-02-22 17:03:57';

La parte INSERT INTO … VALUES … se utiliza para agregar nuevas filas. La parte ON DUPLICATE KEY UPDATE … sirve para modificar filas ya existentes. Esta parte se activaría sólo cuando la inserción fuese imposible porque crearía valores duplicados en las claves primaria o única de la tabla.

Por ejemplo: Si la fila con id=1 no existiese en la tabla se usaría la parte de INSERT INTO … VALUES …. Y si, al contrario, la fila id=1 ya existiese se activaría el ON DUPLICATE KEY UPDATE … porque el campo id al ser clave principal no puede tener duplicados.

3) Actualización directa

En el hipotético caso de que las dos tablas estuviesen accesibles desde el mismo servidor se podría hacer lo siguiente:

INSERT INTO tclonica(id, campo1, campo2, …, instante) 
  SELECT id, campo1, campo2, …, instante
    FROM tmaestra m
    WHERE m.instante >= (SELECT COALESCE(MAX(instante), '0000-01-01 00:00:00') FROM tclonica)
  ON DUPLICATE KEY UPDATE
    id = m.id,
    campo1 = m.campo1, 
    campo2 = m.campo2, 
    …, 
    instante = m.instante;

DELETE FROM tclonica WHERE id NOT IN (SELECT id FROM tmaestra);

Si cada tabla está en una base de datos diferente tan sólo hay que anteponer al nombre de la tabla, el nombre de la base de datos y un punto. Por ejemplo: bd_alpha.tmaestra y bd_beta.tclonica

4) Elimina filas

En este caso la marca de tiempo no servíria para nada ya que no podemos tener ningún dato de una fila borrada.

Rangos de id eliminados

El procedimiento que vamos a seguir es obtener el rango de identificadores que no existen. Supongamos que en la tabla tmaestra hay los siguientes campos id:

5678101314

Los rangos de id inexistentes en tmaestra serían:

1-4911-1215-∞

Versión 1

Obtén todos los id inexistentes en tmaestra llamando al siguiente procedimiento almacenado:

CALL queryDeletedIdsV1('tmaestra', 'id');
start_idfinal_id
14
99
1112
152147483647

Para borrar todas estas filas en tclonica podemos hacer lo siguiente:

DELETE tclonica 
  FROM tclonica INNER JOIN tmp_deleted_ids
  WHERE id BETWEEN start_id AND final_id;

Para obtener los ids en JSON: [[1, 4], [9], [11, 12], [15, 2147483647]]

SELECT JSON_ARRAYAGG(IF(start_id = final_id, 
  JSON_ARRAY(start_id), JSON_ARRAY(start_id, final_id))) AS deleted_ids
FROM tmp_deleted_ids

Para obtener los ids como rangos: 1-4,9,11-12,15-2147483647

SELECT GROUP_CONCAT(IF(start_id = final_id, 
  start_id, CONCAT(start_id, '-', final_id))) AS deleted_ids
FROM tmp_deleted_ids

El problema de la función queryDeletedIdsV1 es que, cuando hay muchos saltos en la secuencia de ids, el cálculo se puede demorar mucho tiempo

Versión 2

Está versión es mucho más rápida que la anterior y da exactamente el mismo resultado:

CALL queryDeletedIds('tmaestra', 'id');

Para borrar estas filas en tclonica:

DELETE tclonica 
  FROM tclonica INNER JOIN (
    SELECT start_id, final_id 
    FROM tmp_deleted_start 
      INNER JOIN tmp_deleted_final USING(position)
  ) aa
  WHERE id BETWEEN start_id AND final_id;

Reinicia el autoincremento del campo id

La anterior versión 2 no es lo bastante rápida si la tabla es muy grande y tiene muchos huecos en la secuencia de ids. Si no existiesen dichos huecos el cálculo sería muchísimo más rápido.

Si tmaestra no es una tabla primaria de alguna posible tabla foránea, podríamos eliminar dichos huecos reiniciando el campo de autoincremento:

CALL resetAutoincrement('tmaestra', 'id');

Secuencia continua de ids

Podemos evitar el problema de los saltos en la secuencia de ids reiniciando AUTO_INCREMENT después de cada borrado de fila.

ALTER TABLE tmaestra AUTO_INCREMENT = 1;

Código auxiliar y PHP

Para automatizar esta sincronización entre tablas se puede usar PHP. A continuación muestro rutinas de código que podríamos necesitar.

Obtén el listado de tablas de la BD

SELECT table_name 
FROM information_schema.tables
WHERE table_schema = 'nombre_base_datos' 
  AND table_type = 'BASE TABLE';

Tipos de campo de una tabla

DESCRIBE nombre_tabla;

Codifica tipos binarios

Si el tipo de campo es BLOB o BINARY hay que codificarlo en hexadecimal para poderlos almacenar en JSON o SQL

<?php …
$value = '0x'.bin2hex($value);
… ?>

Y el proceso inverso es:

<?php …
$value = pack("H*", ltrim($value, '0x'));
… ?>

Genera código SQL

Se trata de un código extraído de una clase PHP para generar SQL de actualización y borrado.

<?php …
  private function sqlInsertOrUpdateSentence($table, $row, $meta) {
    $COMMA = ', ';
    $APOS = "'";
    $fields = $values = $assigns = [];
    $idField = $this->ids[$table];

    foreach ($row as $field=>$value) {

      if ($value=='') $digestValue = 'null';
      elseif (in_array($meta[$field], ['blob', 'binary'])) $digestValue = '0x'.bin2hex($value);      
      else $digestValue = $this->pdo->quote($value);

      $assigns[] = "`$field` = $digestValue";      
      $fields[] = "`$field`";
      $values[] = $digestValue;
    }
    $names = implode($COMMA, $fields);
    $insert = implode($COMMA, $values);
    $update = implode($COMMA, $assigns);

    return "INSERT INTO `$table`($names) VALUES ($insert) ON DUPLICATE KEY UPDATE $update";
  }
  private function sqlDeleteSentence($table, $duo) {
    $idField = $this->ids[$table];
    if ($duo[0] == $duo[1]) {
      return "DELETE FROM `$table` WHERE `$idField` = $duo[0]";
    } else {
      return "DELETE FROM `$table` WHERE `$idField` BETWEEN $duo[0] AND $duo[1]";
    }
  }
… ?>

Comentarios

Proinf.net, ©2003-2019 ci 3.1.10 (CC) Esta obra está bajo una licencia de Creative Commons Este software está sujeto a la CC-GNU GPL