我有两张表
tb1
{
gid 自增
color
size
}
tb2
{
id 自增
gid
weight
}我想向两张表中同时插入一条gid相同的数据,比如我向tb1中插入(red,"23"),然后向tb2中插入一条数据,要求tb2.gid与刚插入tb1的gid相同,应当怎么写呢,谢谢@!~~~
tb1
{
gid 自增
color
size
}
tb2
{
id 自增
gid
weight
}我想向两张表中同时插入一条gid相同的数据,比如我向tb1中插入(red,"23"),然后向tb2中插入一条数据,要求tb2.gid与刚插入tb1的gid相同,应当怎么写呢,谢谢@!~~~
mysql_select_db("mydb");$sql = "INSERT INTO tb1 (color,size) values ('red','23')";
mysql_query($sql);
$id = mysql_insert_id(); //得到刚刚插入的ID$sql = "INSERT INTO tb2 (gid,weight) values ($id,'test')";
mysql_query($sql);这样就可以了。
for each row
insert into tb2(gid) values(new.gid);触发器里的插入值你可以根据自己需要来写。
-> gid int auto_increment primary key,
-> color int,
-> size int
-> );
Query OK, 0 rows affected (0.16 sec)mysql>
mysql> create table tb2(
-> id int auto_increment primary key,
-> gid int,
-> weight int
-> );
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> create table tb3(
-> id int auto_increment primary key,
-> gid int,
-> c3 int
-> );
Query OK, 0 rows affected (0.08 sec)mysql> delimiter |
mysql>
mysql> CREATE TRIGGER t_tb1_ai AFTER INSERT ON tb1
-> FOR EACH ROW BEGIN
-> insert into tb2(gid) values (new.gid);
-> insert into tb3(gid) values (new.gid);
-> END;
-> |
Query OK, 0 rows affected (0.11 sec)mysql>
mysql> delimiter ;
mysql>
mysql> insert into tb1 (color) values (123);
Query OK, 1 row affected (0.27 sec)mysql> select * from tb1;
+-----+-------+------+
| gid | color | size |
+-----+-------+------+
| 1 | 123 | NULL |
+-----+-------+------+
1 row in set (0.00 sec)mysql> select * from tb2;
+----+------+--------+
| id | gid | weight |
+----+------+--------+
| 1 | 1 | NULL |
+----+------+--------+
1 row in set (0.00 sec)mysql> select * from tb3;
+----+------+------+
| id | gid | c3 |
+----+------+------+
| 1 | 1 | NULL |
+----+------+------+
1 row in set (0.00 sec)mysql>
否则,你要像前面说的:进行第一次插入后,获取last_insert_id()放进变量里,后面2次insert引用该变量的值。
有其它字段依然触发器插入啊!
CREATE TRIGGER t_tb1_ai AFTER INSERT ON tb1
-> FOR EACH ROW BEGIN
-> insert into tb2(gid,name,type) values (new.gid,'d','d');
-> insert into tb3(gid,name,type) values (new.gid,'af','af');
-> END;
-> |
http://community.csdn.net/IndexPage/SmartQuestion.aspx 提问的智慧
表结构(只列出了部分字段):
CREATE TABLE `goods` (
`goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`goods_name` varchar(120) NOT NULL DEFAULT ''
PRIMARY KEY (`goods_id`)
)
CREATE TABLE `goods_extension` (
`Id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`desc` tinyint(2) unsigned NOT NULL DEFAULT '1' ,
PRIMARY KEY (`Id`)
)
CREATE TABLE `goods_image` (
`Id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0' ',
`goods_thumb` varchar(255) NOT NULL DEFAULT '0' ,
PRIMARY KEY (`Id`)
)
goods表中的goods_id是自增的。我想同时向三张表中插数据,当然goods_id必须要相同,但是如果单独对一只能够表insert的话,用last_insert_id()取得的goods_id就不对了。下面是我想要的结果:
goods表:
goods_id goods_name
-------------------------
1001 pen
2001 ruler
......goods_extension表:
id goods_id desc
-----------------------------------
1 1001 this is a pen
2 1002 this is a ruler
......goods_image表:
id goods_id goods_thumb
------------------------------------------
1 1001 http://www.test.com
2 1002 http://www.test2.com
......
mysql_select_db("mydb"); $sql = "INSERT INTO tb1 (color,size) values ('red','23')";
mysql_query($sql);
$id = mysql_insert_id(); //得到刚刚插入的ID $sql = "INSERT INTO tb2 (gid,weight) values ($id,'test')";
mysql_query($sql); $sql = "INSERT INTO tb3 (gid,column3) values ($id,'test3')";
mysql_query($sql); $sql = "INSERT INTO tb4 (gid,column4) values ($id,'test4')";
mysql_query($sql); $sql = "INSERT INTO tb5 (gid,column5) values ($id,'test5')";
mysql_query($sql);