select into from 提示 Undeclared variable.....错误的解决办法 ＆＆ select into from 和 insert into select 的用法和区别

 然而今天在使用 SELECT INTO FROM 备份mysql数据表的时候，运行相关 sql 语句的时候却一直返回 [Err] 1327 - Undeclared variable: ...... 这种错误，实在不解，经过查询相关资料才知道，原来 mysql 数据库是不支持 SELECT INTO FROM 这种语句的，但是经过研究是可以通过另外一种变通的方法解决这个问题的，下面就来说说解决这个错误的辦法吧！

mysql> select user,host into user2 from user;
ERROR 1327 (42000): Undeclared variable: user2

解决方法是:

mysql> create table user2 (select * from user);
Query OK, 6 rows affected (0.18 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create table user01 (select user,password,host from user);
Query OK, 6 rows affected (0.20 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from user01;
+------+----------+-----------+
| user | password | host      |
+------+----------+-----------+
| root |          | localhost |
| root |          | lmr       |
| root |          | 127.0.0.1 |
| root |          | ::1       |
|      |          | localhost |
|      |          | lmr       |
+------+----------+-----------+
6 rows in set (0.00 sec)

mysql> desc user01;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user     | char(16) | NO   |     |         |       |
| password | char(41) | NO   |     |         |       |
| host     | char(60) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

注意： SELECT INTO 复制表或表结构的时候，只是得到了一个“外壳”，就像克隆人一样，只是得到了一个躯体，个人的意识、回忆都不会克隆的。像原表的主键、外键、约束、触发 器、索引都不会被复制过来