我正在尝试从我的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