que - registros diferentes entre dos tablas sql
comparar las diferencias entre dos tablas en mysql (5)
Lo mismo que Oracle diff: cómo comparar dos tablas? excepto en mysql.
Supongamos que tengo dos tablas, t1 y t2, que son idénticas en el diseño pero que pueden contener datos diferentes.
¿Cuál es la mejor manera de diferenciar estas dos tablas?
Para ser más precisos, estoy tratando de encontrar una consulta SQL simple que me diga si los datos de una fila en t1 son diferentes de los datos de la fila correspondiente en t2
Parece que no puedo usar la intersección ni el signo menos. Cuando intento
SELECT * FROM robot intersect SELECT * FROM tbd_robot
Obtengo un código de error:
[Código de error: 1064, estado de SQL: 42000] Tiene un error en la sintaxis de SQL; revise el manual que corresponde a su versión del servidor MySQL para la sintaxis correcta para usar cerca de ''SELECCIONAR * FROM tbd_robot'' en la línea 1
¿Estoy haciendo algo sintácticamente incorrecto? Si no, ¿hay otra consulta que pueda usar?
Editar: Además, estoy consultando a través de una versión gratuita DbVisualizer. No estoy seguro de si eso podría ser un factor.
En base a la respuesta de Haim, creé un código PHP para probar y mostrar todas las diferencias entre dos bases de datos. Esto también se mostrará si una tabla está presente en las bases de datos de origen o de prueba. Tienes que cambiar con tus detalles el contenido de <> variables.
<?php
$User = "<DatabaseUser>";
$Pass = "<DatabasePassword>";
$SourceDB = "<SourceDatabase>";
$TestDB = "<DatabaseToTest>";
$link = new mysqli( "p:". "localhost", $User, $Pass, "" );
if ( mysqli_connect_error() ) {
die(''Connect Error (''. mysqli_connect_errno() .'') ''. mysqli_connect_error());
}
mysqli_set_charset( $link, "utf8" );
mb_language( "uni" );
mb_internal_encoding( "UTF-8" );
$sQuery = ''SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="''. $SourceDB .''";'';
$SourceDB_Content = query( $link, $sQuery );
if ( !is_array( $SourceDB_Content) ) {
echo "Table $SourceDB cannot be accessed";
exit(0);
}
$sQuery = ''SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="''. $TestDB .''";'';
$TestDB_Content = query( $link, $sQuery );
if ( !is_array( $TestDB_Content) ) {
echo "Table $TestDB cannot be accessed";
exit(0);
}
$SourceDB_Tables = array();
foreach( $SourceDB_Content as $item ) {
$SourceDB_Tables[] = $item["TABLE_NAME"];
}
$TestDB_Tables = array();
foreach( $TestDB_Content as $item ) {
$TestDB_Tables[] = $item["TABLE_NAME"];
}
//var_dump( $SourceDB_Tables, $TestDB_Tables );
$LookupTables = array_merge( $SourceDB_Tables, $TestDB_Tables );
$NoOfDiscrepancies = 0;
echo "
<table border=''1'' width=''100%''>
<tr>
<td>Table</td>
<td>Found in $SourceDB (". count( $SourceDB_Tables ) .")</td>
<td>Found in $TestDB (". count( $TestDB_Tables ) .")</td>
<td>Test result</td>
<tr>
";
foreach( $LookupTables as $table ) {
$FoundInSourceDB = in_array( $table, $SourceDB_Tables ) ? 1 : 0;
$FoundInTestDB = in_array( $table, $TestDB_Tables ) ? 1 : 0;
echo "
<tr>
<td>$table</td>
<td><input type=''checkbox'' ". ($FoundInSourceDB == 1 ? "checked" : "") ."></td>
<td><input type=''checkbox'' ". ($FoundInTestDB == 1 ? "checked" : "") ."></td>
<td>". compareTables( $SourceDB, $TestDB, $table ) ."</td>
</tr>
";
}
echo "
</table>
<br><br>
No of discrepancies found: $NoOfDiscrepancies
";
function query( $link, $q ) {
$result = mysqli_query( $link, $q );
$errors = mysqli_error($link);
if ( $errors > "" ) {
echo $errors;
exit(0);
}
if( $result == false ) return false;
else if ( $result === true ) return true;
else {
$rset = array();
while ( $row = mysqli_fetch_assoc( $result ) ) {
$rset[] = $row;
}
return $rset;
}
}
function compareTables( $source, $test, $table ) {
global $link;
global $NoOfDiscrepancies;
$sQuery = "
SELECT column_name,ordinal_position,data_type,column_type FROM
(
SELECT
column_name,ordinal_position,
data_type,column_type,COUNT(1) rowcount
FROM information_schema.columns
WHERE
(
(table_schema=''$source'' AND table_name=''$table'') OR
(table_schema=''$test'' AND table_name=''$table'')
)
AND table_name IN (''$table'')
GROUP BY
column_name,ordinal_position,
data_type,column_type
HAVING COUNT(1)=1
) A;
";
$result = query( $link, $sQuery );
$data = "";
if( is_array( $result ) && count( $result ) > 0 ) {
$NoOfDiscrepancies++;
$data = "<table><tr><td>column_name</td><td>ordinal_position</td><td>data_type</td><td>column_type</td></tr>";
foreach( $result as $item ) {
$data .= "<tr><td>". $item["column_name"] ."</td><td>". $item["ordinal_position"] ."</td><td>". $item["data_type"] ."</td><td>". $item["column_type"] ."</td></tr>";
}
$data .= "</table>";
return $data;
}
else {
return "Checked but no discrepancies found!";
}
}
?>
Encontré otra solución en este enlace
SELECT MIN (tbl_name) AS tbl_name, PK, column_list
FROM
(
SELECT '' source_table '' as tbl_name, S.PK, S.column_list
FROM source_table AS S
UNION ALL
SELECT ''destination_table'' as tbl_name, D.PK, D.column_list
FROM destination_table AS D
) AS alias_table
GROUP BY PK, column_list
HAVING COUNT(*) = 1
ORDER BY PK
Puede construir la intersección manualmente usando UNION. Es fácil si tiene algún campo único en ambas tablas, por ejemplo, ID:
SELECT * FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)
UNION
SELECT * FROM T2
WHERE ID NOT IN (SELECT ID FROM T1)
Si no tiene un valor único, puede expandir el código anterior para verificar todos los campos en lugar de solo el ID, y usar AND para conectarlos (por ejemplo, ID NOT IN (...) Y OTHER_FIELD NOT IN (. ..) etc.)
INTERSECT necesita ser emulado en MySQL :
SELECT ''robot'' AS `set`, r.*
FROM robot r
WHERE ROW(r.col1, r.col2, …) NOT IN
(
SELECT *
FROM tbd_robot
)
UNION ALL
SELECT ''tbd_robot'' AS `set`, t.*
FROM tbd_robot t
WHERE ROW(t.col1, t.col2, …) NOT IN
(
SELECT *
FROM robot
)
Editar: se agregó `alrededor de las palabras: establecer
select t1.user_id,t2.user_id
from t1 left join t2 ON t1.user_id = t2.user_id
and t1.username=t2.username
and t1.first_name=t2.first_name
and t1.last_name=t2.last_name
prueba esto. Esto comparará su tabla y encontrará todos los pares que coincidan, si no coinciden, devuelva NULL a la izquierda.