如何使用 PHP 同步两个数据库表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/626192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i synchronize two database tables with PHP?
提问by JD Isaacks
I need to use PHP to copy data from one MySQL database to another.
我需要使用 PHP 将数据从一个 MySQL 数据库复制到另一个。
I can build and array of all the values to go into the other database but first I want to make sure the database has the correct fields before inserting.
我可以构建所有值的数组以进入另一个数据库，但首先我想确保数据库在插入之前具有正确的字段。
For example say I am going to be copying data from tableA to tableB.
例如，假设我要将数据从 tableA 复制到 tableB。
I can set up tableB to look just like tableA but in the future I may add columns to tableA and forget to add them to tableB, then my PHP script will try to insert data into a column that doesn't exist in tableB and it will fail.
我可以将 tableB 设置为看起来像 tableA，但将来我可能会向 tableA 添加列而忘记将它们添加到 tableB，然后我的 PHP 脚本将尝试将数据插入到 tableB 中不存在的列中，它会失败。
So what I want to do is compare tableA to tableB and any columns that tableA has that tableB doesn't have add them to tableB.
所以我想要做的是将 tableA 与 tableB 进行比较，并且 tableA 具有 tableB 的任何列都没有将它们添加到 tableB。
Can anyone tell me how to do this?
谁能告诉我如何做到这一点？
回答by JD Isaacks
Thanks everyone, based on all your help I was able to write a PHP class that copies any columns from table A to table B if they are not already there:
谢谢大家，根据您的帮助，我能够编写一个 PHP 类，将表 A 中的任何列复制到表 B（如果它们尚不存在）：
class MatchTable
{
var $_table_one_name;
var $_table_two_name;
var $_table_one_db_user;
var $_table_one_db_pass;
var $_table_one_db_host;
var $_table_one_db_name;
var $_table_two_db_user;
var $_table_two_db_pass;
var $_table_two_db_host;
var $_table_two_db_name;
var $_table_one_columns = array();
var $_table_two_columns = array();
var $_table_one_types = array();
var $_table_two_types = array();
var $_table_one_link;
var $_table_two_link;
var $_isTest;
function MatchTable($isLive = true)
{
$this->_isTest = !$isLive;
}
function matchTables($table1, $table2)
{
$this->_table_one_name = $table1;
$this->_table_two_name = $table2;
if(isset($this->_table_one_db_pass))
{
$this->db_connect('ONE');
}
list($this->_table_one_columns,$this->_table_one_types) = $this->getColumns($this->_table_one_name);
if(isset($this->_table_two_db_pass))
{
$this->db_connect('TWO');
}
list($this->_table_two_columns,$this->_table_two_types) = $this->getColumns($this->_table_two_name);
$this->addAdditionalColumns($this->getAdditionalColumns());
}
function setTableOneConnection($host, $user, $pass, $name)
{
$this->_table_one_db_host = $host;
$this->_table_one_db_user = $user;
$this->_table_one_db_pass = $pass;
$this->_table_one_db_name = $name;
}
function setTableTwoConnection($host, $user, $pass, $name)
{
$this->_table_two_db_host = $host;
$this->_table_two_db_user = $user;
$this->_table_two_db_pass = $pass;
$this->_table_two_db_name = $name;
}
function db_connect($table)
{
switch(strtoupper($table))
{
case 'ONE':
$host = $this->_table_one_db_host;
$user = $this->_table_one_db_user;
$pass = $this->_table_one_db_pass;
$name = $this->_table_one_db_name;
$link = $this->_table_one_link = mysql_connect($host, $user, $pass, true);
mysql_select_db($name) or die(mysql_error());
break;
case 'TWO';
$host = $this->_table_two_db_host;
$user = $this->_table_two_db_user;
$pass = $this->_table_two_db_pass;
$name = $this->_table_two_db_name;
$link = $this->_table_two_link = mysql_connect($host, $user, $pass, true);
mysql_select_db($name) or die(mysql_error());
break;
default:
die('Improper parameter in MatchTable->db_connect() expecting "one" or "two".');
break;
}
if (!$link) {
die('Could not connect: ' . mysql_error());
}
}
function getColumns($table_name)
{
$columns = array();
$types = array();
$qry = 'SHOW COLUMNS FROM '.$table_name;
$result = mysql_query($qry) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
$field = $row['Field'];
$type = $row['Type'];
/*
$column = array('Field' => $field, 'Type' => $type);
array_push($columns, $column);
*/
$types[$field] = $type;
array_push($columns, $field);
}
$arr = array($columns, $types);
return $arr;
}
function getAdditionalColumns()
{
$additional = array_diff($this->_table_one_columns,$this->_table_two_columns);
return $additional;
}
function addAdditionalColumns($additional)
{
$qry = '';
foreach($additional as $field)
{
$qry = 'ALTER TABLE '.$this->_table_two_name.' ADD '.$field.' '.$this->_table_one_types[$field].'; ';
if($this->_isTest)
{
echo $qry.'<br><br>';
}
else
{
mysql_query($qry) or die(mysql_error());
}
}
}
/**
* End of Class
*/
}
回答by Sakkle
I'm not a 100% sure this is what you're looking for but I used to do a little database maintenance a while back. We needed a way to make sure the devDB and the prodDB were identical in structure and I tracked down this nifty little tool. The tool creates a sql-alter-script that can be run on the database you would like to patch. It written in perl so I guess it should work cross platform but I have only tried it on linux.
我不是 100% 确定这就是你要找的东西，但我曾经做过一些数据库维护。我们需要一种方法来确保 devDB 和 prodDB 在结构上是相同的，我找到了这个漂亮的小工具。该工具会创建一个 sql-alter-script，该脚本可以在您想要修补的数据库上运行。它是用 perl 编写的，所以我想它应该可以跨平台工作，但我只在 linux 上尝试过。
The tool is called mySQLdiff, is freeware and can be downloaded at www.mysqldiff.org.
该工具称为 mySQLdiff，是免费软件，可从www.mysqldiff.org下载。
回答by Gabriel Solomon
you could look into some phpclasses that do this for you http://www.phpclasses.org/search.html?words=mysql+sync&x=0&y=0&go_search=1
您可以查看一些为您执行此操作的 phpclasses http://www.phpclasses.org/search.html?words=mysql+sync&x=0&y=0&go_search=1
回答by Scott
Probably the easiest way to do this would be
可能最简单的方法是
$sql = "SELECT * FROM tableA WHERE 1"
$results = mysql_fetch_assoc($sql);
$sql = "truncate table tableB";
// run truncate
foreach($result as $update){
$sql = "Insert into table b VALUES(....)"
// run insert
}
But you need to be extremely careful here. Make sure that the only process that can write to tableB is the one that does the copy from tableA other wise you will have lost data. Also make sure that nothing can write to tableA once this process has begun.
但是在这里你需要非常小心。确保唯一可以写入 tableB 的进程是从 tableA 进行复制的进程，否则您将丢失数据。还要确保在此过程开始后没有任何内容可以写入 tableA。
The best practice for this would be to not do this in php but rather through mysql replication.
最好的做法是不要在 php 中执行此操作，而是通过 mysql 复制。
回答by jacekll
Use TOAD for MySQL Schema compare tool, it will look at the tables, show you the differences visually and generate SQL queries to synchronize the tables structure. Also it does data compare.
使用 TOAD for MySQL Schema 比较工具，它将查看表，直观地向您展示差异并生成 SQL 查询以同步表结构。它也做数据比较。
回答by vartec
SHOW COLUMNS FROM ?table?
回答by jonstjohn
You could write a function that returns the columns from the table such as this:
您可以编写一个函数来返回表中的列，例如：
function columns($table) {
$columns = array();
$sql = "desc $table";
$q = mysql_query($sql);
while ($r = mysql_fetch_array($q)) {
$columns[] = $r[0];
}
return $columns;
}
Next, you can compare the columns, from the two tables:
接下来，您可以比较两个表中的列：
function tables_different($table1, $table2) {
$cols1 = columns($table1);
$cols2 = columns($table2);
return count(array_diff($cols1, $cols2)) ? true : false;
}
Now, you can integrate the tables_different() function into your data transfer script, running it each time to make sure the tables are the same.
现在，您可以将 tables_different() 函数集成到数据传输脚本中，每次都运行它以确保表相同。
Of course, you can make this fancier and have it tell you which columns are different between the two tables, making it more useful to synchronizing them.
当然，您可以让这个更有趣，并让它告诉您两个表之间哪些列不同，从而更有用地同步它们。
回答by alexpopescu
This is a very complex task and as far as I know many have tried to solved it so far (unfortunately I am not aware of any 100% guaranteed solution).
这是一项非常复杂的任务，据我所知，到目前为止，很多人都试图解决它（不幸的是，我不知道任何 100% 保证的解决方案）。
I'd say that before jumping to implement your own solution you should take a look and read about the challenges you'll be facing by reading about Schema evolution, Schema Refactoring, Schema versioning, etc.
我想说的是，在开始实施您自己的解决方案之前，您应该通过阅读架构演化、架构重构、架构版本控制等来了解并了解您将面临的挑战。
Afterwards, you can take a look at PHP MDB2_Schema(some more documentation in this article).
之后，你可以看看PHP MDB2_Schema（这在一些文件的文章）。
If you are not tied to PHP then you may also take a look at Sundogwhich provides a set of advanced schema refactorings.
如果您不依赖 PHP，那么您还可以查看Sundog，它提供了一组高级模式重构。
Once you get a reliable schema migration tool for your app, migrating data will be just a trivial task.
一旦您为您的应用程序获得了可靠的架构迁移工具，迁移数据将只是一项微不足道的任务。
./alex
./亚历克斯
