我正试图从我的PHP类执行下面的SQL代码，但当我这样做时，会出现错误。下面的代码可以在PHPMyAdmin的控制台中完美运行，但不能在PHP中运行。
SET @columns := (
SELECT
GROUP_CONCAT(column_name)
FROM information_schema.columns
WHERE table_schema = 'test'
AND table_name = 'mytable'
AND column_key <> 'PRI'
);
SET @sql := (
SELECT CONCAT(
'INSERT INTO mytable (', @columns, ') ',
'SELECT ', @columns, ' FROM mytable ',
'WHERE id = 1;'
)
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
这就是我在PHP中的做法：
$sql='';
$sql.="SET @columns := (
SELECT
GROUP_CONCAT(column_name)
FROM information_schema.columns
WHERE table_schema = 'test'
AND table_name = 'mytable'
AND column_key <> 'PRI'
);";
$sql.="SET @sql := (
SELECT CONCAT(
'INSERT INTO mytable (', @columns, ') ',
'SELECT ', @columns, ' FROM mytable ',
'WHERE id = 1;'
)
);";
$sql.="PREPARE stmt FROM @sql;
EXECUTE stmt;";
$result = mysql_query($sql, $this->connection);
我做错了什么？
看到错误我得到：：
Database query failed: 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 'SET @sql := (
SELECT CONCAT(
'INSERT INTO mytable(', @colu' at line 9
来自手册：
mysql_query()发送一个唯一的查询（多个查询不是supported）到服务器上当前活动的数据库与指定的link_identifier关联。
转到mysqli，它支持多个语句。
/*
Author: Jack Mason
website: volunteer @http://www.osipage.com , web access application and bookmarking tool.
Language: PHP, Mysql
This script is free and can be used anywhere, no attribution required.
*/
ini_set('display_errors', 0);
error_reporting(0);
// SET MYSQL CONFIGURATION
$serverName = 'localhost';
$username = 'root';
$password = '';
$database = 'test_delete';
// SET THE SQL FILE PATH OR DIRECTLY GIVE ALL SQL STATEMENTS INSIDE QUOTES
$query = file_get_contents('file.sql');
//OR to execute multiple SQL statements directly, set "$query" variable as follows:
$query = 'CREATE TABLE IF NOT EXISTS `employee_attendances` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`attendance_date` date DEFAULT NULL,
`employee_id` int(11) DEFAULT NULL,
`employee_leave_type_id` int(11) DEFAULT NULL,
`reason` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`is_half_day` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `items_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` TEXT DEFAULT NULL,
`price` DOUBLE DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
';
// Establishing connection with mysqli database
$con = new mysqli($serverName, $username, $password, $database);
/* check connection */
if(mysqli_connect_errno()) {
printf("Connect failed: %s'n", mysqli_connect_error());
exit();
}
/* execute multi query */
if($con->multi_query($query))
{
do {
/* store first result set */
if($resultSet = $con->store_result())
{
while($row = $resultSet->fetch_row())
{
printf("%s'n", $row[0]);
}
$resultSet->free();
}
//print divider
if($con->more_results())
{
$loadArray = array("Creating tables....", "please wait..", "stay tuned while all table definitions are dumped...");
$upperLimit = count($loadArray) - 1;
$randNumb = rand(0, $upperLimit);
echo $loadArray[$randNumb]; echo '<br/>';
$loadArray = array();
}
} while ($con->next_result());
echo 'All tables have been successfully copied/created to given database!';
/* close connection */
}
$con->close();
此代码既可用于.SQL文件，也可直接执行多个SQL查询。通过一次成功执行多达200个表进行测试。取自这个phpsnipps页面。
只需对MySQL数据库运行查询"set names'utf8'"，您的输出应该是正确的。
使用以下代码作为示例：
$sql.= <<<EOF
SET @sql := (
SELECT CONCAT(
'INSERT INTO mytable (', @columns, ') ',
'SELECT ', @columns, ' FROM mytable ',
'WHERE id = 1;'
)
)
EOF;
编辑：
$sql.= <<<EOF
SET @columns := (
SELECT
GROUP_CONCAT(column_name)
FROM information_schema.columns
WHERE table_schema = 'test'
AND table_name = 'mytable'
AND column_key <> 'PRI'
);
EOF;
使用EOF进行这样的陈述。
正如Burhan所指出的，mysql+php不再支持多个查询。原因可能是SQL注入