mysqlperldbd

DBD::mysql: ChopBlanks


Why are trailing blanks chopped off from the column value even though ChopBlanks is not enabled?

use DBI;

my $value = '     string     ';
my $db = 'my_mysql_db';
my $dbh = DBI->connect( "dbi:mysql:db=$db", 'user', '*', {  RaiseError => 1, ChopBlanks => 0 } ) or die DBI->errstr;
my $table = 'test_mysql';
$dbh->do( "CREATE TABLE IF NOT EXISTS $table (col_1 CHAR(64))" );
my $sth = $dbh->prepare( "DELETE FROM $table WHERE col_1 = ?" );
$sth->execute( $value );
$sth = $dbh->prepare( "INSERT INTO $table (col_1) VALUES( ? )" );
$sth->execute( $value );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results; 

'     string'
1 rows

Solution

  • I believe this is a MySQL thing and not a DBI thing. From 11.3.2 The CHAR and VARCHAR Types:

    When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.