how-to create mysql table with all datatypes

/* Create Table Example 1. */

CREATE TABLE TableWithAllTypes (

col_boolean       BOOL,                 -- boolean

col_byte          TINYINT,              -- byte

col_short         SMALLINT,             -- short

col_int           INTEGER,              -- int

col_long          BIGINT,               -- long

col_float         FLOAT,                -- float

col_double        DOUBLE PRECISION,     -- double

col_bigdecimal    DECIMAL(13,0),        -- BigDecimal

col_string        VARCHAR(254),         -- String

col_date          DATE,                 -- Date

col_time          TIME,                 -- Time

col_timestamp     TIMESTAMP,            -- Timestamp

col_asciistream1  TINYTEXT,             -- Clob ( 2^8 bytes)

col_asciistream2  TEXT,                 -- Clob ( 2^16 bytes)

col_asciistream3  MEDIUMTEXT,           -- Clob (2^24 bytes)

col_asciistream4  LONGTEXT,             -- Clob ( 2^32 bytes)

col_blob1         TINYBLOB,             -- Blob ( 2^8 bytes)

col_blob2         BLOB,                 -- Blob ( 2^16 bytes)

col_blob3         MEDIUMBLOB,           -- Blob ( 2^24 bytes)

col_blob4         LONGBLOB              -- Blob ( 2^32 bytes)

);

show tables like 'TableWithAllTypes' ;

how-to deal with file paths with spaces from file paths containing list files

# answer : imitate find $dir -print0 - turn the new lines into nulls 

cat $include_file | perl -ne 's|\n|\000|g;print'| xargs -0 zip $zip_file

how-to perform upsert from excel to mysql with perl

    use Spreadsheet::XLSX;

    use Text::Iconv;

    #

    # -----------------------------------------------------------------------------

    # runs the insert sql by passed data part 

    # by convention is assumed that the first column is unique and update could 

    # be performed on it ... should there be duplicates the update should fail

    # -----------------------------------------------------------------------------

    sub RunUpsertSql {

       my $self         = shift ; 

       my $table_name   = shift ; 

       my $refHeaders   = shift ; 

       my $refData      = shift ; 

       my $data_str     = '' ; 

       my @headers      = @$refHeaders ; 

       my @data         = @$refData ; 

       print ( "\@data : @data" ) ; 

       print ( "\@headers: @headers" ) ; 

       my $sql_str = " INSERT INTO $table_name " ; 

       $sql_str   .= '(' ; 

       for ( $i=0; $i<scalar (@headers);$i++ ) {

          $sql_str .= " $headers[$i] " . ' , ' ; 

       } #eof for

       for (1..3) { chop ( $sql_str) } ; 

       $sql_str   .= ')' ; 

       foreach my $cellValue ( @data ) {

          # replace the ' chars with \'

          $cellValue       =~ s|\'|\\\'|g ; 

          $data_str .= "'" . "$cellValue" . "' , " ; 

       }

       #eof foreach ' replacement

       # remove the " , " at the end 

       for (1..3) { chop ( $data_str ) } ; 

       $sql_str   .=  " VALUES (" . "$data_str" . ')' ; 

       $sql_str   .= ' ON DUPLICATE KEY UPDATE ' ; 

       for ( $i=0; $i<scalar(@headers);$i++ ) {

          $sql_str .= "$headers[$i]" . ' = ' . "'" . "$data[$i]" . "' , " ; 

       } #eof for

       for (1..3) { chop ( $sql_str) } ; 

       print ( "sql_str : $sql_str " ); 

       $sth = $dbh->prepare($sql_str ) ; 

       $sth->execute( );

    }

    #eof sub RunUpsertSql

    #

    # -----------------------------------------------------------------------------

    # walk trough the Excel and build the data part of the insert sql

    # -----------------------------------------------------------------------------

    sub ParseExcel {

       my $self = shift ; 

       print (  " == START == " ) ; 

       # not sure if it could work without the next line

       # for utf8 strings - slavic , japanese etc. 

       my $converter = Text::Iconv -> new ("utf-8", "utf-8");

       # http://search.cpan.org/~dmow/Spreadsheet-XLSX-0.13-withoutworldwriteables/lib/Spreadsheet/XLSX.pm

       my $objExcelParser = Spreadsheet::XLSX -> new ("$FileInputExcel", $converter);

       # iterate the sheets

       foreach my $objSheet (@{$objExcelParser-> {Worksheet}}) {

          return unless ( $objSheet->{'Name'} eq 'Issue' ) ; 

          print("Sheet: " . $objSheet->{'Name'});

          my $rowCount = 0 ; 

          # iterate the rows 

          my @headerData               = ();

          foreach my $row ($objSheet -> {'MinRow'} .. $objSheet -> {'MaxRow'}) {

             my @rowData               = (); 

             $objSheet -> {'MaxCol'} ||= $objSheet -> {'MinCol'};

             # iterate the coloumns

             foreach my $col ($objSheet -> {'MinCol'} ..  $objSheet -> {'MaxCol'}) {

                my $cell = $objSheet -> {'Cells'} [$row] [$col];

                if ($cell) {

                   #debug printf("( %s , %s ) => %s\n", $row, $col, $cell -> {'Val'});

                   # the unformatted value

                   #my $cellValue = $cell->{'Val'}  ; 

                   # push the formatted value

                   push ( @rowData , $cell->value() )       if $rowCount != 0 ; 

                   push ( @headerData , $cell->value() )    if $rowCount == 0 ; 

                }  #eof if the cell is defined

             } 

             #eof foreach col

          # by convention the name of the xls sheet is the same as the table name

          $self->RunUpsertSql ( $objSheet->{'Name'} , \@headerData , \@rowData) 

             if $rowCount != 0 ; 

             $rowCount++ ; 

          }

          #eof foreach row

       } 

       #eof foreach $objSheet

       print (  " == STOP  == " ) ; 

    } #eof sub ParseExcel