CHAR Data Type - Oracle to MySQL Migration

Oracle and MySQL have CHAR(n) data type that stores a fixed-length character string right-padded with blanks to the specified n length.

Oracle MySQL
CHAR(n) CHAR(n) n <= 255
CHAR(n CHAR) Size in characters VARCHAR(n) n > 255
CHAR(n BYTE) Size in bytes
1 <= n <= 2000, default is 1 0 <= n <= 255, default is 1
Size n in characters or bytes, but the storage limit is 2000 bytes Size n in characters
Default length semantics is defined by NLS_LENGTH_SEMANTICS parameter
Trailing spaces are retained on read Trailing spaces are removed on read


Oracle MySQL

Trailing Spaces on Retrieval

When you select data from a CHAR column in Oracle, trailing spaces are always returned:


  -- Sample table 
  CREATE TABLE airport_codes (code CHAR(5));
  INSERT INTO airport_codes VALUES ('SFO');
  -- Trailing spaces are not trimmed
  SELECT '(' || code || ')' FROM airport_codes;
  # (SFO  )

At the same time, MySQL removes the trailing spaces from CHAR columns on retrieval, by default:


  -- Trailing spaces are trimmed
  SELECT CONCAT('(', code, ')') FROM airport_codes;
  # (SFO)

Note that MySQL offers PAD_CHAR_TO_FULL_LENGTH option (it is not set by default) that specifies to retain the trailing spaces for CHAR columns on retrieval:


  SET @old_sql_mode = @@sql_mode;
  -- Set option to retain trailing spaces for CHAR columns 
  // Trailing spaces are not trimmed now
  SELECT CONCAT('(', code, ')') FROM airport_codes;
  # (SFO  )
  SET sql_mode = @old_sql_mode;

For more information, see Oracle to MySQL Migration.

You could leave a comment if you were logged in.