各位大大，目前遇到一个问题：在mysql数据库中保存有一个树形结构，表结构如下：id tree_id parent_id1 1 0 // root node2 1 13 1 14 1 25 1 26 1 37 1 4其中id是自增的，tree_id标识一棵树，即tree_id相同的node为同一棵树，现在需要将这棵树进行复制，即新树不改变原树的结构和数据，但id继续增加，tree_id++，请问有什么好的办法。现在能想到的只有把这棵树读出来之后用数组或者其他结构保存在内存中然后对其先根遍历进行insert，每增加一个新node就把他的id保存到这个结构中以使后续的子节点可以使用。求教各位。
insert into t_tree select null,tree_id+1,parent_id from t_tree where tree_id =?;
Test Case:
mysql> create table t_tree (id int auto_increment primary key,tree_id int,parent_id int);
Query OK, 0 rows affected (0.04 sec)mysql> insert into t_tree values(1,1,0);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(2,1,1);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(3,1,1);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(4,1,2);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(5,1,2);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(6,1,3);
Query OK, 1 row affected (0.06 sec)mysql> insert into t_tree values(7,1,4);
Query OK, 1 row affected (0.00 sec)mysql> commit;
Query OK, 0 rows affected (0.00 sec)mysql> insert into t_tree select null,tree_id+1,parent_id from t_tree where tree_id =1;
Query OK, 7 rows affected (0.09 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql> select * from t_tree;
+----+---------+-----------+
| id | tree_id | parent_id |
+----+---------+-----------+
| 1 | 1 | 0 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 1 | 2 |
| 5 | 1 | 2 |
| 6 | 1 | 3 |
| 7 | 1 | 4 |
| 8 | 2 | 0 |
| 9 | 2 | 1 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 2 |
| 13 | 2 | 3 |
| 14 | 2 | 4 |
+----+---------+-----------+
14 rows in set (0.00 sec)
这样复制之后的parent_id还是tree 1的，并不tree2中的相应id呀
delimiter //
create function copytree(p_tree_id int) returns int
begin
declare new_tree_id int default 0;
declare baseaddr int default 0;
set new_tree_id = p_tree_id+1;
insert into t_tree select null,tree_id+1,parent_id from t_tree where tree_id = p_tree_id;
select min(id)-1 into baseaddr from t_tree where tree_id= new_tree_id;
update t_tree set parent_id = parent_id+baseaddr where tree_id= new_tree_id
and parent_id !=0;
return 0;
end//
delimiter ;
Test Case:mysql> drop table if exists t_tree;
Query OK, 0 rows affected (0.03 sec)mysql> create table t_tree (id int auto_increment primary key,tree_id int,parent_id int);
Query OK, 0 rows affected (0.01 sec)mysql> insert into t_tree values(1,1,0);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(2,1,1);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(3,1,1);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(4,1,2);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(5,1,2);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(6,1,3);
Query OK, 1 row affected (0.00 sec)mysql> insert into t_tree values(7,1,4);
Query OK, 1 row affected (0.00 sec)mysql> drop function if exists copytree;
Query OK, 0 rows affected (0.00 sec)mysql> delimiter //
mysql> create function copytree(p_tree_id int) returns int
-> begin
-> declare new_tree_id int default 0;
-> declare baseaddr int default 0;
-> set new_tree_id = p_tree_id+1;
-> insert into t_tree select null,tree_id+1,parent_id from t_tree where tree_id = p_tree_id;
-> select min(id)-1 into baseaddr from t_tree where tree_id= new_tree_id;
-> update t_tree set parent_id = parent_id+baseaddr where tree_id= new_tree_id
-> and parent_id !=0;
-> return 0;
-> end//
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> select * from t_tree;
+----+---------+-----------+
| id | tree_id | parent_id |
+----+---------+-----------+
| 1 | 1 | 0 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 1 | 2 |
| 5 | 1 | 2 |
| 6 | 1 | 3 |
| 7 | 1 | 4 |
+----+---------+-----------+
7 rows in set (0.02 sec)mysql> select copytree(1);
+-------------+
| copytree(1) |
+-------------+
| 0 |
+-------------+
1 row in set (0.04 sec)mysql> select * from t_tree;
+----+---------+-----------+
| id | tree_id | parent_id |
+----+---------+-----------+
| 1 | 1 | 0 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 1 | 2 |
| 5 | 1 | 2 |
| 6 | 1 | 3 |
| 7 | 1 | 4 |
| 8 | 2 | 0 |
| 9 | 2 | 8 |
| 10 | 2 | 8 |
| 11 | 2 | 9 |
| 12 | 2 | 9 |
| 13 | 2 | 10 |
| 14 | 2 | 11 |
+----+---------+-----------+
14 rows in set (0.00 sec)