php迁移数据

业务的增长，有时数据库激增，满足不了现状，就需要对数据库进行调整和切分，难免要对数据进行处理后再插入到新的表中。在这个过程中，往往由于一些历史的问题，数据表中含有“脏数据”，也就是不符合规范的数据，比如用户名含有特殊字符，邮箱地址不为空却为空等等。最重要的是迁移数据的时间和准确性。
下面是一段迁移数据的php程序：
[codesyntax lang="php"]
<?php 

/*
*** author: www.ttlsa.com
***** QQ群：39514058
*** E-mail：service@ttlsa.com
*/
$CONFIG['TTLSA']['from']=array();
$CONFIG['TTLSA']['from']['db_user']='root';
$CONFIG['TTLSA']['from']['db_pass']='123456';
$CONFIG['TTLSA']['from']['db_name']='ttlsa';
$CONFIG['TTLSA']['from']['db_host']="192.168.1.2";文章源自运维生存时间-https://www.ttlsa.com/php/php-qian-yi-shu-ju/

$CONFIG['TTLSA']['to']=array();
$CONFIG['TTLSA']['to']['db_user']='root';
$CONFIG['TTLSA']['to']['db_pass']='';
$CONFIG['TTLSA']['to']['db_name']='ttlsa_blog';
$CONFIG['TTLSA']['to']['db_host']="localhost";文章源自运维生存时间-https://www.ttlsa.com/php/php-qian-yi-shu-ju/

$from_conn=mysql_connect($CONFIG['TTLSA']['from']['db_host'],$CONFIG['TTLSA']['from']['db_user'],$CONFIG['TTLSA']['from']['db_pass'],$CONFIG['TTLSA']['from']['db_name']);
$to_conn=mysql_connect($CONFIG['TTLSA']['to']['db_host'],$CONFIG['TTLSA']['to']['db_user'],$CONFIG['TTLSA']['to']['db_pass']);文章源自运维生存时间-https://www.ttlsa.com/php/php-qian-yi-shu-ju/

mysql_select_db($CONFIG['TTLSA']['from']['db_name'],$from_conn);文章源自运维生存时间-https://www.ttlsa.com/php/php-qian-yi-shu-ju/

$max=3000000;
$step=100000;
for ($i=1;$i $step_i=$i+$step;
mysql_query("set names 'utf8'",$from_conn);
$sql="select `uid`,`reg_ip`,`reg_time`,`last_login`,`question`,`answer` from users where user_id>=$i and user_id $rs=mysql_query($sql,$from_conn) or die("Invalid query: " . mysql_error());
$value=array();
while ($v=mysql_fetch_array($rs, MYSQL_ASSOC)) {
$column=array();
$column['uid']=$v['uid'];
$column['reg_ip']=$v['reg_ip'];
$column['reg_time']=$v['reg_time'];
$column['last_login']=$v['last_login'];
if ($column['question'] && $column['answer']) {
$column['aq']=serialize(array($v['question'],$v['answer'])); //序列化存储
} else {
$column['aq']='';
}
$value[].="('{$column['uid']}','{$column['reg_ip']}','{$column['reg_time']}','{$column['last_login']}','{$column['aq']}')";
}
if ($value) {
$sql="insert into ttlsa_users (`uid`,`reg_ip`,`reg_time`,`last_login`,`aq`) values " . implode(",",$value);
mysql_select_db($CONFIG['TTLSA']['to']['db_name'],$to_conn);
mysql_query("set names 'utf8'",$to_conn);
mysql_query($sql,$to_conn) or die("Invalid query: " . mysql_error());
}
}
?>文章源自运维生存时间-https://www.ttlsa.com/php/php-qian-yi-shu-ju/

[/codesyntax]文章源自运维生存时间-https://www.ttlsa.com/php/php-qian-yi-shu-ju/