生成sql表结构
时间：2021-07-01 10:21:17
帮助过：7人阅读
/**
* Created by PhpStorm.
* User: Administrator
* Date: 2017/7/21
* Time: 14:26
* 列子
* $pdo=new DataConstruct();
* $tables=$pdo->getAllTableNames();
* $pdo->printLog($pdo->getAllTableConstruct($tables),"F:/logstr.php");
* $pdo->importSql(‘E:/UserTable.sql‘);
*/
namespace app\index\command;
class DataConstruct
{
public $dataBaseType;
public $host;
public $username;
public $password;
public $port;
public $database;
public $conn;
public function __construct(
$username = "root",
$password = "",
$database = "aui",
$port = "3306",
$host = "localhost",
$dataBaseType = "mysql"
)
{
$this->dataBaseType =
$dataBaseType;
$this->host =
$host;
$this->username =
$username;
$this->password =
$password;
$this->port =
$port;
$this->database =
$database;
$dsn = "
$dataBaseType:dbname=
$database;host=
$host"
;
try {
$this->conn =
new \PDO(
$dsn,
$this->username,
$this->
password);
} catch (PDOException
$e) {
echo ‘Connection failed: ‘ .
$e->
getMessage();
}
}
/**
* @desc 获取所有的额表明
* @return array
*/
public function getAllTableNames()
{
$tableNames =
array();
$sql = "show tables"
;
$cursor =
$this->conn->query(
$sql);
foreach (
$cursor as $row) {
$tableNames[] =
$row[0
];
}
return $tableNames;
}
/**
* @desc 获取某个表的表结构
* @param $tablename
* @return array
*/
public function getTableConstruct(
$tablename)
{
$table =
array();
$sql = "select COLUMN_NAME,COLUMN_TYPE,COLUMN_DEFAULT,COLUMN_KEY,COLUMN_COMMENT from information_schema.`COLUMNS` where TABLE_NAME=‘" .
$tablename . "‘ and TABLE_SCHEMA=‘" .
$this->database . "‘"
;
$cursor =
$this->conn->query(
$sql, \PDO::
FETCH_ASSOC);
foreach (
$cursor as $row) {
$table[
$row[‘COLUMN_NAME‘]] =
$row;
}
return $table;
}
/**
* @desc 获取所有表的表结构
* @param array $tableNames
* @return array
*/
public function getAllTableConstruct(
$tableNames =
array())
{
$AllTableConstruct =
array();
if (
is_array(
$tableNames)) {
foreach (
$tableNames as $tableName) {
$AllTableConstruct[
$tableName] =
$this->getTableConstruct(
$tableName);
}
}
return $AllTableConstruct;
}
/**
* @desc 打印表结构到指定的文件中
* @param $tableConstruct
* @param $logpath
*/
public function printLog(
$tableConstruct,
$logpath)
{
foreach (
$tableConstruct as $key =>
$table) {
file_put_contents(
$logpath,
$key .
PHP_EOL,
FILE_APPEND);
file_put_contents(
$logpath, "COLUMN_NAME COLUMN_TYPE COLUMN_DEFAULT COLUMN_KEY COLUMN_COMMENT" .
PHP_EOL,
FILE_APPEND);
foreach (
$table as $column) {
file_put_contents(
$logpath,
$column[‘COLUMN_NAME‘] . "\t" .
$column[‘COLUMN_TYPE‘] . "\t" .
$column[‘COLUMN_DEFAULT‘] . "\t" .
$column[‘COLUMN_KEY‘] . "\t" .
$column[‘COLUMN_COMMENT‘] .
PHP_EOL,
FILE_APPEND);
}
file_put_contents(
$logpath,
PHP_EOL,
FILE_APPEND);
}
}
/**
* @desc 执行sql文件 （不能超温php.ini中配置的内存大小，否则会报内存溢出错误）
* @param $filepath
*/
public function importSql(
$filepath)
{
$_sql =
file_get_contents(
$filepath);
$_arr =
explode(‘;‘,
$_sql);
$this->conn->query(‘set names utf8;‘
);
foreach (
$_arr as $_value) {
try {
$this->conn->query(
$_value . ‘;‘
);
} catch (\PDOException
$e) {
var_dump(
$this->conn->
errorInfo());
}
}
}
}
生成sql表结构
标签：put contents UI mat new message username code log
