将数据从“原始”数据传输到优化的 MySQL 表中带有 php 的 cron 中的表

Question

我正在与一家 MLS 房地产列表提供商 (RETS) 合作。每隔 48 小时，我们就会通过 cron 作业将数据从他们的服务器提取到 SQL 数据库。我负责编写一个 php 脚本，该脚本将在远程服务器的数据转储到我们的“原始”表中后运行。在这些原始表中，所有列都是 VARCHAR(255)，我们希望将数据移动到优化表中。在我将我的脚本发送给负责设置 cron 作业的人之前，我想知道是否有更有效的方法来做到这一点，这样我就不会显得愚蠢。

这就是我正在做的事情：

总共有 8 个表，其中 4 个是原始表，4 个是优化表 - 全部都在同一个数据库中。原始表列名称是非描述性的，例如 c1、c2、c2、c4 等。这是故意的，因为每列中的数据可能会发生变化。原始表列名称使用 php 映射到正确的优化表列，如下所示：

$tables['optimized_table_name1']['raw_table'] = 'raw_table_name1';
$tables['optimized_table_name1']['data_map'] = array(
    'c1'    =>  array( // <--- "c1" is the raw table column name
                    'column_name'   =>  'id',
                    // I use other values for table creation,
                    // but they don't matter to the question.
                    // Just explaining why the array looks like this
                    //'type'        =>  'VARCHAR',
                    //'max_length'  =>  45,
                    //'primary_key' =>  FALSE,
                    // etc.
                ),
    'c9'    =>  array('column_name' =>  'address'),
    'c25'   =>  array('column_name' =>  'baths'),
    'c2'    =>  array('column_name' =>  'bedrooms') //etc.
    );

我对 4 个表中的每一个执行相同的操作：SELECT * FROM 原始表，读取配置数组并创建一个巨大的 SQL 插入语句，TRUNCATE 优化表，然后运行 ​​INSERT 查询。

foreach ($tables as $table_name => $config):

$raw_table  =   $config['raw_table'];
$data_map   =   $config['data_map'];
$fields =   array();
$values =   array();
$count      =   0;

// Get the raw data and create an array mapped to the optimized table columns.
$query = mysql_query("SELECT * FROM dbname.{$raw_table}");

while ($row = mysql_fetch_assoc($query))
{
    // Reading column names from my config file on first pass
    // Setting up the array, will only run once per table
    if (empty($fields))
    {
        foreach ($row as $key => $val)
        {// Produces an array with the column names
            $fields[] = $data_map[$key]['column_name'];
        }
    }
    foreach ($row as $key => $val)
    {// Assigns data to an array to be imploded later
        $values[$count][] = $val;
    }
    $count++;
}

// Create the INSERT statement string
$insert = array();
$sql = "\nINSERT INTO `{$table_name}` (`".implode('`,`', $fields)."`) VALUES\n";
foreach ($values as $key => $vals)
{
    foreach ($vals as &$val)
    {
        // Escape the data
        $val = mysql_real_escape_string($val);
    }
    // Using implode for simplicity, could avoid the nested foreach if I wanted to
    $insert[] = "('".implode("','", $vals)."')";
}
$sql .= implode(",\n", $insert).";\n";

// TRUNCATE optimized table and run INSERT query here

endforeach;

它会产生这样的结果（只是更大 - 每个表最多大约 15,000 条记录，每个表一个插入语句）：

INSERT INTO `optimized_table_name1` (`id`,`beds`,`baths`,`town`) VALUES
('50300584','2','1','Fairfield'),
('87560584','3','2','New Haven'),
('76545584','2','1','Bristol');

现在我承认，我已经在 ORM 的羽翼下很长一段时间了，并且不喜欢我的香草mysql/php.ini这是一个非常简单的任务，我想让代码保持简单。

我的问题：

1. TRUNCATE/INSERT 方法是执行此操作的好方法吗？
2. 您认为我的代码有什么问题吗？我知道您看到嵌套的 foreach 循环并感到不寒而栗，但我希望代码尽可能小而干净，并避免大量混乱的字符串连接（以生成插入查询）。正如我所说，我也已经很长时间没有使用原生 PHP 函数来执行 SQL 了。
3. 我觉得如果代码每两天凌晨 3 点运行一次，即使代码没有优化也没关系。 重要吗？这段代码执行正常吗？
4. 有没有更好的整体策略来完成这项任务？
5. 我需要使用交易吗？
6. 如何知道 cron 脚本中可能出现的错误？

如果我没有使用正确的 cron 术语，请道歉，这对我来说是新的。

Answer 1

保持简单。 ORM 非常适合这项任务。

回答：

1. 是的。
2. 你的代码是可读的。至少我读起来没有任何问题。
3. 我们有一个早上运行的脚本。它没有经过优化并且消​​耗了大量内存。四年后，它开始消耗超过 512 Mb。我花了 2 个小时来优化它，所以现在它消耗了 7 Mb（相当好的优化，哈？:)）。我个人认为你的脚本现在没有优化是“可以的”。如果此脚本开始失败，您就会找出问题所在。也许它会耗尽内存，也许你的SQL查询会导致死锁...也许你稍后会优化它以从从服务器读取...我不知道，但它现在工作正常，没关系。
4. 我会做一些类似于你的代码的事情。但我可能会先生成文件并通过运行 shell 命令 mysql -u username --password=password 将数据加载到服务器中导入_file.sql。因此，我会将文件存储在磁盘上的某个位置，这样我就可以随时查看它。甚至可以编辑一次性校正负载。但你仍然可以通过将 sql 语句写入文件来完成。
5. 不，这只是一个查询。如果你使用InnoDB引擎它已经是一个事务了。
6. 首先，使用 error_reporting(E_ALL & ~E_NOTICE)。其次，使用 mysql_error PHP 函数来确保查询正确执行。第三，在 cronjob 输出中，错误流到某个文件中，如下所示： 0 7 * * 0 /path/to/php -c /path/to/php.ini /path/to/script.php 2> /tmp/errors_file 因此，您可以创建在第一个脚本之后运行的第二个脚本，以通过电子邮件或......任何您喜欢的通知方式通知 script.php 中的错误。我更喜欢 register_shutdown_functions 来检查 error_file，如果不为空，则通知您并随后将其删除。

只是我的意见，但希望我的回答对您有所帮助。