<?php
/**
* @desc: 将数据库中的表字段进行重新排序
* @author: benjamin
* @date: 2015年7月21日
* @example：$formatDbFields = new FormatDbFields();
* $formatDbFields->db_tb_freorder();
*/
$formatDbFields = new FormatDbFields();
$formatDbFields->db_tb_freorder();
class FormatDbFields
{
private $__dbHost;
private $__dbName;
private $__dbPwd;
private $__dbUser;
private $__mysqli;
function __construct()
{
$this->__dbHost = "121.199.35.152";
$this->__dbName = "guagua";
$this->__dbUser = "guagua";
$this->__dbPwd = "wq0yUKO6fnOcrTf";
$this->__mysqli = new mysqli ( $this->__dbHost , $this->__dbUser , $this->__dbPwd , $this->__dbName );
$this->__mysqli->query('set names utf8');
}
private function __dump($var,$debugType = 1)
{
echo "<pre>";
if($debugType == 1)
{
print_r($var);
}
elseif($debugType == 2)
{
var_dump($var);
}
}
/* 对本次连接的数据库中所有表进行字段重排，按自增量ID再a-z进行排序 */
// database table filed reorder
public function db_tb_freorder()
{
if ( $this->__mysqli -> connect_errno )
{
printf ( "Connect failed: %s\n" , $this->__mysqli -> connect_error );
exit();
}
$sql = "SHOW FULL TABLES FROM `" . $this->__dbName."`";
$queryObj = $this->__mysqli->query($sql);
if($queryObj)
{
while($row = $queryObj->fetch_object())
{
$k = 'Tables_in_' . $this->__dbName;
$name = "`{$row->$k}`";
$this->tb_field_reorder($name);
echo "$name is ok<br>";
}
}
}
private function tb_field_reorder($name) {
$sql = "SHOW CREATE TABLE " . $name;
$queryObj = $this->__mysqli->query($sql);
$row = (array)$queryObj->fetch_object();
preg_match_all('~\n\s+((`[^`]+`).+),~i', $row['Create Table'], $ms);
$fs = array();
$pk = $pv = '';
foreach($ms[2] as $i=>$k) {
$fs[$k] = $ms[1][$i];
if(false !== stripos($fs[$k], 'AUTO_INCREMENT')) {
$pk = $k;
$pv = $fs[$k];
unset($fs[$k]);
}
}
ksort($fs);
$sqls = array();
$l = '';
if($pk && $pv) {
$sqls[] = " CHANGE $pk $pv FIRST ";
$l = $pk;
}
foreach($fs as $k=>$v) {
$sqls[] = " CHANGE $k $v " . ($l ? " AFTER $l " : " FIRST ");
$l = $k;
}
$sql = "ALTER TABLE " . $name . implode(" , ", $sqls);
$this->__mysqli->query($sql);
}
}
?>
评论