今天同事让给他数据库生成一些测试数据的时候,实用PowerDesign生成时,出现主键冲突或varchar字段太短截断等问题,总是不能够顺利生成。于是自己实用perl脚本来生成,目的是顺便学习一下Perl对MySQL数据库的操作。
要用Perl连接数据库,要用DBI模块,需要安装DBD::mysql模块。在windows的cmd下实用ppm install DBD::mysql自动安装,ppm是安装Perl时安装的,如果没有设置环境变量则要到对应的目录执行该命令。操作脚本如下,主要是根据网上的例子改了改:
#!/usr/bin/perl -w
use DBI;
#定义插入表的记录数
my $InsertCount=shift @ARGV;
#definition of variables
$db="mcw_ic_sd";
$host="172.18.14.106";
#$host="localhost";
$user="root";
$password="root";
#$password="";
#connect to MySQL database
my $dbh = DBI->connect ("DBI:mysql:database=$db:host=$host",
$user,
$password)
or die "Can't connect to database: $DBI::errstr\n";
#prepare the query
my $sth = $dbh->prepare( "show tables");
#execute the query
$sth->execute( );
## Retrieve the results of a row of data and print
print "\tQuery results:\n================================================\n";
my $strSQL="SELECT TABLE_NAME '表名',ORDINAL_POSITION '序号',COLUMN_NAME '字段名',COLUMN_KEY '主外键',
IS_NULLABLE '是否可为空',DATA_TYPE '类型',CHARACTER_MAXIMUM_LENGTH '长度',EXTRA '是否自增',COLUMN_COMMENT '说明'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mcw_ic_sd' AND TABLE_NAME=?;";
while ( my @row = $sth->fetchrow_array( ) ) {
print "$row[0]\n";
$sth1 = $dbh->prepare($strSQL);
$sth1->bind_param(1, $row[0]);
$sth1->execute();
my $strInsert="INSERT INTO ".$row[0]."(";
my $strValue=" VALUES(";
my $i = 0;
my $Filed="";
while (my @row1 = $sth1->fetchrow_array( ) ) {
print "Hi:$row1[0]"."|$row1[1]"."|$row1[2]"."|$row1[3]"."|$row1[5]\n";
if($i eq 0){
$strInsert=$strInsert.$row1[2];
if($row1[7] eq "auto_increment"){
$strValue=$strValue."NULL";
}elsif($row1[5] eq "int"){
#$strValue=$strValue."1";
}else{
$strValue=$strValue."'I'm UUID哈哈'";
}
$i=1;
next;
}
$strInsert=$strInsert.",".$row1[2];
if($row1[5] eq "int"){
$Filed=","."123"."";
}elsif($row1[5] eq "datetime"){
$Filed=",'"."2011-12-26 11:32:33"."'";
}elsif($row1[5] eq "bit"){
$Filed=","."1"."";
}elsif($row1[5] eq "date"){
$Filed=",'"."2011-12-26"."'";
}elsif($row1[5] eq "time"){
$Filed=",'"."22:22:22"."'";
}elsif($row1[5] eq "float"){
$Filed=",'"."3.14"."'";
}elsif($row1[5] eq "decimal"){
$Filed=",'"."0.1"."'";
}else{
if($row1[6] lt 5){
$Filed=",'"."as"."'";
}elsif($row1[6] lt 10){
$Filed=",'"."assdd39f"."'";
}elsif($row1[6] lt 20){
$Filed=",'"."Bsd45678d01234567"."'";
}elsif($row1[6] lt 30){
$Filed=",'"."aSN45678d012345dsfdsf67DFEG"."'";
}else{
$Filed=",'"."AF$#2asd45678d012345dsfdsDFDf67dGfdf"."'";
}
}
$strValue=$strValue.$Filed
}
$strInsert=$strInsert.") ";
$strValue=$strValue.") ";
$strInsert=$strInsert.$strValue;
for(my $icount = 0; $icount < $InsertCount;$icount+=1)
{
$sth2 = $dbh->prepare($strInsert);
$sth2->execute();
}
print $strInsert."\n";
}
warn "Problem in retrieving results", $sth->errstr( ), "\n"
if $sth->err( );
exit;
后来想字符串写死了不好,长的字符我们还是实用UUID来填充吧。但怎么生成UUID呢。实用Perl中的Data::UUID模块就行了,这个也需要手动安装。
使用ppm install Data::UUID安装好后,
use Data::UUID;
$ug = new Data::UUID;
$uuid1 = $ug->create();
$str = $ug->to_string( $uuid1 );
print $str;
Data::UUID下还有一些不会实用,暂时不看了
如下代码(<namespace>, <name>);中我居然不知道是什么意思,对Perl的命名空间没认识。
use Data::UUID;
$ug = new Data::UUID;
$uuid1 = $ug->create();
$uuid2 = $ug->create_from_name(<namespace>, <name>);
$res = $ug->compare($uuid1, $uuid2);
$str = $ug->to_string( $uuid );
$uuid = $ug->from_string( $str );