是否可以编写一个php代码,该代码将获取序列化数据,取消序列化数据,并在没有的新数据库模式上写入内容
ini_set("max_execution_time",0(;
我想我已经优化了我的代码,但这真的无法避免。
我使用以下代码进行迭代。
$queryCount ="SELECT count(*) FROM APPLICATION where PRO_UID='$PRO_UID'
AND APP_STATUS != 'DRAFT'
AND APP_STATUS != 'CANCELLED'";
$resultCount = $mysqli->query($queryCount);
$count = (mysqli_fetch_array($resultCount));
for($x=0;$x < $count; $x += 1000){
$queryData = "SELECT * FROM wf_workflow.APPLICATION
where PRO_UID='$PRO_UID'
AND APP_STATUS != 'CANCELLED'
AND APP_STATUS != 'DRAFT'
not in (SELECT APP_NUMBER FROM export_workflow.CARDS_CONTACT_DETAILS_LOOKUP) LIMIT 2000";
$resultData = $mysqli->query($queryData);
while($row = mysqli_fetch_array($resultData)){
$data = unserialize($row['APP_DATA']);
$APP_NUMBER = $row['APP_NUMBER'];
$PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
$MOBILE = $data["ContactDetails"][1]["MobileNo"];
$OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
$HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];
$sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
VALUES ('$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";
if (mysqli_query($mysqli, $sql)) {
echo "New record created successfully FROM dev";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
}
}
echo "finished the dev";
}
第一个查询显示的只是用于for循环的计数。但for循环似乎不是必需的。
主查询正在使用AND APP_STATUS!='DRAFT'not in(SELECT不检查值/列在子查询的结果集中。怀疑这只会检查true还是false(APP_STATUS!='DRAFT'的结果(是子查询的返回值。看看你的代码,我想你应该有AND APP_STATUS!='DRAFT的AND APP_NUMBER不在(SELECT。但是not in可能执行得很差,所以最好将其重新编码为联接。
考虑到这一点,类似这样的事情(未测试(:-
<?php
$queryData = "SELECT a.APP_NUMBER, a.APP_DATA
FROM wf_workflow.APPLICATION a
LEFT OUTER JOIN export_workflow.CARDS_CONTACT_DETAILS_LOOKUP b
ON a.APP_NUMBER = b.APP_NUMBER
WHERE a.PRO_UID='$PRO_UID'
AND a.APP_STATUS != 'CANCELLED'
AND a.APP_STATUS != 'DRAFT'
AND b.APP_NUMBER IS NULL";
$resultData = $mysqli->query($queryData);
while($row = mysqli_fetch_array($resultData))
{
$data = unserialize($row['APP_DATA']);
$APP_NUMBER = $row['APP_NUMBER'];
$PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
$MOBILE = $data["ContactDetails"][1]["MobileNo"];
$OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
$HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];
$sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
VALUES ('$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";
if (mysqli_query($mysqli, $sql))
{
echo "New record created successfully FROM dev";
}
else
{
echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
}
}
echo "finished the dev";
您还可以通过批处理insert(例如,一次插入100行(来提高脚本的性能。这可能不会对脚本超时产生太大的影响(等待MySQL的时间不应该影响脚本的时间限制(,但可能会加快速度。类似这样的东西:-
$queryData = "SELECT a.APP_NUMBER, a.APP_DATA
FROM wf_workflow.APPLICATION a
LEFT OUTER JOIN export_workflow.CARDS_CONTACT_DETAILS_LOOKUP b
ON a.APP_NUMBER = b.APP_NUMBER
WHERE a.PRO_UID='$PRO_UID'
AND a.APP_STATUS != 'CANCELLED'
AND a.APP_STATUS != 'DRAFT'
AND b.APP_NUMBER IS NULL";
$resultData = $mysqli->query($queryData);
$inserts = array();
while($row = mysqli_fetch_array($resultData))
{
$data = unserialize($row['APP_DATA']);
$APP_NUMBER = $row['APP_NUMBER'];
$PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
$MOBILE = $data["ContactDetails"][1]["MobileNo"];
$OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
$HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];
$inserts[] = "'$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";
if (count($inserts) > 100)
{
$sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
VALUES ".implode(', ', $inserts);
$inserts = array();
if (mysqli_query($mysqli, $sql))
{
echo "New records created successfully FROM dev";
}
else
{
echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
}
}
}
if (count($inserts) > 0)
{
$sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
VALUES ".implode(', ', $inserts);
$inserts = array();
if (mysqli_query($mysqli, $sql))
{
echo "New records created successfully FROM dev";
}
else
{
echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
}
}
echo "finished the dev";
不过,你最好还是逃离你的数据!