DBD::mysql未启用ChopBlanks时列值尾部空格仍被截断的原因

问题复现 #

在Perl脚本中显式设置数据库连接参数ChopBlanks => 0，向CHAR(64)类型字段插入前后均带空格的字符串' string '，查询返回结果仅保留前置空格，尾部空格被截断。
复现代码如下：

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

原因说明 #

这个截断行为和DBD::mysql的ChopBlanks配置没有关系，是MySQL服务端本身对CHAR定长字段的默认处理规则导致的：

• CHAR是定长字符串类型，存储时MySQL会自动把字段值右侧补空格到字段定义的固定长度，但检索CHAR类型字段时，服务端默认会自动剥离所有尾部空格再返回给客户端，这个逻辑在服务端层执行，不受客户端DBI层ChopBlanks参数的控制。
• DBI的ChopBlanks配置作用是控制数据库驱动是否对返回的定长类型字段值做尾部空格截断，它生效的前提是驱动从服务端拿到的原始结果本身保留了尾部空格。由于DBD::mysql收到MySQL返回的CHAR字段结果时，尾部空格已经被服务端提前移除，因此即使把ChopBlanks设为0，也无法拿到被截断的尾部空格。

解决方案 #

如果需要保留字符串的尾部空格，可以选择以下两种方案：

• 将字段类型从CHAR替换为VARCHAR或TEXT这类变长字符串类型，变长类型在存储和检索时都会完整保留插入的前后空格，不会触发服务端自动截断尾部空格的逻辑。
• 如果业务必须使用CHAR类型，可以在连接后开启PAD_CHAR_TO_FULL_LENGTH会话级SQL模式，开启后服务端检索CHAR字段时不会剥离尾部空格，会返回填满至字段定义长度的完整值，开启示例：

$dbh->do("SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'");

注意该模式下返回的CHAR字段值会包含MySQL自动补齐到定长长度的空格，需要根据业务需求自行处理字符串长度。 #

内容的提问来源于stack exchange，提问作者sid_com