如何用shell脚本实现传递表id值?
如何用shell 脚本 实现 根据查询出的id 然后根据id 找出子表对应的记录然后删除子表中对应的记录??
比如说 父表parent ;子表child;
如下表结构：
show create table parent;
CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` datetime DEFAULT ‘2012-01-01 00:00:00’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8 ;
show create table child;
CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8
mysql> select id from parent where create_time<=’2012-01-01′ and create_time>=’2012-01-01′;
+—-+
| id |
+—-+
| 101|
+—-+
1 row in set (0.00 sec)
将此值 id=101 传给 子表 child 将小于此id 的记录删除.
delete from child where id<101;
然后删除父表中的对应记录
delete from parent create_time<=’2012-01-01′;
请教大家 如何通过shell 实现 id的传递？？
为什么不直接用 delete from child where id < (select id from parent where create_time<=’2012-01-01′ and create_time>=’2012-01-01′); 呢，还要外调一下 shell 呀？
直接删除不行？
mysql -uroot -p123 -N -e “delete from child where id<(select id from parent where create_time<=’2012-01-01′ and create_time>=’2012-01-01′);”
mysql -uroot -p123 -N -e “delete from parent create_time<=’2012-01-01′;”
