在这两种方法中,您更喜欢哪一种:试图找到正确的数据类型还是仅仅使用always varchar?

# ...
use HTML::TableExtract;
my $te = HTML::TableExtract->new( headers => [ 'some headers', 'one', 'two' ], keep_headers => 1 );
$te->parse( $html_string );
die $te->tables if $te->tables != 1;
( my $grid ) = $te->tables;

use DBI;
my $dbh = DBI->connect( ... ) or die $DBI::errstr;
my $table = 'my_test_table';

my @rows = $grid->rows;
my $header_row = shift @rows;

#####  version 1  ####
use Data::Types qw(:all);
my @create_row;

for my $col ( 0 .. $#$header_row ) {
    my ( $count, $int, $float ) = ( 0, 0, 0 );
    my $longest = 0;
    for my $row ( @rows ) {
        $longest = length $row->[$col] if length $row->[$col] > $longest;
        $int++ if is_int( $row->[$col] );
        $float++ if is_float( $row->[$col] );
        $count++;
    }
    if ( $int == $count ) {
        $create_row[$col] = $dbh->quote( $header_row->[$col] ) . ' int';
    }
    elsif ( $float == $count ) {
        $create_row[$col] = $dbh->quote( $header_row->[$col] ) . ' float';
    }
    else {
        $create_row[$col] = $dbh->quote( $header_row->[$col] ) . " char($longest)";
    }
}

$sql = sprintf "CREATE TABLE $table ( %s )",
join( ', ', @create_row );
$dbh->do( $sql );

$sql = sprintf "INSERT INTO $table ( %s ) VALUES( %s )",
join( ',',  map { $dbh->quote( $_ ) } @$header_row ), join( ',', ('?') x @$header_row );
my $sth = $dbh->prepare( $sql );

####  version 2  ####
# always varchar

$sql = sprintf "CREATE TABLE $table ( %s )",
join( ', ', map { "'$_' varchar(60)" } @$header_row );
$dbh->do( $sql );

$sql = sprintf "INSERT INTO $table ( %s ) VALUES( %s )",
join( ',',  map { $dbh->quote( $_ ) } @$header_row ), join( ',', ('?') x @$header_row );
my $sth = $dbh->prepare( $sql );

最佳答案

如果正在处理的表不会更改,并且如果该列仅用于该单个表的数据,则可以安全地猜测似乎适合的数据类型(版本1)。
但是,如果您计划向该列中添加更多数据,则需要将所有内容都保留为varchars,以防将来有其他类型的数据(版本2)。

关于database - DBI:如何为未知数据找到正确的数据类型?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/5210503/

10-12 00:20
查看更多