我照你说的做了一遍没有任何问题下面是屏幕拷贝: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3059 to server version: 3.23.49aType 'help;' or '\h' for help. Type '\c' to clear the buffermysql> create TEMPORARY TABLE tmp(name char(4)); Query OK, 0 rows affected (0.04 sec)mysql> insert into tmp (name) values('z'); Query OK, 1 row affected (0.04 sec)mysql> select * from tmp; +------+ | name | +------+ | z | +------+ 1 row in set (0.00 sec)
我也在本机(3.23.41)试了一次,却实可以,看样子是你的MYSQL有问题.
创建临时表： create temporary table if not exists tblname(); 删除临时表 drop table if exists tblname; 插入记录 insert into tblname () values ();
A.4.4 Where MySQL Stores Temporary Files MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally `/tmp' or `/usr/tmp'. If the filesystem containing your temporary file directory is too small, you should edit safe_mysqld to set TMPDIR to point to a directory in a filesystem where you have enough space! You can also set the temporary directory using the --tmpdir option to mysqld. MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will not see a big temporary file that fills up the filesystem in which the temporary file directory is located. When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk-space needed is: (length of what is sorted + sizeof(database pointer)) * number of matched rows * 2sizeof(database pointer) is usually 4, but may grow in the future for really big tables. For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form `SQL_*'. ALTER TABLE creates a temporary table in the same directory as the original table. The following are a list of the limitations with TEMPORARY TABLES. A temporary table can only be of type HEAP, ISAM, MyISAM or InnoDB. You can't use temporary tables more than once in the same query. For example, the following doesn't work. mysql> SELECT * FROM temporary_table, temporary_table AS t2;We plan to fix the above in 4.0. You can't use RENAME on a TEMPORARY table. Note that ALTER TABLE org_name RENAME new_name works! We plan to fix the above in 4.0.
我是指用 create TEMPORARY TABLE tmp (...)生成临时表后，怎么在临时表里插如记录啊？为什么insert 临时表会报找不到该表？
2.语法有没错,注意空格:INSERT INTO tbl_name (col1,col2) VALUES(15,2);
create TEMPORARY TABLE tmp(name char(4));
insert into tmp (name) values('z');
第一句通过了，第2句说找不到tmp表，另外我是用root权限的。
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3059 to server version: 3.23.49aType 'help;' or '\h' for help. Type '\c' to clear the buffermysql> create TEMPORARY TABLE tmp(name char(4));
Query OK, 0 rows affected (0.04 sec)mysql> insert into tmp (name) values('z');
Query OK, 1 row affected (0.04 sec)mysql> select * from tmp;
+------+
| name |
+------+
| z |
+------+
1 row in set (0.00 sec)
create temporary table if not exists tblname();
删除临时表
drop table if exists tblname;
插入记录
insert into tblname () values ();
MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally `/tmp' or `/usr/tmp'. If the filesystem containing your temporary file directory is too small, you should edit safe_mysqld to set TMPDIR to point to a directory in a filesystem where you have enough space! You can also set the temporary directory using the --tmpdir option to mysqld. MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will not see a big temporary file that fills up the filesystem in which the temporary file directory is located. When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk-space needed is: (length of what is sorted + sizeof(database pointer))
* number of matched rows
* 2sizeof(database pointer) is usually 4, but may grow in the future for really big tables. For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form `SQL_*'. ALTER TABLE creates a temporary table in the same directory as the original table.
The following are a list of the limitations with TEMPORARY TABLES. A temporary table can only be of type HEAP, ISAM, MyISAM or InnoDB.
You can't use temporary tables more than once in the same query. For example, the following doesn't work.
mysql> SELECT * FROM temporary_table, temporary_table AS t2;We plan to fix the above in 4.0.
You can't use RENAME on a TEMPORARY table. Note that ALTER TABLE org_name RENAME new_name works! We plan to fix the above in 4.0.