以下代码来自PHPBB的SQL文件解析类,原理就是逐行的解释SQL文件,并进行删除等 *** 作,可以参照修改。希望能帮到你。
<?phpini_set('memory_limit', '5120M')
set_time_limit ( 0 )
/***************************************************************************
* sql_parse.php
* -------------------
* begin : Thu May 31, 2001
* copyright : (C) 2001 The phpBB Group
* email : support@phpbb.com
*
* $Id: sql_parse.php,v 1.8 2002/03/18 23:53:12 psotfx Exp $
*
****************************************************************************/
/***************************************************************************
*
* This program is free software you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation either version 2 of the License, or
* (at your option) any later version.
*
***************************************************************************/
/***************************************************************************
*
* These functions are mainly for use in the db_utilities under the admin
* however in order to make these functions available elsewhere, specifically
* in the installation phase of phpBB I have seperated out a couple of
* functions into this file. JLH
*
\***************************************************************************/
//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
function remove_comments(&$output)
{
$lines = explode("\n", $output)
$output = ""
// try to keep mem. use down
$linecount = count($lines)
$in_comment = false
for($i = 0 $i < $linecount $i++)
{
if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
{
$in_comment = true
}
if( !$in_comment )
{
$output .= $lines[$i] . "\n"
}
if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
{
$in_comment = false
}
}
unset($lines)
return $output
}
//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
function remove_remarks($sql)
{
$lines = explode("\n", $sql)
// try to keep mem. use down
$sql = ""
$linecount = count($lines)
$output = ""
for ($i = 0 $i < $linecount $i++)
{
if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
{
if (isset($lines[$i][0]) && $lines[$i][0] != "#")
{
$output .= $lines[$i] . "\n"
}
else
{
$output .= "\n"
}
// Trading a bit of speed for lower mem. use here.
$lines[$i] = ""
}
}
return $output
}
//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
function split_sql_file($sql, $delimiter)
{
// Split up our string into "possible" SQL statements.
$tokens = explode($delimiter, $sql)
// try to save mem.
$sql = ""
$output = array()
// we don't actually care about the matches preg gives us.
$matches = array()
// this is faster than calling count($oktens) every time thru the loop.
$token_count = count($tokens)
for ($i = 0 $i < $token_count $i++)
{
// Don't wanna add an empty string as the last thing in the array.
if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
{
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$i], $matches)
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches)
$unescaped_quotes = $total_quotes - $escaped_quotes
// If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
if (($unescaped_quotes % 2) == 0)
{
// It's a complete sql statement.
$output[] = $tokens[$i]
// save memory.
$tokens[$i] = ""
}
else
{
// incomplete sql statement. keep adding tokens until we have a complete one.
// $temp will hold what we have so far.
$temp = $tokens[$i] . $delimiter
// save memory..
$tokens[$i] = ""
// Do we have a complete statement yet?
$complete_stmt = false
for ($j = $i + 1 (!$complete_stmt && ($j < $token_count)) $j++)
{
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$j], $matches)
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches)
$unescaped_quotes = $total_quotes - $escaped_quotes
if (($unescaped_quotes % 2) == 1)
{
// odd number of unescaped quotes. In combination with the previous incomplete
// statement(s), we now have a complete statement. (2 odds always make an even)
$output[] = $temp . $tokens[$j]
// save memory.
$tokens[$j] = ""
$temp = ""
// exit the loop.
$complete_stmt = true
// make sure the outer loop continues at the right point.
$i = $j
}
else
{
// even number of unescaped quotes. We still don't have a complete statement.
// (1 odd and 1 even always make an odd)
$temp .= $tokens[$j] . $delimiter
// save memory.
$tokens[$j] = ""
}
} // for..
} // else
}
}
return $output
}
$dbms_schema = 'yourfile.sql'
$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema)) or die('problem ')
$sql_query = remove_remarks($sql_query)
$sql_query = split_sql_file($sql_query, '')
$host = 'localhost'
$user = 'user'
$pass = 'pass'
$db = 'database_name'
mysql_connect($host,$user,$pass) or die('error connection')
mysql_select_db($db) or die('error database selection')
$i=1
foreach($sql_query as $sql){
echo $i++
echo "
"
mysql_query($sql) or die('error in query')
}
?>
sql文件属于一种已经导出的数据库备份文件,你需要将其导入数据库才能正常使用。sql语句是:
use `数据库名`
source xxx.sqlsource后最好使用完整的文件路径例如“d:/xampp/xxx.sql”。
navicat中,直接打开数据库,然后右键点击数据库名,选择运行sql,执行即可。
$haha = M(),$res = $haha->query($sql)。
或 $res = $waw->execute($sql)。
$sql中包含了表名,实例化模型时可以为空。注意query是查功能,execute是增删改功能。
结构化查询语言(Structured Query Language)简称SQL(发音:/ˈes kjuː ˈel/ "S-Q-L"),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。
结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统。
可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能。
1986年10月,美国国家标准协会对SQL进行规范后,以此作为关系式数据库管理系统的标准语言(ANSI X3. 135-1986),1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。
所以,实际上不同数据库系统之间的SQL不能完全相互通用。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)