php 制作一个临时表并从中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10348196/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
make a temporary table and select from it
提问by Rich
I'm getting an error 'undeclared variable: temp' when I run this...
当我运行这个时，我收到一个错误“未声明的变量：临时”......
<?php
$maketemp = "CREATE TEMPORARY TABLE temp(`itineraryId` int NOT NULL, `live` varchar(1), `shipCode` varchar(10), `description` text, `duration` varchar(10), PRIMARY KEY(itineraryId))";
mysql_query( $maketemp, $connection ) or die ( "Sql error : " . mysql_error ( ) );
$inserttemp = "SELECT live, id AS itineraryId, ship AS shipCode, description AS description, duration AS length FROM cruises WHERE live ='Y' INTO temp";
mysql_query( $inserttemp, $connection ) or die ( "Sql error : " . mysql_error ( ) );
$select = "SELECT intineraryId, shipCode, description, duration FROM temp";
$export = mysql_query ( $select, $connection ) or die ( "Sql error : " . mysql_error( ) );
Any ideas ?
有任何想法吗 ？
回答by DaveRandom
Please, don't use mysql_*functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statementsinstead, and use PDOor MySQLi- this articlewill help you decide which. If you choose PDO, here is a good tutorial.
请不要mysql_*在新代码中使用函数。它们不再被维护并被正式弃用。看到红框了吗？了解准备好的语句，并使用PDO或MySQLi-本文将帮助您决定哪个。如果您选择 PDO，这里有一个很好的教程。
This code should work:
此代码应该工作：
<?php
$maketemp = "
CREATE TEMPORARY TABLE temp_table_1 (
`itineraryId` int NOT NULL,
`live` varchar(1),
`shipCode` varchar(10),
`description` text,
`duration` varchar(10),
PRIMARY KEY(itineraryId)
)
";
mysql_query($maketemp, $connection) or die ("Sql error : ".mysql_error());
$inserttemp = "
INSERT INTO temp_table_1
(`itineraryId`, `live`, `shipCode`, `description`, `duration`)
SELECT `id`, `live`, `ship`, `description`, `duration`
FROM `cruises`
WHERE `live` = 'Y'
";
mysql_query($inserttemp, $connection) or die ("Sql error : ".mysql_error());
$select = "
SELECT `itineraryId`, `shipCode`, `description`, `duration`
FROM temp_table_1
";
$export = mysql_query($select, $connection) or die ("Sql error : ".mysql_error());
I guess you are going to do more stuff with the temporary table, or are just playing with it, but if not be aware that the whole code could be summed up with:
我想你会用临时表做更多的事情，或者只是在玩它，但如果不知道整个代码可以总结为：
<?php
$query = "
SELECT `id` AS 'itineraryId', `ship`, `description`, `duration`
FROM `cruises`
WHERE `live` = 'Y'
";
$export = mysql_query($query, $connection) or die ("Sql error : ".mysql_error());
回答by antoniuslin
I like to use heredoc to help me construct embedded sql query (just to help make any subtle error glaring); so your first query would look something like this:
我喜欢使用 heredoc 来帮助我构建嵌入式 sql 查询（只是为了帮助使任何细微的错误变得明显）；所以你的第一个查询看起来像这样：
$maketemp =<<<s
CREATE TEMPORARY TABLE temp(
`itineraryId` int NOT NULL,
`live` varchar(1),
`shipCode` varchar(10),
`description` text,
`duration` varchar(10),
PRIMARY KEY(itineraryId));
s;
Then if you want to correct the 2nd query without listing the fields of the table you want to insert the records, you have to list the fields in the same order.
然后，如果您想在不列出要插入记录的表的字段的情况下更正第二个查询，则必须以相同的顺序列出字段。
Just the query this time:
这次只是查询：
INSERT INTO temp
SELECT id, live, ship, description, duration
FROM cruises
WHERE live = 'y';
And last thing about temporary variable is this: Check out the part about its visibility. You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. http://dev.mysql.com/doc/refman/5.5/en/create-table.html
关于临时变量的最后一件事是：查看有关其可见性的部分。创建表时可以使用 TEMPORARY 关键字。TEMPORARY 表仅对当前连接可见，并在连接关闭时自动删除。 http://dev.mysql.com/doc/refman/5.5/en/create-table.html
What that means is this: when you're connected to MySQL directly, e.g. through a command line interface like this:
这意味着：当您直接连接到 MySQL 时，例如通过这样的命令行界面：
mysql> #our query here line-by-line
Then you're essentially on the same connection through all of your multiple queries as long as your session is active.
然后，只要您的会话处于活动状态，您就基本上通过所有多个查询处于同一连接上。
Butin an external script (like PHP, for example), just because it's on the same script file doesn't necessarily mean it's the same connection so that by the time you execute your insert query, your temp table is not visible to that connection session.
但是在外部脚本（例如 PHP）中，仅仅因为它在同一个脚本文件上并不一定意味着它是同一个连接，因此当您执行插入查询时，您的临时表对该连接不可见会议。
Try to concat all the queries, send it all within a single command/query execution.
尝试连接所有查询，在单个命令/查询执行中将其全部发送。
Good luck.
祝你好运。
回答by Devart
The second query is not correct.
第二个查询不正确。
From the reference -
从参考 -
MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT. standard SQL syntax, which is basically the same thing.
MySQL Server 不支持 SELECT ... INTO TABLE Sybase SQL 扩展。相反，MySQL Server 支持INSERT INTO ... SELECT。标准的 SQL 语法，基本上是一样的。
Try this one instead -
试试这个 -
INSERT INTO temp
SELECT live
, id AS itineraryId
, ship AS shipCode
, description AS description
, duration AS length
FROM
cruises
WHERE
live = 'Y'
