第1行的MySql语法错误

I have made a backup script which outputs a .sql file which should be able to restore the db. When restoring the database, MySql is telling me there is something wrong with my syntax.

我做了一个备份脚本,输出一个.sql文件,该文件应该能够恢复数据库。在恢复数据库时,MySql告诉我语法有问题。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 1

您的SQL语法有错误;检查与您的MySQL服务器版本对应的手册,以便在''附近使用正确的语法')ENGINE = MyISAM DEFAULT CHARSET = latin1'在第1行

   DROP TABLE category; 

CREATE TABLE `category` ( 
`cat_id` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
`cat_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
PRIMARY KEY (`cat_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

The .sql file is read using this script

使用此脚本读取.sql文件

    // Read in entire file
$sql_commands_array = file($backup_file);

$file = fopen($backup_file, "r") or exit("Unable to open file!");
//Output a line of the file until the end is reached
while(!feof($file))
  {
  echo fgets($file). "<br />";
  }
fclose($file);

// Loop through each line
foreach ($sql_commands_array as $current_command)
{
    //echo $current_command."test";

    // Add this line to the current segment
    $current_query .= $current_command;
    // If it has a semicolon at the end, it's the end of the query
    if (substr(trim($current_command), -1, 1) == ';')
    {
        // Perform the query
        mysql_query($current_query) or print('Error Updating DB'.mysql_error().'<br />');
        // Reset temp variable to empty
    }

    $current_query = '';
}

any suggestions on how to solve this. The CREATE TABLE output was made using the mysql_query('SHOW CREATE TABLE '.$table)

关于如何解决这个问题的任何建议。 CREATE TABLE输出是使用mysql_query('SHOW CREATE TABLE'。$ table)生成的

2 个解决方案

#1

You should empty the $current_query in if (substr(trim($current_command), -1, 1) == ';') block. But instead you are doing it in every iteration. The result is, you are sending lines from backup file to mysql_query instead of complete query.

你应该在if(substr(trim($ current_command),-1,1)==';')块中清空$ current_query。但相反,你在每次迭代中都这样做。结果是,您将从备份文件发送到mysql_query而不是完整查询。

#2

The best solution is:

最好的解决方案是:

`mysql < $backup_file`

Restoring a mysqldump file is a lot more complex than you think. For example, semicolons can occur inside comments, string literals, or inside CREATE PROCEDURE statements, without being the end of an SQL statement. Your PHP code doesn't handle those cases.

恢复mysqldump文件要比你想象的要复杂得多。例如,分号可以出现在注释,字符串文字或CREATE PROCEDURE语句中,而不是SQL语句的结尾。您的PHP代码不处理这些情况。

You can write a simple PHP function to execute SQL statements from a file only if you're prepared to limit the types of statements that you support. Supporting all valid SQL scripts is months of work, and requires a real parser, not substr().

只有在准备限制所支持的语句类型时,才可以编写一个简单的PHP函数来从文件中执行SQL语句。支持所有有效的SQL脚本需要几个月的工作,并且需要一个真正的解析器,而不是substr()。