请教mysql+linux如何生成.sql文件

peterddjj

我是通过shell 来执行的 我在我win机子上装的mysql 上试 的却可以 不知道为什么

atgc

最初由 peterddjj 发布
[B]我是通过shell 来执行的 我在我win机子上装的mysql 上试 的却可以 不知道为什么 [/B]

你是指什么不可以，是文件没有生成，还是？

atgc

你可以指定路径的
比如
select * from table into outfile '/mysql/test.sql';

atgc

如果你能生成文件，能把你生成的文件的前面几行贴出来吗

peterddjj

#!/bin/sh

mysql -uroot -p -Didd_billing < /root/bin/upprefix.txt > /root/data/upprefix.sql

cd /root/data
ftp -ivn 193.177.243.9 <<EOT
user accou acct
cd sc/data/
put upprefix.sql
bye
EOT

--------------------------------------
upprefix.txt   里的sql是
SELECT concat("update testing set end_time=","'",DATE_FORMAT(effdate - INTERVAL 1 second,'%Y%m%d%H%i%s'),"'"," where carrier_id=","'",carrier,"'"," and prefix=","'",prefix,"'",";" into outfile '/root/data/upprefix.sql' FROM upprefix
---------------------------------------------------
生成的upprefix.sql如下：
concat("update testing set end_time=","'",DATE_FORMAT(effdate - INTERVAL 1 second,'%Y%m%d%H%i%s'),"'"," where carrier_id=","'",carrier,"'"," and prefix=","'",prefix,"'",";"
update testing set end_time='20070430235959' where carrier_id='hgc' and prefix='41';
update testing set end_time='20070430235959' where carrier_id='hgc' and prefix='411';
update testing set end_time='20070430235959' where carrier_id='hgc' and prefix='4143';
update testing set end_time='20070430235959' where carrier_id='hgc' and prefix='4144';
update testing set end_time='20070430235959' where carrier_id='hgc' and prefix='4174';
update testing set end_time='20070430235959' where carrier_id='hgc' and prefix='4176';
update testing set end_time='20070430235959' where carrier_id='hgc' and prefix='4177';

atgc

我现在Linux上没装MySQL
你不要用shell
试试俺给你的SQL语句

peterddjj

mysql -uroot -p -Didd_billing < /root/bin/upprefix.txt > /root/data/upprefix.sql
改为mysql -uroot -p -Didd_billing < /root/bin/upprefix.txt
local: upprefix.sql remote: upprefix.sql
local: upprefix.sql: No such file or directory
就不行拉

peterddjj

upprefix.txt 里的sql是
SELECT concat("update testing set end_time=","'",DATE_FORMAT(effdate - INTERVAL 1 second,'%Y%m%d%H%i%s'),"'"," where carrier_id=","'",carrier,"'"," and prefix=","'",prefix,"'",";" into outfile '/root/data/upprefix.sql' FROM upprefix

就是和你的差不多吧

atgc

你直接在MySQL提示符下执行就没有你说的那个内容了
mysql> select id from aa into outfile 'd:\aa.sql';
Query OK, 1 row affected (0.00 sec)

mysql>

atgc

即使是你那样的方式也是没有问题的
如mm.txt内容
select id from aa into outfile 'c:/aa.sql';

然后
D:\MySQL\bin>mysql -uroot -pgene gene <d:\mm.txt

再看
C:\>more aa.sql
100