如何用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的传递??
比如说 父表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的传递??
sql="select id from parent where create_time<='2012-01-01' and create_time>='2012-01-01';"
id=$(mysql -h<IP> -u<用户> -p<密码> --column-names=0 --execute="$sql")sql="delete from child where id<$id;"
mysql -h<IP> -u<用户> -p<密码> --column-names=0 --execute="$sql"
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';"
不起作用啊
我用crontab 定时执行
[@* ~]$ crontab -l
07 17 * * * sh ~/test.sh
[@* ~]$ more test.shecho "===============TEST ID_SAILING==================================" > ~/111.log/usr/local/mysql/bin/mysql mysql -uroot -pmysql -e "SELECT * FROM
child where id<( select id from parent where create_time<='2012-01-01' and create_time>='2012-01-01'); " -N >> ~/111.log
echo "---------------select over-------------">>~/111.log
/usr/local/mysql/bin/mysql mysql -uroot -pmysql -e "delete FROM
child where id<( select id from parent where create_time<='2012-01-01' and create_time>='2012-01-01'); " -N >> ~/111.log
echo "-------delete child over-----">~/111.log执行完到mysql里面查看 都还在啊
mysql> use DBA_DEMO;
Database changed
mysql> SELECT * FROM child where id<( select id from parent where create_time<='2012-01-01' and create_time>='2012-01-01');
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 7 | 23 |
| 8 | 55 |
| 9 | 56 |
+----+-----------+
8 rows in set (0.00 sec)
现在先在测试库做测试.看能不能实现
而且我用/usr/local/mysql/bin/mysql mysql -uroot -pmysql -e "SELECT * FROM
child where id<( select id from parent where create_time<='2012-01-01' and create_time>='2012-01-01')\G " -N >> ~/111.log
echo "---------------select over-------------">~/111.log
/usr/local/mysql/bin/mysql mysql -uroot -pmysql -e "delete FROM
child where id<( select id from parent where create_time<='2012-01-01' and create_time>='2012-01-01') \G " -N >> ~/111.log
echo "-------delete child over-----">~/111.log
这种方法通过crontab 实现 不能成功 不知道是哪里出的问题
所有两种方法都试验下 那种可行就用那种吧
delete from child where id<(select id from parent where create_time<='2012-01-01' and create_time>='2012-01-01'
结果如何
这样不行啊 不执行啊
下面是我sh里面的内容 我用crontab执行
数据删不掉啊
sql="use DBA_DEMO;select id from parent where id<73 and id>71;"id=$(mysql -uroot -pmysql --column-names=0 --execute="$sql")sql=" USE DBA_DEMO ; delete from child where parent_id > $id \G"/usr/local/mysql/bin/mysql mysql -uroot -pmysql --column-names=0 --execute="$sql"
请教高手指点 此段语句哪里不对
多谢
可能还是路径的问题,你可以在 crontab 里把错误输出定向到一个文件里,这样就好看到错误,进行分析了。
如果手工执行正常,则推测是这里,把 mysql 改为绝对路径试试:
id=$(mysql -uroot -pmysql --column-names=0 --execute="$sql")
在crontab里设置才能将错误定向输出到一个文件中
07 16 * * * sh ~/id2.sh> ~/test120831.log
我这样写 什么错误信息也没有啊
请指点下啊
crontab 里没有用户啊,你用 ~ 它到哪找去
假设你的 ~ 就是 /home/user_name,就要这样:07 16 * * * /home/user_name/id2.sh 2> /home/user_name/test120831.log
sh脚本也可以执行了 谢谢你
给分了哦