MySQL操作程序五
返回
表格数据复制
<![CDATA[
成功
先保存数据
C:\database\sql>mysqldump cookbook main_name1 > main_name1.txt -u cbuser -p
Enter password: ******
再插入数据
C:\database\sql>mysqldump cookbook < main_name1.txt -u cbuser -p
Enter password: ******
失败
C:\database\sql>mysqldump --tab=/tmp cookbook main_name1 -u cbuser -p
Enter password: ******
mysqldump: Can't create/write to file '\tmp\main_name1.sql' (Errcode: 2)
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'main\_name1'': Commands out of
sync; you can't run this command now (2014)
C:\database\sql>mysqldump --no-create-info --tab=/tmp cookbook main_name1 -u roo
t -p
Enter password: ****
mysqldump: Got error: 1: Can't create/write to file '\tmp\main_name1.txt' (Errco
de: 2) when executing 'SELECT INTO OUTFILE'
]]>
指定数据(部分项目)写入数据库(insert_tables2.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,$n1,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 指定数据库名
print "Please input database table name=";
chop($t{table1}=<STDIN>);
# 清空指定数据库表格内容
#$t{delete_table} = 'DELETE FROM ' . $t{table1};
#$$pref{dbh}->do($t{delete_table});
# 读取最大的数量
$t{maxid} = $$pref{dbh}->selectrow_array("SELECT max(id) FROM $t{table1}");
# 读取指定表格的所有数据
$t{inputf} = 'seki_' . $t{table1} . '.txt';
open(IN,"../txt/$t{inputf}") or die "Can't open the file $t{inputf}\n";
$t{NO} = -1;
while(<IN>){
if ( $. == 2 ) {
chop;
@fld = split(/===/,$_);
@{ $t{columns_list} } = @fld[1..$#fld];
} elsif ( $. > 2 ) {
chop;
@fld = split(/===/,$_);
$t{NO}++;
@{ $t{data_list}[$t{NO}] } = @fld[1..$#fld];
}
}
close(IN);
# 插入数据
$t{leng1} = $#{ $t{columns_list} };
$t{leng2} = $#{ $t{columns_list} } - 1;
for $n ( 0 .. $#{ $t{data_list} } ) {
next if $n < $t{maxid};
$t{sql} = 'INSERT INTO ' . $t{table1} . ' (';
for $n1 ( 0 .. $t{leng2} ) {
$t{sql} .= $t{columns_list}[$n1] . ',';
}
$t{sql} .= $t{columns_list}[$t{leng1}] . ')';
$t{sql} .= ' VALUES("';
for $n1 ( 0 .. $t{leng2} ) {
$t{data1} = $t{data_list}[$n][$n1];
$t{sql} .= $t{data_list}[$n][$n1] . '","';
}
$t{sql} .= $t{data_list}[$n][$t{leng1}] . '")';
# print "sql=$t{sql}\n";
$t{DO} = $$pref{dbh}->do($t{sql});
if ( $t{DO} == 0 ) {
print $t{sql},"\n";
}
}
print "Insert $t{table1} finished.\n";
# 关闭数据库
$$pref{dbh}->disconnect;
]]>
指定数据(全部项目)写入数据库(insert_tables.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,$n1,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 指定数据库名
print "Please input database table name=";
chop($t{table1}=<STDIN>);
# 清空指定数据库表格内容
$t{delete_table} = 'DELETE FROM ' . $t{table1};
$$pref{dbh}->do($t{delete_table});
# 读取指定表格的所有数据
$t{inputf} = 'kobe_' . $t{table1} . '.txt';
open(IN,"../txt/$t{inputf}") or die "Can't open the file $t{inputf}\n";
$t{NO} = -1;
while(<IN>){
if ( $. == 2 ) {
chop;
@fld = split(/===/,$_);
@{ $t{columns_list} } = @fld[1..$#fld];
} elsif ( $. > 2 ) {
chop;
@fld = split(/===/,$_);
$t{NO}++;
for $n ( 1 .. $#fld ) {
if ( $fld[$n] ) {
$t{data_list}[$t{NO}][$n-1] = '"' . $fld[$n] . '"';
} else {
$t{data_list}[$t{NO}][$n-1] = 'NULL';
}
}
# 这个操作的目的是保证两个array一样长
$t{start} = $#{ $t{data_list}[$t{NO}] };
$t{end} = $#{ $t{columns_list} };
if ($t{end} > $t{start}) {
$t{start} = $t{start} + 1;
for $n ( $t{start} .. $t{end} ) {
$t{data_list}[$t{NO}][$n] = 'NULL';
}
}
}
}
close(IN);
print "data_list=@{ $t{data_list}[0] }\n";
print "data_list=@{ $t{data_list}[1] }\n";
print "$#{ $t{columns_list} }\n";
print "$#{ $t{data_list}[0] }\n";
#exit;
# 插入数据
$t{leng1} = $#{ $t{columns_list} };
$t{leng2} = $#{ $t{columns_list} } - 1;
for $n ( 0 .. $#{ $t{data_list} } ) {
$t{sql} = 'INSERT INTO ' . $t{table1} . ' (';
for $n1 ( 0 .. $t{leng2} ) {
$t{sql} .= $t{columns_list}[$n1] . ',';
}
$t{sql} .= $t{columns_list}[$t{leng1}] . ')';
$t{sql} .= ' VALUES(';
for $n1 ( 0 .. $t{leng2} ) {
$t{data1} = $t{data_list}[$n][$n1];
$t{sql} .= $t{data1} . ',';
}
$t{sql} .= $t{data_list}[$n][$t{leng1}] . ')';
$$pref{dbh}->do($t{sql});
# print $t{sql},"\n";
# exit;
}
# 关闭数据库
$$pref{dbh}->disconnect;
]]>
指定数据写入enq1(insert_series2.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,$n1,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
$t{sth} = $$pref{dbh}->prepare("SELECT id,type1id FROM enq1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
if ( $rec[1] ne 'B' ) {
@{ $t{type1ids} } = split(/==/,$rec[1]);
@{ $t{sess} } = ();
for $n ( 0 .. $#{ $t{type1ids} } ) {
push(@{ $t{sess} },1);
}
$t{sess1} = join('=',@{ $t{sess} });
$t{sql} = 'UPDATE enq1 SET seriesid = "';
$t{sql} .= $t{sess1} . '" WHERE id = "' . $rec[0] . '"';
$t{DO} = $$pref{dbh}->do($t{sql});
print "$rec[0],$rec[1],$t{sess1},DO=$t{DO}\n";
}
}
$t{sth}->finish;
# 关闭数据库
$$pref{dbh}->disconnect;
]]>
指定数据写入main_type1(insert_series.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,$n1,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取得main_type1的长度
$t{length1} = $$pref{dbh}->selectrow_array("SELECT COUNT(*) FROM main_type1");
for $n ( 1 .. $t{length1} ) {
$t{series} = $$pref{dbh}->selectrow_array("SELECT series FROM main_type1 WHERE id = $n and series is NOT NULL");
if ( $t{series} ) {
$t{series} = 'XXXSERIES=' . $t{series};
# print "$n==>$t{series}\n";
} else {
$t{series} = 'XXXSERIES';
}
$t{sql} = 'UPDATE main_type1 SET series = "';
$t{sql} .= $t{series} . '" WHERE id = "' . $n . '"';
$t{DO} = $$pref{dbh}->do($t{sql});
if ( $t{DO} == 0 ) {
print "$n==>$t{DO}\n";
print "sql==>$t{sql}\n";
exit;
}
}
# 关闭数据库
$$pref{dbh}->disconnect;
]]>
抽出符合条件的main_type1的id(test080714.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$aref);
# 连接数据库
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
$t{word1} = '17A';
$t{type1_leng} = $t{dbh}->selectrow_array("SELECT count(*) FROM main_type1");
for $n ( 1 .. $t{type1_leng} ) {
$t{ptable1} = sprintf("%06d",$n);
$t{ptable1} = 'a' . $t{ptable1};
$t{count1} = $t{dbh}->selectrow_array("SELECT count(*) FROM $t{ptable1} WHERE code LIKE \'\%$t{word1}\%\'");
print "$n===>$t{count1}\n";
}
$t{dbh}->disconnect;
]]>
用SHOW CREATE TABLE复制表格
<![CDATA[
$t{table1} = 'enq1';
$t{table2} = $t{table1} . '_tmp';
$t{sth} = $$pref{dbh}->prepare("SHOW CREATE TABLE $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{create_table} = $rec[1];
print $t{create_table},"\n";
$t{create_table} =~ s/$t{table1}/$t{table2}/;
print $t{create_table},"\n";
}
$t{sth}->finish;
$$pref{dbh}->do($t{create_table});
执行结果
CREATE TABLE `enq1` (
`id` int(11) NOT NULL auto_increment,
`time` date default NULL,
`ourref` int(11) default NULL,
`owner` int(11) default NULL,
`ownerno` varchar(100) default NULL,
`hullnoid` int(11) default NULL,
`type1id` text,
`partsid` text,
`QTY` text,
`memo` text,
`enq2s` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
CREATE TABLE `enq1_tmp` (
`id` int(11) NOT NULL auto_increment,
`time` date default NULL,
`ourref` int(11) default NULL,
`owner` int(11) default NULL,
`ownerno` varchar(100) default NULL,
`hullnoid` int(11) default NULL,
`type1id` text,
`partsid` text,
`QTY` text,
`memo` text,
`enq2s` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
]]>
取出指定数据库数据并写入中间文件(obtain_tables.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 指定数据库名
print "Please input database table name=";
chop($t{table1}=<STDIN>);
$t{outputf} = $t{table1} . '.txt';
# 取出COLUMNS
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $t{table1}");
$t{sth}->execute;
$t{column_list} = '';
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns_list} },$rec[0]);
}
$t{sth}->finish;
# 取出所有数据并写入中间文件
open(OUT,">../txt/$t{outputf}");
print OUT "filename=$t{outputf}\n";
$t{line1} = join('===',@{ $t{columns_list} });
print OUT $t{table1};
print OUT '===';
print OUT $t{line1};
print OUT "\n";
$t{sth} = $$pref{dbh}->prepare("SELECT * FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{line1} = join('===',@rec);
print OUT $t{table1};
print OUT '===';
print OUT $t{line1};
print OUT "\n";
}
$t{sth}->finish;
close(OUT);
# 关闭数据库
$$pref{dbh}->disconnect;
print "The output file is ../txt/$t{outputf}\n";
]]>
生成一个表格(make_table1.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# 文件表名
$t{table1} = 'enq1list';
# 连接数据库
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 删除一个零件表
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{table1} . ';';
$t{dbh}->do($t{sql});
# 创建一个零件表
$t{sql} = 'CREATE TABLE ' . $t{table1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'enq1s1 TEXT,';
$t{sql} .= 'enq1s2 TEXT,';
$t{sql} .= 'enq1s3 TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{dbh}->do($t{sql});
$t{dbh}->disconnect;
__END__;
]]>
perl检索测试程序
<![CDATA[
结果正确
---------------------------------------------------------------------------
$t{orig1} = '17==28';
$t{word1} = '28';
@{ $t{name1s} } = split(/==/,$t{orig1});
$t{SEARCH_OK} = 0;
for $n ( 0 .. $#{ $t{name1s} } ) {
if ( $t{name1s}[$n] == $t{word1} ) {
$t{SEARCH_OK} = 1;
}
}
print "SEARCH_OK=$t{SEARCH_OK}\n";
SEARCH_OK=1
---------------------------------------------------------------------------
结果有错
---------------------------------------------------------------------------
$t{orig1} = '17==28';
$t{word1} = '7';
@{ $t{name1s} } = split(/==/,$t{orig1});
$t{name1} = join(' ',@{ $t{name1s} });
if ( $t{name1} =~ /$t{word1}/ ) {
print "word1=$t{word1}\n";
print "name1=$t{name1}\n";
}
word1=7
name1=17 28
---------------------------------------------------------------------------
]]>
读取一个表格的所有ID的语句
<![CDATA[
$aref = $t{dbh}->selectcol_arrayref("SELECT id FROM enq1");
print "enq1list=@$aref\n";
enq1list=1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
]]>
obtain_tables.pl的修改
<![CDATA[
解决办法:
把程序obtain_tables.pl的分隔号"==="改为"---",因为type1id和partsid的分隔号用"=="和"=",容易出问题!
---------------------------------------------
Please input database table name=enq1
DBD::mysql::db do failed: Incorrect integer value: '' for column 'LANGUAGEid' at
row 1 at insert_tables.pl line 65.
INSERT INTO enq1 (id,time,ourref,owner,ownerno,hullnoid,type1id,partsid,QTY,memo
,LANGUAGEid,makerid,enq2s,seriesid) VALUES("1535","2008-10-14","8101412","4","S0
8028","116","273","103=104=105=96=106=4=107=108=109=110=7","","2=2=15=2=5=10=2=2
=3=6=6","","2","1","1701")
DBD::mysql::db do failed: Incorrect integer value: '' for column 'LANGUAGEid' at
row 1 at insert_tables.pl line 65.
INSERT INTO enq1 (id,time,ourref,owner,ownerno,hullnoid,type1id,partsid,QTY,memo
,LANGUAGEid,makerid,enq2s,seriesid) VALUES("1578","2008-10-16","8101601","33","K
3115","468","1414","5=6=7=8=9=10=11=12","","2=1=1=2=2=2=2=2","","1","153","1747"
)
enq1
id===1535
time===2008-10-14
ourref===8101412
owner===4
ownerno===S08028
hullnoid===116
type1id===273
partsid===103=104=105=96=106=4=107=108=109=110=7
QTY======2=2=15=2=5=10=2=2=3=6=6
memo===
LANGUAGEid===2
makerid===1
enq2s===1701
seriesid===C
修改前
enq1===1535===2008-10-14===8101412===4===S08028===116===273===103=104=105=96=106=4=107=108=109=110=7======2=2=15=2=5=10=2=2=3=6=6======2===1===1701===C
修改后
enq1===1535===2008-10-14===8101412===4===S08028===116===273===103=104=105=96=106=4=107=108=109=110=7===2=2=15=2=5=10=2=2=3=6=6======2===1===1701===C
修改前
enq1===1578===2008-10-16===8101601===33===K3115===468===1414===5=6=7=8=9=10=11=12======2=1=1=2=2=2=2=2======1===153===1747===2
修改后
enq1===1578===2008-10-16===8101601===33===K3115===468===1414===5=6=7=8=9=10=11=12======2=1=1=2=2=2=2=2===1===153===1747===2
---------------------------------------------
INSERT INTO enq2 (id,time,enq1id,LANGUAGEid,ORIGINid,PRICEid,PAYMENTid,makerid,D
ELIVERY,type1id,partsid,QTY,memo,price,discount,discount0,money,price1,makerref,
disc,charges) VALUES("1701","2008-10-15","1535","1","1","1","1","222","","273","
103=104=105=96=106=4=107=108=109=110=7","","2=2=15=2=5=10=2=2=3=6=6","","42.48=5
3.1=0.24=9.44=0.41=3.19=0.24=53.1=0.88=1.53=0.12","D","100","2","P1","","")
DBD::mysql::db do failed: Incorrect integer value: 'D' for column 'discount0' at
row 1 at insert_tables.pl line 65.
INSERT INTO enq2 (id,time,enq1id,LANGUAGEid,ORIGINid,PRICEid,PAYMENTid,makerid,D
ELIVERY,type1id,partsid,QTY,memo,price,discount,discount0,money,price1,makerref,
disc,charges) VALUES("1747","2008-10-17","1578","1","1","1","1","153","","1414",
"5=6=7=8=9=10=11=12","","2=1=1=2=2=2=2=2","","9000=29000=5000=8000=5000=5000=450
0=6000","D","100","1","P1","","")
修改前
enq2===1701===2008-10-15===1535===1===1===1===1===222======273===103=104=105=96=106=4=107=108=109=110=7======2=2=15=2=5=10=2=2=3=6=6======42.48=53.1=0.24=9.44=0.41=3.19=0.24=53.1=0.88=1.53=0.12===D===100===2===P1=========
修改后
enq2===1701===2008-10-15===1535===1===1===1===1===222======273===103=104=105=96=106=4=107=108=109=110=7===2=2=15=2=5=10=2=2=3=6=6======42.48=53.1=0.24=9.44=0.41=3.19=0.24=53.1=0.88=1.53=0.12===D===100===2===P1=========
修改前
enq2===1747===2008-10-17===1578===1===1===1===1===153======1414===5=6=7=8=9=10=11=12======2=1=1=2=2=2=2=2======9000=29000=5000=8000=5000=5000=4500=6000===D===100===1===P1=========
修改后
enq2===1747===2008-10-17===1578===1===1===1===1===153======1414===5=6=7=8=9=10=11=12===2=1=1=2=2=2=2=2======9000=29000=5000=8000=5000=5000=4500=6000===D===100===1===P1=========
]]>
返回