Set PAD_CHAR_TO_FULL_LENGTH on MySql Connector/Net

Question

As MySQL Reference Manual states,

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. 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.

How to enable PAD_CHAR_TO_FULL_LENGTH flag in Connector/Net? (not ODBC)

Answer 1

As documented under Server SQL Modes:

To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement:

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time.

Therefore you want to execute a plain old SQL command along the following lines:

SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

Answer 2

A workaround in case that you don't want to change the SQL mode is to TRIM previously the value, the reason is that CHAR types are fixed.

Example:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(10) DEFAULT NULL,
  `field2` char(10) DEFAULT NULL,
  `comb` varchar(20) GENERATED ALWAYS AS (concat(TRIM(`field2`),'test')) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;